[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_login_id jl_co_gl_trx.last_update_login%TYPE;
SELECT gljh.je_source source,
gljh.je_batch_id,
gljl.je_header_id,
gljl.je_line_num ,
gljh.je_category category,
gljh.reversed_je_header_id,
gljl.code_combination_id,
gljl.period_name,
gljl.effective_date accounting_date,
gljl.reference_1 ext_doc_num,
0 extgl_nit_id,
NVL(NVL(gljl.reference_2,NVL(gljl.CO_THIRD_PARTY,gljl.global_attribute1)),'0') ext_nit,
SUBSTR(gljl.reference_4,1,30) ext_nit_type,
gljl.reference_5 ext_nit_name,
gljl.reference_3 ext_nit_v_digit,
gljl.subledger_doc_sequence_value subl_doc_num,
gljh.currency_code currency,
gljl.entered_dr,
gljl.entered_cr,
gljl.accounted_cr,
gljl.accounted_dr,
gljl.reference_1 ref_1,
gljl.reference_2 ref_2,
gljl.reference_3 ref_3,
gljl.reference_4 ref_4,
gljl.reference_5 ref_5,
gljl.reference_6 ref_6,
gljl.reference_7 ref_7,
gljl.reference_8 ref_8,
gljl.reference_9 ref_9,
gljl.reference_10 ref_10,
DECODE(g_account_segment,
'SEGMENT1', glcc.segment1, 'SEGMENT2', glcc.segment2,
'SEGMENT3', glcc.segment3, 'SEGMENT4', glcc.segment4,
'SEGMENT5', glcc.segment5, 'SEGMENT6', glcc.segment6,
'SEGMENT7', glcc.segment7, 'SEGMENT8', glcc.segment8,
'SEGMENT9', glcc.segment9, 'SEGMENT10',glcc.segment10,
'SEGMENT11',glcc.segment11, 'SEGMENT12',glcc.segment12,
'SEGMENT13',glcc.segment13, 'SEGMENT14',glcc.segment14,
'SEGMENT15',glcc.segment15, 'SEGMENT16',glcc.segment16,
'SEGMENT17',glcc.segment17, 'SEGMENT18',glcc.segment18,
'SEGMENT19',glcc.segment19, 'SEGMENT20',glcc.segment20,
'SEGMENT21',glcc.segment21, 'SEGMENT22',glcc.segment22,
'SEGMENT23',glcc.segment23, 'SEGMENT24',glcc.segment24,
'SEGMENT25',glcc.segment25, 'SEGMENT26',glcc.segment26,
'SEGMENT27',glcc.segment27, 'SEGMENT28',glcc.segment28,
'SEGMENT28',glcc.segment28, 'SEGMENT29',glcc.segment29,
'SEGMENT30',glcc.segment30, NULL) account_code
FROM gl_je_headers gljh,
gl_code_combinations glcc,
gl_je_lines gljl
WHERE gljl.status = 'P'
AND gljl.period_name = x_period
AND gljl.ledger_id = x_sobid
AND gljh.je_batch_id = NVL(x_batchid,gljh.je_batch_id)
AND gljl.code_combination_id = glcc.code_combination_id
AND EXISTS (SELECT '1'
FROM jl_co_gl_nit_accts jlcgna
WHERE DECODE(g_account_segment,
'SEGMENT1', glcc.segment1, 'SEGMENT2', glcc.segment2,
'SEGMENT3', glcc.segment3, 'SEGMENT4', glcc.segment4,
'SEGMENT5', glcc.segment5, 'SEGMENT6', glcc.segment6,
'SEGMENT7', glcc.segment7, 'SEGMENT8', glcc.segment8,
'SEGMENT9', glcc.segment9, 'SEGMENT10',glcc.segment10,
'SEGMENT11',glcc.segment11, 'SEGMENT12',glcc.segment12,
'SEGMENT13',glcc.segment13, 'SEGMENT14',glcc.segment14,
'SEGMENT15',glcc.segment15, 'SEGMENT16',glcc.segment16,
'SEGMENT17',glcc.segment17, 'SEGMENT18',glcc.segment18,
'SEGMENT19',glcc.segment19, 'SEGMENT20',glcc.segment20,
'SEGMENT21',glcc.segment21, 'SEGMENT22',glcc.segment22,
'SEGMENT23',glcc.segment23, 'SEGMENT24',glcc.segment24,
'SEGMENT25',glcc.segment25, 'SEGMENT26',glcc.segment26,
'SEGMENT27',glcc.segment27, 'SEGMENT28',glcc.segment28,
'SEGMENT28',glcc.segment28, 'SEGMENT29',glcc.segment29,
'SEGMENT30',glcc.segment30, NULL) = jlcgna.account_code
AND jlcgna.nit_required = 'Y'
AND jlcgna.chart_of_accounts_id = g_chart_of_accounts_id)
AND gljl.je_header_id = gljh.je_header_id
AND nvl(gljl.co_processed_flag, 'N') <> 'Y'
AND gljh.actual_flag = 'A'
AND gljh.currency_code <> 'STAT';
PROCEDURE Insert_Error_Rec
(p_message_text IN VARCHAR2 ) IS
BEGIN
INSERT INTO jl_co_gl_conc_errs (message_text,
process_id,
je_header_id,
je_line_num,
identifier,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (p_message_text,
g_parameter_rec.cid,
g_gl_je_rec.je_header_id,
g_gl_je_rec.je_line_num,
g_gl_je_rec.identifier,
sysdate,
NVL(g_parameter_rec.user_id,-1),
sysdate,
NVL(g_parameter_rec.user_id,-1),
g_login_id );
FND_FILE.PUT_LINE(FND_FILE.log,'Insert_Error_Rec:'|| g_error_text);
END Insert_Error_Rec;
SELECT nit_id,
nit,
name,
type,
verifying_digit
INTO l_master_nit_rec
FROM jl_co_gl_nits
WHERE nit = p_nit_rec.nit;
SELECT nit_id,
nit,name,
type,
verifying_digit
INTO l_master_nit_rec
FROM jl_co_gl_nits
WHERE UPPER(name) = UPPER(p_nit_rec.nit_name);
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
INSERT INTO jl_co_gl_nits (nit_id,
nit,
type,
verifying_digit,
name,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (jl_co_gl_nits_s.nextval,
p_nit_rec.nit,
p_nit_rec.nit_type,
p_nit_rec.verifying_digit,
p_nit_rec.nit_name,
sysdate,
NVL(g_parameter_rec.user_id,-1),
sysdate,
NVL(g_parameter_rec.user_id,-1),
g_login_id);
SELECT jl_co_gl_nits_s.currval
INTO p_nit_rec.nit_id
FROM DUAL ;
Insert_Error_Rec(l_message_text);
SELECT nit_id
INTO g_nit_rec.nit_id
FROM jl_co_gl_nits jlcgn
WHERE nit = DECODE(p_journal_rec.source,
'Payables','0',
'Purchasing','0',
'Receivables','0',
p_journal_rec.ext_nit);
SELECT p_journal_rec.je_header_id,
p_journal_rec.je_line_num,
null
INTO g_gl_je_rec
FROM DUAL;
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
Insert_Error_Rec(l_message_text);
INSERT INTO jl_co_gl_trx (transaction_id,
process_id,
set_of_books_id,
code_combination_id,
account_code,
nit_id,
period_name,
period_year,
period_num,
je_batch_id,
je_header_id,
category,
subledger_doc_number,
je_line_num,
document_number,
accounting_date,
currency_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr)
VALUES (jl_co_gl_trx_s.nextval,
g_parameter_rec.cid,
g_parameter_rec.set_of_books_id,
p_journal_rec.code_combination_id,
p_journal_rec.account_code,
NVL(g_nit_rec.nit_id,g_default_nit_id),
p_journal_rec.period_name,
g_period_year,
g_period_num,
p_journal_rec.je_batch_id,
p_journal_rec.je_header_id,
p_journal_rec.category,
p_journal_rec.subl_doc_num,
p_journal_rec.je_line_num,
NULL,
p_journal_rec.accounting_date,
p_journal_rec.currency,
sysdate,
NVL(g_parameter_rec.user_id,-1),
sysdate,
NVL(g_parameter_rec.user_id,-1),
g_login_id,
DECODE(sign(p_journal_rec.accounted_cr), -1,
(abs(p_journal_rec.accounted_cr) +
NVL(DECODE(sign(p_journal_rec.accounted_dr),1,
p_journal_rec.accounted_dr,NULL),0)),
DECODE(sign(p_journal_rec.accounted_dr),-1,
NULL,p_journal_rec.accounted_dr)),
DECODE(sign(p_journal_rec.accounted_dr),-1,
(abs(p_journal_rec.accounted_dr) +
NVL(DECODE(sign(p_journal_rec.accounted_cr),1,
p_journal_rec.accounted_cr,NULL),0)),
DECODE(sign(p_journal_rec.accounted_cr),-1,
NULL,p_journal_rec.accounted_cr)),
DECODE(sign(p_journal_rec.entered_cr),-1,
(abs(p_journal_rec.entered_cr) +
NVL(DECODE(sign(p_journal_rec.entered_dr),1,
p_journal_rec.entered_dr,NULL),0)),
DECODE(sign(p_journal_rec.entered_dr),-1,
NULL,p_journal_rec.entered_dr)),
DECODE(sign(p_journal_rec.entered_dr),-1,
(abs(p_journal_rec.entered_dr) +
NVL(DECODE(sign(p_journal_rec.entered_cr),1,
p_journal_rec.entered_cr,NULL),0)),
DECODE(sign(p_journal_rec.entered_cr),-1,
NULL,p_journal_rec.entered_cr)) );
SELECT 'TRUE'
INTO g_error_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM jl_co_gl_conc_errs jlcgce
WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
AND jlcgce.je_line_num = p_journal_rec.je_line_num);
DELETE FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
AND jlcgt.je_line_num = p_journal_rec.je_line_num;
UPDATE gl_je_lines gljl
SET co_processed_flag = 'Y'
WHERE gljl.je_header_id = p_journal_rec.je_header_id
AND gljl.je_line_num = p_journal_rec.je_line_num
AND EXISTS (SELECT 'Y'
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = gljl.je_header_id
AND jlcgt.je_line_num = gljl.je_line_num);
SELECT DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,ABS(lnk.unrounded_accounted_dr),null),
lnk.unrounded_accounted_cr) ACCOUNTED_CR,
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
DECODE(SIGN(lnk.unrounded_accounted_cr),-1,ABS(lnk.unrounded_accounted_cr),null),
lnk.unrounded_accounted_dr) ACCOUNTED_DR,
DECODE(SIGN(lnk.unrounded_entered_dr),-1,
DECODE(SIGN(lnk.unrounded_entered_cr),-1, ABS(lnk.unrounded_entered_cr), null),
lnk.unrounded_entered_dr) ENTERED_DR,
DECODE(SIGN(lnk.unrounded_entered_cr),-1,
DECODE(SIGN(lnk.unrounded_entered_dr),-1, ABS(lnk.unrounded_entered_dr), null),
lnk.unrounded_entered_cr) ENTERED_CR,
lnk.source_distribution_type SOURCE_TABLE,
ael.party_id PARTY_ID,
ent.transaction_number TRX_NUMBER_C ,
I.invoice_id TRX_HDR_ID,
'INV' TRX_CLASS,
ael.accounting_class_code ACCT_LINE_TYPE,
D.invoice_distribution_id TRX_DIST_ID
FROM
ap_invoices_all I,
xla_transaction_entities ent,
xla_ae_headers AEH,
xla_ae_lines AEL,
ap_invoice_distributions_all D,
xla_distribution_links LNK,
gl_import_references R
WHERE
ent.application_id = 200
and ent.application_id =aeh.application_id
and aeh.application_id = ael.application_id
and (ent.ledger_id = g_parameter_rec.set_of_books_id
or ent.ledger_id in (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
and ent.entity_code = 'AP_INVOICES'
and i.invoice_id = ent.source_id_int_1
and ent.entity_id = aeh.entity_id
AND AEH.ledger_id = ent.ledger_id
and aeh.ae_header_id = ael.ae_header_id
and ael.ae_header_id = lnk.ae_header_id
and ael.ae_line_num = lnk.ae_line_num
and ael.application_id = 200
and lnk.application_id = 200
--AND D.invoice_distribution_id(+) = DECODE(lnk.source_distribution_type,
-- 'AP_INVOICE_DISTRIBUTIONS', lnk.source_distribution_id_num_1,null)
AND D.invoice_distribution_id = lnk.source_distribution_id_num_1
AND R.gl_sl_link_id = AEL.gl_sl_link_id
AND R.je_header_id = p_journal_rec.je_header_id
AND R.je_line_num = p_journal_rec.je_line_num
UNION ALL
SELECT DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,ABS(lnk.unrounded_accounted_dr),null),
lnk.unrounded_accounted_cr) ACCOUNTED_CR,
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
DECODE(SIGN(lnk.unrounded_accounted_cr),-1,ABS(lnk.unrounded_accounted_cr),null),
lnk.unrounded_accounted_dr) ACCOUNTED_DR,
DECODE(SIGN(lnk.unrounded_entered_dr),-1,
DECODE(SIGN(lnk.unrounded_entered_cr),-1, ABS(lnk.unrounded_entered_cr), null),
lnk.unrounded_entered_dr) ENTERED_DR,
DECODE(SIGN(lnk.unrounded_entered_cr),-1,
DECODE(SIGN(lnk.unrounded_entered_dr),-1, ABS(lnk.unrounded_entered_dr), null),
lnk.unrounded_entered_cr) ENTERED_CR,
ent.entity_code SOURCE_TABLE,
ael.party_id PARTY_ID,
TO_CHAR(C.CHECK_NUMBER) TRX_NUMBER_C ,
C.CHECK_ID TRX_HDR_ID,
'PAY' TRX_CLASS,
ael.accounting_class_code ACCT_LINE_TYPE,
D.invoice_distribution_id TRX_DIST_ID
FROM
ap_checks_all C,
xla_transaction_entities ent,
xla_ae_headers AEH,
xla_ae_lines AEL,
ap_payment_hist_dists D, -- bug 6956097
xla_distribution_links LNK,
gl_import_references R
WHERE
ent.application_id = 200
and (ent.ledger_id = g_parameter_rec.set_of_books_id
or ent.ledger_id in (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
and ent.entity_code = 'AP_PAYMENTS'
and c.check_id = ent.source_id_int_1
and ent.entity_id = aeh.entity_id
AND AEH.ledger_id = ent.ledger_id
and aeh.ae_header_id = ael.ae_header_id
and ael.ae_header_id = lnk.ae_header_id
and ael.ae_line_num = lnk.ae_line_num
and ael.application_id = 200
and lnk.application_id = 200
--AND D.invoice_distribution_id(+) = DECODE(lnk.source_distribution_type,
-- 'AP_INVOICE_DISTRIBUTIONS', lnk.source_distribution_id_num_1,null)
AND D.payment_hist_dist_id = lnk.source_distribution_id_num_1 -- bug 6956097
AND R.gl_sl_link_id = AEL.gl_sl_link_id
AND R.je_header_id = p_journal_rec.je_header_id
AND R.je_line_num = p_journal_rec.je_line_num
UNION ALL
SELECT DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,ABS(lnk.unrounded_accounted_dr),null),
lnk.unrounded_accounted_cr) ACCOUNTED_CR,
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
DECODE(SIGN(lnk.unrounded_accounted_cr),-1,ABS(lnk.unrounded_accounted_cr),null),
lnk.unrounded_accounted_dr) ACCOUNTED_DR,
DECODE(SIGN(lnk.unrounded_entered_dr),-1,
DECODE(SIGN(lnk.unrounded_entered_cr),-1, ABS(lnk.unrounded_entered_cr), null),
lnk.unrounded_entered_dr) ENTERED_DR,
DECODE(SIGN(lnk.unrounded_entered_cr),-1,
DECODE(SIGN(lnk.unrounded_entered_dr),-1, ABS(lnk.unrounded_entered_dr), null),
lnk.unrounded_entered_cr) ENTERED_CR,
lnk.source_distribution_type SOURCE_TABLE,
ael.party_id PARTY_ID,
ent.transaction_number TRX_NUMBER_C ,
I.invoice_id TRX_HDR_ID,
'INV' TRX_CLASS,
ael.accounting_class_code ACCT_LINE_TYPE,
D.invoice_distribution_id TRX_DIST_ID
FROM
ap_invoices_all I,
xla_transaction_entities ent,
xla_ae_headers AEH,
xla_ae_lines AEL,
ap_prepay_app_dists D,
xla_distribution_links LNK,
gl_import_references R
WHERE
ent.application_id = 200
and ent.application_id =aeh.application_id
and aeh.application_id = ael.application_id
and (ent.ledger_id = g_parameter_rec.set_of_books_id
AND lnk.source_distribution_type = 'AP_PREPAY'
or ent.ledger_id in (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
and ent.entity_code = 'AP_INVOICES'
and i.invoice_id = ent.source_id_int_1
and ent.entity_id = aeh.entity_id
AND AEH.ledger_id = ent.ledger_id
and aeh.ae_header_id = ael.ae_header_id
and ael.ae_header_id = lnk.ae_header_id
and ael.ae_line_num = lnk.ae_line_num
and ael.application_id = 200
and lnk.application_id = 200
--AND D.invoice_distribution_id(+) = DECODE(lnk.source_distribution_type,
-- 'AP_INVOICE_DISTRIBUTIONS', lnk.source_distribution_id_num_1,null)
AND D.prepay_app_dist_id = lnk.source_distribution_id_num_1
AND R.gl_sl_link_id = AEL.gl_sl_link_id
AND R.je_header_id = p_journal_rec.je_header_id
AND R.je_line_num = p_journal_rec.je_line_num;
SELECT p_journal_rec.je_header_id,
p_journal_rec.je_line_num,
p_journal_rec.ext_nit
INTO g_gl_je_rec
FROM DUAL;
SELECT DECODE(ap_trx.acct_line_type,'LIABILITY',NULL,NVL(global_attribute2,NULL))
INTO l_supplier_num
FROM ap_invoice_distributions_all apida
WHERE apida.invoice_id = ap_trx.trx_hdr_id
--AND apida.distribution_line_number = ap_trx.trx_dist_id
AND apida.invoice_distribution_id = ap_trx.trx_dist_id
and (apida.set_of_books_id = g_parameter_rec.set_of_books_id
or apida.set_of_books_id in (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id));
SELECT jlcgn.nit_id,
REPLACE(pov.num_1099,'-'),
pov.vendor_name,
SUBSTR(pov.global_attribute10,1,30),
pov.global_attribute12,
NVL(l_supplier_num,pov.segment1)
INTO g_nit_rec.nit_id,
g_nit_rec.nit,
g_nit_rec.nit_name,
g_nit_rec.nit_type,
g_nit_rec.verifying_digit,
l_supplier_num
FROM jl_co_gl_nits jlcgn, po_vendors pov
WHERE NVL(l_supplier_num,TO_CHAR(ap_trx.party_id)) =
DECODE(l_supplier_num, NULL,TO_CHAR(pov.vendor_id),pov.segment1)
AND jlcgn.nit(+) = REPLACE(pov.num_1099,'-') ; */
SELECT jlcgn.nit_id,
REPLACE(povapf.num_1099,'-'),
povapf.vendor_name,
SUBSTR(povapf.global_attribute10,1,30),
povapf.global_attribute12,
NVL(l_supplier_num,povapf.segment1)
INTO g_nit_rec.nit_id,
g_nit_rec.nit,
g_nit_rec.nit_name,
g_nit_rec.nit_type,
g_nit_rec.verifying_digit,
l_supplier_num
FROM jl_co_gl_nits jlcgn, (SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
aps.vendor_name,
aps.global_attribute10,
aps.global_attribute12,
aps.segment1,
aps.vendor_id
FROM ap_suppliers aps,
(select distinct person_id ,national_identifier from per_all_people_f
where nvl(EFFECTIVE_END_DATE,sysdate)>=sysdate) papf
WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
WHERE ap_trx.party_id = povapf.vendor_id
AND jlcgn.nit(+) = REPLACE(povapf.num_1099,'-') ;
SELECT jlcgn.nit_id,
REPLACE(povapf.num_1099,'-'),
povapf.vendor_name,
SUBSTR(povapf.global_attribute10,1,30),
povapf.global_attribute12,
NVL(l_supplier_num,povapf.segment1)
INTO g_nit_rec.nit_id,
g_nit_rec.nit,
g_nit_rec.nit_name,
g_nit_rec.nit_type,
g_nit_rec.verifying_digit,
l_supplier_num
FROM jl_co_gl_nits jlcgn, (SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
aps.vendor_name,
aps.global_attribute10,
aps.global_attribute12,
aps.segment1,
aps.vendor_id
FROM ap_suppliers aps,
(select distinct person_id ,national_identifier from per_all_people_f
where nvl(EFFECTIVE_END_DATE,sysdate)>=sysdate) papf
WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
WHERE l_supplier_num = povapf.segment1
AND jlcgn.nit(+) = REPLACE(povapf.num_1099,'-') ;
INSERT INTO jl_co_gl_trx (transaction_id,
process_id,
set_of_books_id,
code_combination_id,
account_code,
nit_id,
period_name,
period_year,
period_num,
je_batch_id,
je_header_id,
category,
subledger_doc_number,
je_line_num,
document_number,
accounting_date,
currency_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr)
VALUES (jl_co_gl_trx_s.nextval,
g_parameter_rec.cid,
g_parameter_rec.set_of_books_id,
p_journal_rec.code_combination_id,
p_journal_rec.account_code,
g_nit_rec.nit_id,
p_journal_rec.period_name,
g_period_year,
g_period_num,
p_journal_rec.je_batch_id,
p_journal_rec.je_header_id,
p_journal_rec.category,
p_journal_rec.subl_doc_num,
p_journal_rec.je_line_num,
ap_trx.trx_number_c,
p_journal_rec.accounting_date,
p_journal_rec.currency,
sysdate,
NVL(g_parameter_rec.user_id,-1),
sysdate,
NVL(g_parameter_rec.user_id,-1),
g_login_id,
ap_trx.accounted_dr,
ap_trx.accounted_cr,
ap_trx.entered_dr,
ap_trx.entered_cr );
SELECT 'TRUE'
INTO g_error_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM jl_co_gl_conc_errs jlcgce
WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
AND jlcgce.je_line_num = p_journal_rec.je_line_num); */
SELECT 'TRUE'
INTO g_error_exists
FROM jl_co_gl_conc_errs jlcgce
WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
AND jlcgce.je_line_num = p_journal_rec.je_line_num and rownum = 1;
DELETE FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
AND jlcgt.je_line_num = p_journal_rec.je_line_num;
UPDATE gl_je_lines gljl
SET co_processed_flag = 'Y'
WHERE gljl.je_header_id = p_journal_rec.je_header_id
AND gljl.je_line_num = p_journal_rec.je_line_num
AND EXISTS (SELECT 'Y'
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = gljl.je_header_id
AND jlcgt.je_line_num = gljl.je_line_num);
SELECT 'TRUE'
INTO g_error_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM jl_co_gl_conc_errs jlcgce
WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
AND jlcgce.je_line_num = p_journal_rec.je_line_num);
UPDATE gl_je_lines gljl
SET co_processed_flag = NULL
WHERE gljl.je_header_id = p_journal_rec.je_header_id
AND gljl.je_line_num = p_journal_rec.je_line_num
AND EXISTS (SELECT 'Y'
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = gljl.je_header_id
AND jlcgt.je_line_num = gljl.je_line_num);
DELETE FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
AND jlcgt.je_line_num = p_journal_rec.je_line_num;
SELECT jlcgn.nit_id,
REPLACE(povapf.num_1099,'-'),
povapf.vendor_name,
SUBSTR(povapf.global_attribute10,1,30),
povapf.global_attribute12,
povapf.segment1,
p_journal_rec.je_header_id,
p_journal_rec.je_line_num
INTO g_nit_rec.nit_id,
g_nit_rec.nit,
g_nit_rec.nit_name,
g_nit_rec.nit_type,
g_nit_rec.verifying_digit,
g_gl_je_rec.identifier,
g_gl_je_rec.je_header_id,
g_gl_je_rec.je_line_num
FROM jl_co_gl_nits jlcgn,
po_headers_all poha,
(SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
aps.vendor_name,
aps.global_attribute10,
aps.global_attribute12,
aps.segment1,
aps.vendor_id
FROM ap_suppliers aps,
(select distinct person_id ,national_identifier from per_all_people_f
where nvl(EFFECTIVE_END_DATE,sysdate)>=sysdate) papf
WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
WHERE poha.po_header_id = TO_NUMBER(p_journal_rec.ref_2)
AND povapf.vendor_id = poha.vendor_id
AND REPLACE(povapf.num_1099,'-') = jlcgn.nit(+);
INSERT INTO jl_co_gl_trx (transaction_id,
process_id,
set_of_books_id,
code_combination_id,
account_code,
nit_id,
period_name,
period_year,
period_num,
je_batch_id,
je_header_id,
category,
subledger_doc_number,
je_line_num,
document_number,
accounting_date,
currency_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr )
VALUES (jl_co_gl_trx_s.nextval,
g_parameter_rec.cid,
g_parameter_rec.set_of_books_id,
p_journal_rec.code_combination_id,
p_journal_rec.account_code,
g_nit_rec.nit_id,
p_journal_rec.period_name,
g_period_year,
g_period_num,
p_journal_rec.je_batch_id,
p_journal_rec.je_header_id,
p_journal_rec.category,
p_journal_rec.subl_doc_num,
p_journal_rec.je_line_num,
p_journal_rec.ref_4,
p_journal_rec.accounting_date,
p_journal_rec.currency,
sysdate,
NVL(g_parameter_rec.user_id,-1),
sysdate,
NVL(g_parameter_rec.user_id,-1),
g_login_id,
DECODE(sign(p_journal_rec.accounted_cr),-1,
(abs(p_journal_rec.accounted_cr) +
NVL(DECODE(sign(p_journal_rec.accounted_dr),
1,p_journal_rec.accounted_dr,NULL),0)),
DECODE(sign(p_journal_rec.accounted_dr),-1,
NULL,p_journal_rec.accounted_dr)),
DECODE(sign(p_journal_rec.accounted_dr),-1,
(abs(p_journal_rec.accounted_dr) +
NVL(DECODE(sign(p_journal_rec.accounted_cr),
1,p_journal_rec.accounted_cr,NULL),0)),
DECODE(sign(p_journal_rec.accounted_cr),-1,
NULL,p_journal_rec.accounted_cr)),
DECODE(sign(p_journal_rec.entered_cr),-1,
(abs(p_journal_rec.entered_cr) +
NVL(DECODE(sign(p_journal_rec.entered_dr),
1,p_journal_rec.entered_dr,NULL),0)),
DECODE(sign(p_journal_rec.entered_dr),-1,
NULL,p_journal_rec.entered_dr)),
DECODE(sign(p_journal_rec.entered_dr),-1,
(abs(p_journal_rec.entered_dr) +
NVL(DECODE(sign(p_journal_rec.entered_cr),
1,p_journal_rec.entered_cr,NULL),0)),
DECODE(sign(p_journal_rec.entered_cr),-1,
NULL,p_journal_rec.entered_cr)) );
SELECT 'TRUE'
INTO g_error_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM jl_co_gl_conc_errs jlcgce
WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
AND jlcgce.je_line_num = p_journal_rec.je_line_num);
DELETE FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
AND jlcgt.je_line_num = p_journal_rec.je_line_num;
UPDATE gl_je_lines gljl
SET co_processed_flag = 'Y'
WHERE gljl.je_header_id = p_journal_rec.je_header_id
AND gljl.je_line_num = p_journal_rec.je_line_num
AND EXISTS (SELECT 'Y'
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = gljl.je_header_id
AND jlcgt.je_line_num = gljl.je_line_num);
SELECT DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,ABS(lnk.unrounded_accounted_dr),null),
lnk.unrounded_accounted_cr) ACCOUNTED_CR,
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
DECODE(SIGN(lnk.unrounded_accounted_cr),-1,ABS(lnk.unrounded_accounted_cr),null),
lnk.unrounded_accounted_dr) ACCOUNTED_DR,
DECODE(SIGN(lnk.unrounded_entered_dr),-1,
DECODE(SIGN(lnk.unrounded_entered_cr),-1, ABS(lnk.unrounded_entered_cr), null),
lnk.unrounded_entered_dr) ENTERED_DR,
DECODE(SIGN(lnk.unrounded_entered_cr),-1,
DECODE(SIGN(lnk.unrounded_entered_dr),-1, ABS(lnk.unrounded_entered_dr), null),
lnk.unrounded_entered_cr) ENTERED_CR,
ent.source_id_int_1,
ent.transaction_number,
ent.entity_code,
ael.party_id,
et.event_class_code
FROM xla_transaction_entities ent,
xla_ae_headers AEH,
xla_ae_lines AEL,
xla_distribution_links LNK, --bug 7169346
gl_import_references R,
xla_event_types_b et
WHERE
ent.application_id = 222
and (ent.ledger_id = g_parameter_rec.set_of_books_id
or ent.ledger_id in (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
-- and ent.entity_code = 'TRANSACTIONS'
--and i.invoice_id = ent.source_id_int_1
and ent.entity_id = aeh.entity_id
AND AEH.ledger_id = ent.ledger_id
and aeh.ae_header_id = ael.ae_header_id
and ael.application_id = 222
AND R.gl_sl_link_id = AEL.gl_sl_link_id
AND R.je_header_id = p_journal_rec.je_header_id
AND R.je_line_num = p_journal_rec.je_line_num
AND ael.ae_header_id = lnk.ae_header_id --bug 7169346
AND ael.ae_line_num = lnk.ae_line_num --bug 7169346
AND lnk.application_id = 222 --bug 7169346
AND et.event_type_code = aeh.event_type_code;
SELECT reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_7,
reference_8,
reference_9,
reference_10
FROM gl_import_references glir
WHERE glir.je_header_id = p_journal_rec.je_header_id
AND glir.je_line_num = p_journal_rec.je_line_num;
SELECT p_journal_rec.je_header_id,
p_journal_rec.je_line_num,
p_journal_rec.ext_nit
INTO g_gl_je_rec
FROM DUAL;
SELECT nit.nit_id,
substr(party.jgzz_fiscal_code,1,14),
br.bank_name,
party.country, -- nit type
substr(party.jgzz_fiscal_code,15,1),
br.bank_name
INTO g_nit_rec.nit_id,
g_nit_rec.nit,
g_nit_rec.nit_name,
l_branch_country_code, --g_nit_rec.nit_type,
g_nit_rec.verifying_digit,
g_gl_je_rec.identifier
FROM jl_co_gl_nits nit,
hz_parties party,
ce_bank_branches_v br,
ce_bank_accounts ce_accts,
ce_bank_acct_uses_all acctuse,
ar_cash_receipts_all arcash
WHERE arcash.cash_receipt_id = ar_trx.source_id_int_1
AND arcash.set_of_books_id = g_parameter_rec.set_of_books_id
AND acctuse.bank_acct_use_id = arcash.remit_bank_acct_use_id
AND acctuse.bank_account_id = ce_accts.bank_account_id
AND ce_accts.bank_branch_id = br.branch_party_id
AND br.bank_party_id = party.party_id
AND nit.nit = substr(party.jgzz_fiscal_code,1,14);
SELECT jlcgn.nit_id,
REPLACE(rac.jgzz_fiscal_code,'-'),
rac.party_name,
SUBSTR(custacct.global_attribute10,1,30),
custacct.global_attribute12,
rac.party_number
INTO g_nit_rec.nit_id,
g_nit_rec.nit,
g_nit_rec.nit_name,
g_nit_rec.nit_type,
g_nit_rec.verifying_digit,
l_customer_num
FROM jl_co_gl_nits jlcgn,
hz_parties rac,
hz_cust_accounts custacct
WHERE custacct.cust_account_id = ar_trx.party_id
AND REPLACE(rac.jgzz_fiscal_code,'-') = jlcgn.nit(+)
AND custacct.party_id=rac.party_id;
SELECT nit_id,
nit,
name,
type,
verifying_digit
INTO g_nit_rec.nit_id,
g_nit_rec.nit,
g_nit_rec.nit_name,
g_nit_rec.nit_type,
g_nit_rec.verifying_digit
FROM jl_co_gl_nits
WHERE nit_id = g_default_nit_id;
INSERT INTO jl_co_gl_trx (transaction_id,
process_id,
set_of_books_id,
code_combination_id,
account_code,
nit_id,
period_name,
period_year,
period_num,
je_batch_id,
je_header_id,
category,
subledger_doc_number,
je_line_num,
document_number,
accounting_date,
currency_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr)
VALUES (jl_co_gl_trx_s.nextval,
g_parameter_rec.cid,
g_parameter_rec.set_of_books_id,
p_journal_rec.code_combination_id,
p_journal_rec.account_code,
g_nit_rec.nit_id,
p_journal_rec.period_name,
g_period_year,
g_period_num,
p_journal_rec.je_batch_id,
p_journal_rec.je_header_id,
p_journal_rec.category,
p_journal_rec.subl_doc_num,
p_journal_rec.je_line_num,
-- DECODE(ar_trx.reference_8,'ADJ',
-- ar_trx.reference_5,ar_trx.reference_4),
ar_trx.transaction_number,
p_journal_rec.accounting_date,
p_journal_rec.currency,
sysdate,
NVL(g_parameter_rec.user_id,-1),
sysdate,
NVL(g_parameter_rec.user_id,-1),
g_login_id,
l_acc_dr,
l_acc_cr,
l_ent_dr,
l_ent_cr );
SELECT 'TRUE'
INTO g_error_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM jl_co_gl_conc_errs jlcgce
WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
AND jlcgce.je_line_num = p_journal_rec.je_line_num);
DELETE FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
AND jlcgt.je_line_num = p_journal_rec.je_line_num;
UPDATE gl_je_lines gljl
SET co_processed_flag = 'Y'
WHERE gljl.je_header_id = p_journal_rec.je_header_id
AND gljl.je_line_num = p_journal_rec.je_line_num
AND EXISTS (SELECT 'Y'
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = gljl.je_header_id
AND jlcgt.je_line_num = gljl.je_line_num);
SELECT 'TRUE'
INTO g_error_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM jl_co_gl_conc_errs jlcgce
WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
AND jlcgce.je_line_num = p_journal_rec.je_line_num);
UPDATE gl_je_lines gljl
SET co_processed_flag = NULL
WHERE gljl.je_header_id = p_journal_rec.je_header_id
AND gljl.je_line_num = p_journal_rec.je_line_num
AND EXISTS (SELECT 'Y'
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = gljl.je_header_id
AND jlcgt.je_line_num = gljl.je_line_num);
DELETE FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
AND jlcgt.je_line_num = p_journal_rec.je_line_num;
SELECT p_journal_rec.extgl_nit_id,
p_journal_rec.ext_nit,
p_journal_rec.ext_nit_name,
p_journal_rec.ext_nit_type,
p_journal_rec.ext_nit_v_digit
INTO g_nit_rec
FROM DUAL;
SELECT nit_id
INTO g_nit_rec.nit_id
FROM jl_co_gl_nits jlcgn
WHERE nit = p_journal_rec.ext_nit;
SELECT p_journal_rec.je_header_id,
p_journal_rec.je_line_num,
p_journal_rec.ext_nit
INTO g_gl_je_rec FROM DUAL;
INSERT INTO jl_co_gl_trx (transaction_id,
process_id,
set_of_books_id,
code_combination_id,
account_code,
nit_id,
period_name,
period_year,
period_num,
je_batch_id,
je_header_id,
category,
subledger_doc_number,
je_line_num,
document_number,
accounting_date,
currency_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr)
VALUES (jl_co_gl_trx_s.nextval,
g_parameter_rec.cid,
g_parameter_rec.set_of_books_id,
p_journal_rec.code_combination_id,
p_journal_rec.account_code,
NVL(g_nit_rec.nit_id,g_default_nit_id),
p_journal_rec.period_name,
g_period_year,
g_period_num,
p_journal_rec.je_batch_id,
p_journal_rec.je_header_id,
p_journal_rec.category,
p_journal_rec.subl_doc_num,
p_journal_rec.je_line_num,
p_journal_rec.ext_doc_num,
p_journal_rec.accounting_date,
p_journal_rec.currency,
sysdate,
NVL(g_parameter_rec.user_id,-1),
sysdate,
NVL(g_parameter_rec.user_id,-1),
g_login_id,
DECODE(sign(p_journal_rec.accounted_cr),-1,
(abs(p_journal_rec.accounted_cr) +
NVL(DECODE(sign(p_journal_rec.accounted_dr),1,
p_journal_rec.accounted_dr,NULL),0)),
DECODE(sign(p_journal_rec.accounted_dr),-1,
NULL,p_journal_rec.accounted_dr)),
DECODE(sign(p_journal_rec.accounted_dr),-1,
(abs(p_journal_rec.accounted_dr) +
NVL(DECODE(sign(p_journal_rec.accounted_cr),1,
p_journal_rec.accounted_cr,NULL),0)),
DECODE(sign(p_journal_rec.accounted_cr),-1,
NULL,p_journal_rec.accounted_cr)),
DECODE(sign(p_journal_rec.entered_cr),-1,
(abs(p_journal_rec.entered_cr) +
NVL(DECODE(sign(p_journal_rec.entered_dr),1,
p_journal_rec.entered_dr,NULL),0)),
DECODE(sign(p_journal_rec.entered_dr),-1,
NULL,p_journal_rec.entered_dr)),
DECODE(sign(p_journal_rec.entered_dr),-1,
(abs(p_journal_rec.entered_dr) +
NVL(DECODE(sign(p_journal_rec.entered_cr),1,
p_journal_rec.entered_cr,NULL),0)),
DECODE(sign(p_journal_rec.entered_cr),-1,
NULL,p_journal_rec.entered_cr)) );
SELECT 'TRUE'
INTO g_error_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM jl_co_gl_conc_errs jlcgce
WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
AND jlcgce.je_line_num = p_journal_rec.je_line_num);
DELETE FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
AND jlcgt.je_line_num = p_journal_rec.je_line_num;
UPDATE gl_je_lines gljl
SET co_processed_flag = 'Y'
WHERE gljl.je_header_id = p_journal_rec.je_header_id
AND gljl.je_line_num = p_journal_rec.je_line_num
AND EXISTS (SELECT 'Y'
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id = gljl.je_header_id
AND jlcgt.je_line_num = gljl.je_line_num);
SELECT count(*)
INTO l_bal_count
FROM jl_co_gl_balances bal
WHERE bal.period_name = p_period
AND bal.period_year = p_period_year
AND bal.set_of_books_id = p_sobid
AND rownum = 1;
SELECT max((bal.period_year * 100 + bal.period_num))
INTO l_pre_period_num
FROM jl_co_gl_balances bal
WHERE (bal.period_year * 100 + bal.period_num) < p_period_year * 100 + p_period_num
AND bal.set_of_books_id = p_sobid;
INSERT INTO jl_co_gl_balances (
balance_id,
set_of_books_id,
code_combination_id,
account_code,
nit_id,
period_name,
period_num,
period_year,
currency_code,
begin_balance_cr,
begin_balance_dr,
period_net_cr,
period_net_dr,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
(select jl_co_gl_balances_s.nextval,
bal.set_of_books_id,
bal.code_combination_id,
bal.account_code,
bal.nit_id,
p_period,
p_period_num,
p_period_year,
bal.currency_code,
NVL(bal.begin_balance_cr,0)+NVL(bal.period_net_cr,0),
NVL(bal.begin_balance_dr,0)+NVL(bal.period_net_dr,0),
0,
0,
sysdate,
bal.created_by,
sysdate,
bal.last_updated_by,
bal.last_update_login
FROM jl_co_gl_balances bal
WHERE (bal.period_year * 100 + bal.period_num) = l_pre_period_num
AND bal.set_of_books_id = p_sobid);
SELECT jlcgt.set_of_books_id sobid,
jlcgt.nit_id nitid,
jlcgt.period_name period_name,
jlcgt.code_combination_id ccid,
jlcgt.account_code acccode,
jlcgt.period_year peryear,
jlcgt.period_num pernum,
glcc.account_type acctype,
NVL(sum(jlcgt.accounted_dr),0) acc_dr,
NVL(sum(jlcgt.accounted_cr),0) acc_cr
FROM gl_code_combinations glcc, jl_co_gl_trx jlcgt
WHERE jlcgt.process_id IN ( SELECT process_id
FROM jl_co_gl_conc_ctrl
WHERE NVL(balance_calculated,'N') <> 'Y'
AND set_of_books_id
= g_parameter_rec.set_of_books_id)
AND jlcgt.code_combination_id = glcc.code_combination_id
GROUP BY jlcgt.set_of_books_id,
jlcgt.nit_id,
jlcgt.period_name,
jlcgt.code_combination_id,
jlcgt.account_code,
jlcgt.period_year,
jlcgt.period_num,
glcc.account_type ;
SELECT period_set_name,
currency_code
INTO g_period_set_name,g_func_currency
FROM gl_sets_of_books glsob
WHERE glsob.set_of_books_id = trx.sobid;
SELECT balance_id
INTO l_balance_id
FROM jl_co_gl_balances jlcgb
WHERE jlcgb.set_of_books_id = trx.sobid
AND jlcgb.nit_id = trx.nitid
AND jlcgb.code_combination_id = trx.ccid
AND jlcgb.period_name = trx.period_name;
'If l_balance_id is 0 then insert ');
SELECT begin_balance_dr,
begin_balance_cr,
period_net_dr,
period_net_cr
INTO l_begin_bal_dr_prior_period,
l_begin_bal_cr_prior_period,
l_period_net_dr_prior_period,
l_period_net_cr_prior_period
FROM jl_co_gl_balances jlcgb
WHERE jlcgb.nit_id = trx.nitid
AND jlcgb.set_of_books_id = trx.sobid
AND jlcgb.code_combination_id = trx.ccid
AND (jlcgb.period_year * 100 + jlcgb.period_num) =
(SELECT max(jlcgb1.period_year * 100 +
jlcgb1.period_num)
FROM jl_co_gl_balances jlcgb1
WHERE jlcgb1.nit_id = trx.nitid
AND jlcgb1.set_of_books_id = trx.sobid
AND jlcgb1.code_combination_id = trx.ccid
AND (jlcgb1.period_year * 100 + jlcgb1.period_num) <
(trx.peryear * 100 + trx.pernum)
AND jlcgb1.period_year BETWEEN
DECODE(trx.acctype,
'R',trx.peryear, 'E',trx.peryear,
trx.peryear - 200)
AND trx.peryear );
INSERT INTO jl_co_gl_balances (
balance_id,
set_of_books_id,
code_combination_id,
account_code,
nit_id,
period_name,
period_num,
period_year,
currency_code,
begin_balance_dr,
begin_balance_cr,
period_net_dr,
period_net_cr,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES ( jl_co_gl_balances_s.nextval,
trx.sobid,
trx.ccid,
trx.acccode,
trx.nitid,
trx.period_name,
trx.pernum,
trx.peryear,
g_func_currency,
l_begin_bal_dr,
l_begin_bal_cr,
trx.acc_dr,
trx.acc_cr,
sysdate,
NVL(p_userid,-1),
sysdate,
NVL(p_userid,-1),
g_login_id);
'If l_balance_id is non 0 then update :' ||to_char(trx.acc_dr));
UPDATE jl_co_gl_balances jlcgb
SET period_net_dr = (period_net_dr + trx.acc_dr) ,
period_net_cr = (period_net_cr + trx.acc_cr),
last_update_date = sysdate,
last_updated_by = p_userid,
last_update_login = g_login_id
WHERE jlcgb.set_of_books_id = trx.sobid
AND jlcgb.nit_id = trx.nitid
AND jlcgb.code_combination_id = trx.ccid
AND jlcgb.period_name = trx.period_name;
-- Update begin balances of all future balance records for
-- each balance_trx.
-- For income statement accounts only the records in the same year
-- as the transaction needs to be updated
UPDATE jl_co_gl_balances jlcgb
SET begin_balance_dr = (begin_balance_dr + trx.acc_dr),
begin_balance_cr = (begin_balance_cr + trx.acc_cr),
last_update_date = sysdate,
last_updated_by = p_userid,
last_update_login = g_login_id
WHERE jlcgb.nit_id = trx.nitid
AND jlcgb.set_of_books_id = trx.sobid
AND jlcgb.code_combination_id = trx.ccid
AND (jlcgb.period_year * 100 + jlcgb.period_num) >
(trx.peryear * 100 + trx.pernum)
AND period_year BETWEEN trx.peryear AND
DECODE(trx.acctype, 'R',trx.peryear,
'E',trx.peryear,
trx.peryear * 100);
UPDATE jl_co_gl_conc_ctrl
SET status = DECODE(process_id,p_cid,'P',status),
balance_calculated = 'Y',
last_update_date = sysdate,
last_updated_by = p_userid,
last_update_login = g_login_id
WHERE NVL(balance_calculated,'N') <> 'Y'
AND set_of_books_id = g_parameter_rec.set_of_books_id;
SELECT jlcgt.set_of_books_id sobid,
jlcgt.nit_id nitid,
jlcgt.period_name period_name,
jlcgt.code_combination_id ccid,
jlcgt.account_code acccode,
jlcgt.period_year peryear,
jlcgt.period_num pernum,
glcc.account_type acctype,
NVL(sum(jlcgt.accounted_dr),0) acc_dr,
NVL(sum(jlcgt.accounted_cr),0) acc_cr
FROM gl_code_combinations glcc,
jl_co_gl_trx jlcgt
WHERE process_id = p_rcid
AND jlcgt.code_combination_id = glcc.code_combination_id
GROUP BY jlcgt.set_of_books_id,
jlcgt.nit_id,
jlcgt.period_name,
jlcgt.code_combination_id,
jlcgt.account_code,
jlcgt.period_year,
jlcgt.period_num,
glcc.account_type;
UPDATE jl_co_gl_balances jlcgb
-- period_net should be updated only for the purge period
SET period_net_dr =
(period_net_dr - DECODE(jlcgb.period_name, trx.period_name,
trx.acc_dr,0)),
period_net_cr =
(period_net_cr - DECODE(jlcgb.period_name, trx.period_name,
trx.acc_cr,0)),
-- begin_balance for all future periods in the current year
-- only needs to be corrected for Income Statement accounts,
-- and all future periods for balance sheet accounts
begin_balance_dr =
(begin_balance_dr - DECODE(jlcgb.period_name, trx.period_name,
0,trx.acc_dr)) ,
begin_balance_cr =
(begin_balance_cr - DECODE(jlcgb.period_name, trx.period_name,
0,trx.acc_cr)),
last_update_date = sysdate,
last_updated_by = p_userid ,
last_update_login = g_login_id
WHERE jlcgb.set_of_books_id = trx.sobid
AND jlcgb.nit_id = trx.nitid
AND jlcgb.code_combination_id = trx.ccid
AND jlcgb.period_name IN (SELECT period_name
FROM gl_periods
WHERE period_set_name = g_period_set_name
AND (period_year * 100 + period_num) >=
(trx.peryear * 100 + trx.pernum)
AND period_year BETWEEN trx.peryear AND
DECODE(trx.acctype,
'R',trx.peryear,'E',trx.peryear,
trx.peryear * 100));
DELETE FROM jl_co_gl_balances jlcgb
WHERE jlcgb.set_of_books_id = trx.sobid
AND jlcgb.nit_id = trx.nitid
AND jlcgb.code_combination_id = trx.ccid
AND jlcgb.period_name = trx.period_name
AND jlcgb.period_net_dr = 0 AND jlcgb.period_net_cr = 0
AND NOT EXISTS (SELECT 1 FROM jl_co_gl_trx jlcgt
WHERE jlcgt.nit_id = trx.nitid
AND jlcgt.code_combination_id = trx.ccid
AND jlcgt.period_name = trx.period_name
AND jlcgt.set_of_books_id = trx.sobid);
END LOOP; -- UPDATE balances
UPDATE gl_je_lines gljl
SET co_processed_flag = 'N'
WHERE co_processed_flag = 'Y'
AND status = 'P'
AND EXISTS (SELECT 1
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.process_id = p_rcid
AND jlcgt.je_header_id = gljl.je_header_id
AND jlcgt.je_line_num = gljl.je_line_num );
DELETE FROM jl_co_gl_trx
WHERE process_id = p_rcid ;
UPDATE jl_co_gl_conc_ctrl
SET status = 'P',
reversed_process_id = p_rcid,
last_update_date = sysdate,
last_updated_by = p_userid,
last_update_login = g_login_id
WHERE process_id = p_cid;
UPDATE jl_co_gl_conc_ctrl
SET status = 'R',
last_update_date = sysdate,
last_updated_by = p_userid,
last_update_login = g_login_id
WHERE process_id = p_rcid;
DELETE FROM jl_co_gl_conc_errs;
SELECT application_column_name,
id_flex_num
INTO g_account_segment,
g_chart_of_accounts_id
FROM fnd_segment_attribute_values fndsav
WHERE fndsav.id_flex_code = 'GL#'
AND fndsav.segment_attribute_type = 'GL_ACCOUNT'
AND fndsav.attribute_value = 'Y'
AND application_id = 101
AND fndsav.id_flex_num = (SELECT chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = p_sobid);
SELECT TO_NUMBER(NVL(fnd_profile.value('LOGIN_ID'),-1))
INTO g_login_id
FROM DUAL;
SELECT jl_co_gl_conc_ctrl_s.nextval,
p_sobid,
TO_NUMBER(NVL(fnd_profile.value('USER_ID') ,-1)),
p_rcid
INTO g_parameter_rec
FROM dual;
INSERT INTO jl_co_gl_conc_ctrl (
process_id,
set_of_books_id,
period_name,
reversed_process_id,
status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
balance_calculated)
VALUES ( jl_co_gl_conc_ctrl_s.currval,
p_sobid,
DECODE(p_rcid,NULL,p_period,NULL),
p_rcid,
'E',
sysdate,
nvl(g_parameter_rec.user_id,-1),
sysdate,
nvl(g_parameter_rec.user_id,-1),
g_login_id,
NULL);
SELECT nit_id
INTO g_default_nit_id
FROM jl_co_gl_nits
WHERE nit = '0';
INSERT INTO jl_co_gl_nits (
nit_id,
nit,
type,
verifying_digit,
name,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES ( jl_co_gl_nits_s.nextval,
0,
'LEGAL_ENTITY',
'0',
'Default',
sysdate,
1,
sysdate,
1,
null);
SELECT nit_id
INTO g_default_nit_id
FROM jl_co_gl_nits
WHERE nit = '0';
SELECT period_set_name
INTO g_period_set_name
FROM gl_sets_of_books glsob
WHERE glsob.set_of_books_id = p_sobid;
SELECT period_num,
period_year
INTO g_period_num,
g_period_year
FROM gl_periods
WHERE period_set_name = g_period_set_name
AND period_name = p_period;
INSERT INTO jl_co_gl_trx(
transaction_id,
process_id,
set_of_books_id,
code_combination_id,
account_code,
nit_id,
period_name,
period_year,
period_num,
je_batch_id,
je_header_id,
category,
subledger_doc_number,
je_line_num,
document_number,
accounting_date,
currency_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr)
(SELECT jl_co_gl_trx_s.nextval,
g_parameter_rec.cid,
g_parameter_rec.set_of_books_id,
reverse_rec_tbl(j_line).code_combination_id,
reverse_rec_tbl(j_line).account_code,
jlcgt.nit_id,
reverse_rec_tbl(j_line).period_name,
g_period_year,
g_period_num,
reverse_rec_tbl(j_line).je_batch_id,
reverse_rec_tbl(j_line).je_header_id,
reverse_rec_tbl(j_line).category,
reverse_rec_tbl(j_line).subl_doc_num,
reverse_rec_tbl(j_line).je_line_num,
jlcgt.document_number,
reverse_rec_tbl(j_line).accounting_date,
reverse_rec_tbl(j_line).currency,
sysdate,
NVL(g_parameter_rec.user_id,-1),
sysdate,
NVL(g_parameter_rec.user_id,-1),
g_login_id,
jlcgt.accounted_cr,
jlcgt.accounted_dr,
jlcgt.entered_cr,
jlcgt.entered_dr
FROM jl_co_gl_trx jlcgt
WHERE jlcgt.je_header_id
= reverse_rec_tbl(j_line).reversed_je_header_id
AND jlcgt.je_line_num
= reverse_rec_tbl(j_line).je_line_num );