[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,
-- Bug 16529685 Start
gljh.je_from_sla_flag,
-- Bug 16529685 End
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'
AND not exists (select 1 --filtering for intercompany lines created in GL -- Begin of bug10084267 fix
from gl_je_lines jl,
fnd_new_messages fnm
where fnm.message_name in ('PPOS0220', 'PPOS0221', 'PPOS0222', 'PPOS0243', 'PPOS0222_G','PPOSO275')
and fnm.application_id = 101 --bug 13355793
and language_code = USERENV('LANG')
and fnm.message_text = jl.description
and jl.je_header_id = gljl.je_header_id
and jl.je_line_num = gljl.je_line_num)
AND not exists (select 1 -- filtering for intercompany lines created in SLA
from xla_ae_lines ael,
gl_import_references gir
where gir.je_header_id = gljl.je_header_id
and gir.je_line_num = gljl.je_line_num
and gir.gl_sl_link_id = ael.gl_sl_link_id
and ael.accounting_class_code in ('INTER','INTRA')) --End of bug10084267 fix
ORDER BY gljh.je_header_id, nvl(gljh.reversed_je_header_id,0); --bug 8391172
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 name = p_nit_rec.nit_name; -- Bug 8589204 Removed the UPPER fn
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);
FND_FILE.PUT_LINE(FND_FILE.log,'inserted in insert_error_rec');
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);
Select distinct ent.entity_code
Into l_entity_code
From xla_transaction_entities ent,
xla_ae_headers aeh,
xla_ae_lines ael,
gl_import_references imr
Where ent.application_id = 200
And ent.application_id =aeh.application_id
And aeh.application_id = ael.application_id
And ent.entity_id = aeh.entity_id
And (aeh.ledger_id = g_parameter_rec.set_of_books_id
OR
aeh.ledger_id In (Select from_ledger_id
From gl_consolidation
Where to_ledger_id = g_parameter_rec.set_of_books_id)
)
AND aeh.ae_header_id = ael.ae_header_id
And ael.application_id = 200
And imr.gl_sl_link_id = ael.gl_sl_link_id
And imr.je_header_id = p_journal_rec.je_header_id
And imr.je_line_num = p_journal_rec.je_line_num;
Fnd_file.Put_Line(Fnd_file.log, 'Exception occured in the Select Statement');
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_dr),-1,
(ABS(lnk.unrounded_accounted_dr) + NVL(DECODE(SIGN(lnk.unrounded_accounted_cr),1,lnk.unrounded_accounted_cr,null),0)),
DECODE(SIGN(lnk.unrounded_accounted_cr),-1,null, lnk.unrounded_accounted_cr)) ACCOUNTED_CR,
DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
(ABS(lnk.unrounded_accounted_cr) + NVL(DECODE(SIGN(lnk.unrounded_accounted_dr),1,lnk.unrounded_accounted_dr,null),0)),
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,null, lnk.unrounded_accounted_dr)) ACCOUNTED_DR,
DECODE(SIGN(lnk.unrounded_entered_cr),-1,
(ABS(lnk.unrounded_entered_cr) + NVL(DECODE(SIGN(lnk.unrounded_entered_dr),1,lnk.unrounded_entered_dr,null),0)),
DECODE(SIGN(lnk.unrounded_entered_dr),-1,null, lnk.unrounded_entered_dr)) ENTERED_DR,
DECODE(SIGN(lnk.unrounded_entered_dr),-1,
(ABS(lnk.unrounded_entered_dr) + NVL(DECODE(SIGN(lnk.unrounded_entered_cr),1,lnk.unrounded_entered_cr,null),0)),
DECODE(SIGN(lnk.unrounded_entered_cr),-1,null, lnk.unrounded_entered_cr)) ENTERED_CR,
-- Bug 13805457 End
lnk.source_distribution_type SOURCE_TABLE,
ael.party_id PARTY_ID,
ent.transaction_number TRX_NUMBER_C ,
I.invoice_id TRX_HDR_ID,
I.invoice_type_lookup_code TRX_TYPE, -- ER 9435028
I.source TRX_SOURCE, -- ER 9435028
'INV' TRX_CLASS,
ael.accounting_class_code ACCT_LINE_TYPE,
D.invoice_distribution_id TRX_DIST_ID,
aeh.upg_batch_id UPG_BATCH_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
--bug8680825
/*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 --bug8680825
AND (aeh.ledger_id = g_parameter_rec.set_of_books_id
OR
aeh.ledger_id IN (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
)
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.upg_batch_id is null
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND lnk.source_distribution_type <> 'AP_PREPAY' --- bug 7428486
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
-- Bug 13805457 Start
SELECT DECODE(SIGN(AEL.unrounded_accounted_dr),-1,
(ABS(AEL.unrounded_accounted_dr) + NVL(DECODE(SIGN(AEL.unrounded_accounted_cr),1,AEL.unrounded_accounted_cr,null),0)),
DECODE(SIGN(AEL.unrounded_accounted_cr),-1,null, AEL.unrounded_accounted_cr)) ACCOUNTED_CR,
DECODE(SIGN(AEL.unrounded_accounted_cr),-1,
(ABS(AEL.unrounded_accounted_cr) + NVL(DECODE(SIGN(AEL.unrounded_accounted_dr),1,AEL.unrounded_accounted_dr,null),0)),
DECODE(SIGN(AEL.unrounded_accounted_dr),-1,null, AEL.unrounded_accounted_dr)) ACCOUNTED_DR,
DECODE(SIGN(AEL.unrounded_entered_cr),-1,
(ABS(AEL.unrounded_entered_cr) + NVL(DECODE(SIGN(AEL.unrounded_entered_dr),1,AEL.unrounded_entered_dr,null),0)),
DECODE(SIGN(AEL.unrounded_entered_dr),-1,null, AEL.unrounded_entered_dr)) ENTERED_DR,
DECODE(SIGN(AEL.unrounded_entered_dr),-1,
(ABS(AEL.unrounded_entered_dr) + NVL(DECODE(SIGN(AEL.unrounded_entered_cr),1,AEL.unrounded_entered_cr,null),0)),
DECODE(SIGN(AEL.unrounded_entered_cr),-1,null, AEL.unrounded_entered_cr)) ENTERED_CR,
-- Bug 13805457 End
ent.entity_code SOURCE_TABLE,
ael.party_id PARTY_ID,
TO_CHAR(C.CHECK_NUMBER) TRX_NUMBER_C ,
C.CHECK_ID TRX_HDR_ID,
NULL TRX_TYPE, -- ER 9435028
NULL TRX_SOURCE, -- ER 9435028
'PAY' TRX_CLASS,
ael.accounting_class_code ACCT_LINE_TYPE,
NULL TRX_DIST_ID,
aeh.upg_batch_id UPG_BATCH_ID
FROM
ap_checks_all C,
xla_transaction_entities ent,
xla_ae_headers AEH,
xla_ae_lines AEL,
ap_payment_history_all D, -- bug 8673098
gl_import_references R
WHERE
ent.application_id = 200
--bug8680825
/*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 c.check_id = D.check_id
AND ent.entity_id = aeh.entity_id
--AND AEH.ledger_id = ent.ledger_id --bug8680825
AND (aeh.ledger_id = g_parameter_rec.set_of_books_id
OR
aeh.ledger_id IN (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
)
and aeh.ae_header_id = ael.ae_header_id
and ael.application_id = 200
AND D.accounting_event_id = aeh.event_id
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
-- Bug 13805457 Start
SELECT DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
(ABS(lnk.unrounded_accounted_dr) + NVL(DECODE(SIGN(lnk.unrounded_accounted_cr),1,lnk.unrounded_accounted_cr,null),0)),
DECODE(SIGN(lnk.unrounded_accounted_cr),-1,null, lnk.unrounded_accounted_cr)) ACCOUNTED_CR,
DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
(ABS(lnk.unrounded_accounted_cr) + NVL(DECODE(SIGN(lnk.unrounded_accounted_dr),1,lnk.unrounded_accounted_dr,null),0)),
DECODE(SIGN(lnk.unrounded_accounted_dr),-1,null, lnk.unrounded_accounted_dr)) ACCOUNTED_DR,
DECODE(SIGN(lnk.unrounded_entered_cr),-1,
(ABS(lnk.unrounded_entered_cr) + NVL(DECODE(SIGN(lnk.unrounded_entered_dr),1,lnk.unrounded_entered_dr,null),0)),
DECODE(SIGN(lnk.unrounded_entered_dr),-1,null, lnk.unrounded_entered_dr)) ENTERED_DR,
DECODE(SIGN(lnk.unrounded_entered_dr),-1,
(ABS(lnk.unrounded_entered_dr) + NVL(DECODE(SIGN(lnk.unrounded_entered_cr),1,lnk.unrounded_entered_cr,null),0)),
DECODE(SIGN(lnk.unrounded_entered_cr),-1,null, lnk.unrounded_entered_cr)) ENTERED_CR,
-- Bug 13805457 End
lnk.source_distribution_type SOURCE_TABLE,
ael.party_id PARTY_ID,
ent.transaction_number TRX_NUMBER_C ,
I.invoice_id TRX_HDR_ID,
I.invoice_type_lookup_code TRX_TYPE, -- ER 9435028
I.source TRX_SOURCE, -- ER 9435028
'INV' TRX_CLASS,
ael.accounting_class_code ACCT_LINE_TYPE,
D.invoice_distribution_id TRX_DIST_ID,
aeh.upg_batch_id UPG_BATCH_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
--bug8680825
/*and (ent.ledger_id = g_parameter_rec.set_of_books_id
---AND lnk.source_distribution_type = 'AP_PREPAY' ---bug 7428486
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 --bug8680825
AND (aeh.ledger_id = g_parameter_rec.set_of_books_id
OR
aeh.ledger_id IN (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
)
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.upg_batch_id is null
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 lnk.source_distribution_type = 'AP_PREPAY'
--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
UNION ALL
SELECT DECODE(SIGN(AEL.unrounded_accounted_dr),-1,
(ABS(AEL.unrounded_accounted_dr) + NVL(DECODE(SIGN(AEL.unrounded_accounted_cr),1,AEL.unrounded_accounted_cr,null),0)),
DECODE(SIGN(AEL.unrounded_accounted_cr),-1,null, AEL.unrounded_accounted_cr)) ACCOUNTED_CR,
DECODE(SIGN(AEL.unrounded_accounted_cr),-1,
(ABS(AEL.unrounded_accounted_cr) + NVL(DECODE(SIGN(AEL.unrounded_accounted_dr),1,AEL.unrounded_accounted_dr,null),0)),
DECODE(SIGN(AEL.unrounded_accounted_dr),-1,null, AEL.unrounded_accounted_dr)) ACCOUNTED_DR,
DECODE(SIGN(AEL.unrounded_entered_cr),-1,
(ABS(AEL.unrounded_entered_cr) + NVL(DECODE(SIGN(AEL.unrounded_entered_dr),1,AEL.unrounded_entered_dr,null),0)),
DECODE(SIGN(AEL.unrounded_entered_dr),-1,null, AEL.unrounded_entered_dr)) ENTERED_DR,
DECODE(SIGN(AEL.unrounded_entered_dr),-1,
(ABS(AEL.unrounded_entered_dr) + NVL(DECODE(SIGN(AEL.unrounded_entered_cr),1,AEL.unrounded_entered_cr,null),0)),
DECODE(SIGN(AEL.unrounded_entered_cr),-1,null, AEL.unrounded_entered_cr)) ENTERED_CR,
ael.source_table SOURCE_TABLE,
ael.party_id PARTY_ID,
ent.transaction_number TRX_NUMBER_C ,
I.invoice_id TRX_HDR_ID,
I.invoice_type_lookup_code TRX_TYPE, -- ER 9435028
I.source TRX_SOURCE, -- ER 9435028
'INV' TRX_CLASS,
ael.accounting_class_code ACCT_LINE_TYPE,
decode(ael.source_table,'AP_INVOICE_DISTRIBUTIONS',ael.source_id, NULL) TRX_DIST_ID,
aeh.upg_batch_id UPG_BATCH_ID
FROM
ap_invoices_all I,
xla_transaction_entities ent,
xla_ae_headers AEH,
xla_ae_lines AEL,
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.entity_code = 'AP_INVOICES'
AND i.invoice_id = ent.source_id_int_1
AND ent.entity_id = aeh.entity_id
AND (aeh.ledger_id = g_parameter_rec.set_of_books_id
OR
aeh.ledger_id IN (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
)
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.upg_batch_id is not null
AND ael.application_id = 200
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)), invoice_line_number
INTO l_supplier_num, l_invoice_line_number
FROM ap_invoice_distributions_all apida
WHERE apida.invoice_id = ap_trx.trx_hdr_id
AND apida.invoice_distribution_id = ap_trx.trx_dist_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 trim(merchant_taxpayer_id), trim(merchant_name)
INTO l_nit, l_nit_name
FROM ap_invoice_lines_all lines
WHERE lines.invoice_id = ap_trx.trx_hdr_id
AND lines.line_number = l_invoice_line_number;
SELECT jlcgn.nit_id
INTO g_nit_rec.nit_id
FROM jl_co_gl_nits jlcgn
WHERE jlcgn.nit = substr(l_nit,1,instr(l_nit,'-')-1);
SELECT substr(l_nit,1,instr(l_nit,'-')-1),
l_nit_name,
'MERCHANT',
substr(l_nit,instr(l_nit,'-')+1,1)
INTO g_nit_rec.nit,
g_nit_rec.nit_name,
g_nit_rec.nit_type,
g_nit_rec.verifying_digit
FROM dual;
FND_FILE.PUT_LINE(FND_FILE.log,'Exception while inserting NIT information into g_nit_rec '||SQLERRM);
SELECT jlcgn.nit_id
INTO g_nit_rec.nit_id
FROM jl_co_gl_nits jlcgn
WHERE jlcgn.nit = l_nit;
SELECT l_nit,
l_nit_name,
'MERCHANT'
INTO g_nit_rec.nit,
g_nit_rec.nit_name,
g_nit_rec.nit_type
FROM dual;
FND_FILE.PUT_LINE(FND_FILE.log,'Exception while inserting NIT information into g_nit_rec '||SQLERRM);
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 trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) 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 trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) 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;
UPDATE gl_je_lines gljl
SET co_processed_flag = 'N'
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;
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 = 'N'
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);
FND_FILE.PUT_LINE(FND_FILE.log,'Delete jl_co_gl_trx');
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
FROM jl_co_gl_nits jlcgn,
po_headers_all poha,
PO_DISTRIBUTIONS_ALL podis,
(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 trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf,
xla_ae_lines AEL,
gl_import_references R,
RCV_RECEIVING_SUB_LEDGER RCVSD,
xla_distribution_links LNK
WHERE povapf.vendor_id = poha.vendor_id
AND REPLACE(povapf.num_1099,'-') = jlcgn.nit(+)
AND R.je_header_id = p_journal_rec.je_header_id
AND R.je_line_num = p_journal_rec.je_line_num
AND R.gl_sl_link_id = AEL.gl_sl_link_id
AND AEL.application_id = 707
AND AEL.ae_header_id = LNK.ae_header_id
AND AEL.ae_line_num = LNK.ae_line_num
AND LNK.application_id = 707
AND LNK.SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER'
AND LNK.SOURCE_DISTRIBUTION_ID_NUM_1 = RCVSD.RCV_SUB_LEDGER_ID
AND RCVSD.reference3 = podis.PO_DISTRIBUTION_ID
AND podis.po_header_id = poha.po_header_id
UNION
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
FROM jl_co_gl_nits jlcgn,
po_headers_all poha,
PO_DISTRIBUTIONS_ALL podis,
cst_write_offs cwo,
xla_ae_lines AEL, gl_import_references gir,
xla_distribution_links LNK,(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 trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
WHERE povapf.vendor_id = poha.vendor_id
AND REPLACE(povapf.num_1099,'-') = jlcgn.nit(+)
AND gir.je_header_id = p_journal_rec.je_header_id
AND gir.je_line_num = p_journal_rec.je_line_num
AND gir.gl_sl_link_id = AEL.gl_sl_link_id
AND AEL.application_id = 707
AND AEL.ae_header_id = LNK.ae_header_id
AND AEL.ae_line_num = LNK.ae_line_num
AND LNK.application_id = 707
AND LNK.SOURCE_DISTRIBUTION_TYPE = 'CST_WRITE_OFFS'
AND LNK.SOURCE_DISTRIBUTION_ID_NUM_1 = cwo.write_off_id
AND cwo.po_distribution_id = podis.PO_DISTRIBUTION_ID
AND podis.po_header_id = poha.po_header_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,
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);
UPDATE gl_je_lines gljl
SET co_processed_flag = 'N'
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;
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 jlcgn.nit_id,
REPLACE(povpapf.num_1099,'-'),
povpapf.vendor_name,
SUBSTR(povpapf.global_attribute10,1,30),
povpapf.global_attribute12,
povpapf.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(pov.individual_1099,pov.num_1099)) num_1099,
pov.vendor_name,
pov.global_attribute10,
pov.global_attribute12,
pov.segment1,
pov.vendor_id
FROM po_vendors pov,
(select distinct person_id ,national_identifier from per_all_people_f
WHERE nvl(EFFECTIVE_END_DATE,sysdate) >= sysdate) papf --bug 8664192
WHERE nvl(pov.employee_id, -99) = papf.person_id (+))povpapf
WHERE poha.po_header_id = TO_NUMBER(p_journal_rec.ref_2)
AND povpapf.vendor_id = poha.vendor_id
AND REPLACE(povpapf.num_1099,'-') = jlcgn.nit(+);
FND_FILE.PUT_LINE(FND_FILE.log,'Select successfull for 11i PO, executing Validate_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 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 NVL(jlcgn1.nit_id, jlcgn.nit_id),
REPLACE(NVL(apbb1.global_attribute11,
apbb.global_attribute11),'-'),
NVL(apbb1.bank_name,apbb.bank_name),
SUBSTR(NVL(apbb1.global_attribute10,
apbb.global_attribute10),1,30),
NVL(apbb1.global_attribute12,apbb.global_attribute12),
NVL(apbb1.bank_name,apbb.bank_name)
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
FROM jl_co_gl_nits jlcgn1,
jl_co_gl_nits jlcgn,
ap_bank_branches apbb1,
ap_bank_branches apbb,
ap_bank_accounts_all apbaa,
ar_cash_receipts_all arcra
WHERE arcra.cash_receipt_id = TO_NUMBER(ar_trx.reference_2)
AND arcra.set_of_books_id = g_parameter_rec.set_of_books_id
AND arcra.remittance_bank_account_id = apbaa.bank_account_id
AND apbaa.bank_branch_id = apbb.bank_branch_id
AND apbb1.bank_branch_id(+) =
TO_NUMBER(NVL(apbb.global_attribute14,'0'))
AND REPLACE(apbb.global_attribute11,'-') = jlcgn.nit(+)
AND REPLACE(apbb1.global_attribute11,'-') = jlcgn1.nit(+);
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 = TO_NUMBER(ar_trx.reference_7)
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),
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;
SELECT DECODE(SIGN(AEL.accounted_dr),-1,
(ABS(AEL.accounted_dr) + NVL(DECODE(SIGN(AEL.accounted_cr),1,AEL.accounted_cr,null),0)),
DECODE(SIGN(AEL.accounted_cr),-1,null, AEL.accounted_cr)) ACCOUNTED_CR,
DECODE(SIGN(AEL.accounted_cr),-1,
(ABS(AEL.accounted_cr) + NVL(DECODE(SIGN(AEL.accounted_dr),1,AEL.accounted_dr,null),0)),
DECODE(SIGN(AEL.accounted_dr),-1,null, AEL.accounted_dr)) ACCOUNTED_DR,
DECODE(SIGN(AEL.entered_cr),-1,
(ABS(AEL.entered_cr) + NVL(DECODE(SIGN(AEL.entered_dr),1,AEL.entered_dr,null),0)),
DECODE(SIGN(AEL.entered_dr),-1,null, AEL.entered_dr)) ENTERED_DR,
DECODE(SIGN(AEL.entered_dr),-1,
(ABS(AEL.entered_dr) + NVL(DECODE(SIGN(AEL.entered_cr),1,AEL.entered_cr,null),0)),
DECODE(SIGN(AEL.entered_cr),-1,null, AEL.entered_cr)) ENTERED_CR,
-- Dalase Bug 16074336 End
-- Bug 13805457 End
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,
gl_import_references R,
xla_event_types_b et
WHERE
ent.application_id = 222
--bug8680825
/*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 --bug8680825
AND aeh.ae_header_id = ael.ae_header_id
AND (aeh.ledger_id = g_parameter_rec.set_of_books_id --bug8680825
OR
aeh.ledger_id IN (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_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 et.event_type_code = aeh.event_type_code;
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,
-- Bug 14283808 Start
substr(party.jgzz_fiscal_code,1,decode(instr(party.jgzz_fiscal_code,'-'),0,length(party.jgzz_fiscal_code)-1,instr(party.jgzz_fiscal_code,'-')-1)), --bug9078068
-- Bug 14283808 End
br.bank_name,
-- Bug 14283808 Start
--party.country, -- nit type
BankOrgProfile.home_country,
substr(party.jgzz_fiscal_code,decode(instr(party.jgzz_fiscal_code,'-'),0,length(party.jgzz_fiscal_code),instr(party.jgzz_fiscal_code,'-')+1),1), --bug9078068
-- Bug 14283808 End
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,
-- Bug 14283808 Start
ar_cash_receipts_all arcash,
hz_organization_profiles BankOrgProfile
-- Bug 14283808 End
WHERE arcash.cash_receipt_id = ar_trx.source_id_int_1
--AND arcash.set_of_books_id = g_parameter_rec.set_of_books_id --BUG 9078068
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
-- Bug 14283808 Start
AND BankOrgProfile.party_id = party.party_id
AND SYSDATE BETWEEN TRUNC(BankOrgProfile.effective_start_date) AND NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
AND nit.nit = substr(party.jgzz_fiscal_code,1,decode(instr(party.jgzz_fiscal_code,'-'),0,length(party.jgzz_fiscal_code)-1, instr(party.jgzz_fiscal_code,'-')-1));
SELECT jlcgn.nit_id,
substr(jgzz_fiscal_code,1,decode(instr(jgzz_fiscal_code,'-'),0,14,instr(jgzz_fiscal_code,'-')-1)), --bug8685975
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 substr(jgzz_fiscal_code,1,decode(instr(jgzz_fiscal_code,'-'),0,14,instr(jgzz_fiscal_code,'-')-1)) = jlcgn.nit(+) --bug8685975
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);
UPDATE gl_je_lines gljl
SET co_processed_flag = 'N'
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;
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 = 'N'
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;
FND_FILE.PUT_LINE(FND_FILE.log,'Generate_Non_Oracle_trx: start - Before select into g_nit_rec ');
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;
FND_FILE.PUT_LINE(FND_FILE.log,'Values after select into g_nit_rec ');
SELECT nit_id
INTO g_nit_rec.nit_id
FROM jl_co_gl_nits jlcgn
WHERE nit = p_journal_rec.ext_nit;
FND_FILE.PUT_LINE(FND_FILE.log,'After select into g_nit_rec.nit_id from jl_co_gl_nits.nit_id');
FND_FILE.PUT_LINE(FND_FILE.log,'Before select into g_gl_je_rec from p_journal_rec');
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;
FND_FILE.PUT_LINE(FND_FILE.log,'After select into g_gl_je_rec from p_journal_rec');
FND_FILE.PUT_LINE(FND_FILE.log,'Before insert into jl_co_gl_trx');
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))
, max(bal.period_year) --bug 10207969
INTO l_pre_period_num
, l_pre_period_year --bug 10207969
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,
decode(p_period_year, l_pre_period_year,
NVL(bal.begin_balance_cr,0)+NVL(bal.period_net_cr,0),
decode(glcc.account_type, 'R', 0, 'E', 0,
NVL(bal.begin_balance_cr,0)+NVL(bal.period_net_cr,0))), --bug 10207969
decode(p_period_year, l_pre_period_year,
NVL(bal.begin_balance_dr,0)+NVL(bal.period_net_dr,0),
decode(glcc.account_type, 'R', 0, 'E', 0,
NVL(bal.begin_balance_dr,0)+NVL(bal.period_net_dr,0))), --bug 10207969
0,
0,
sysdate,
bal.created_by,
sysdate,
bal.last_updated_by,
bal.last_update_login
FROM jl_co_gl_balances bal
, gl_code_combinations glcc --bug 10207969
WHERE (bal.period_year * 100 + bal.period_num) = l_pre_period_num
AND bal.code_combination_id = glcc.code_combination_id --bug 10207969
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,
sum(nvl(jlcgt.accounted_dr,0)) acc_dr, --bug 10207969
sum(nvl(jlcgt.accounted_cr,0)) acc_cr --bug 10207969
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,
sum(nvl(jlcgt.accounted_dr,0)) acc_dr, --bug 10207969
sum(nvl(jlcgt.accounted_cr,0)) acc_cr --bug 10207969
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));
* for OTHER PROCESS_ID, then we can safely delete balances for SAME SOB,
* NIT, CCID and PERIOD since this cursor already grouped this info for
* current PROCESS_ID, meaning there are NO other TRXs using such balance */
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
-- bug 8339893 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
AND jlcgt.process_id <> p_rcid); --bug 8339893
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;
Select aeh.upg_batch_id upg_batch_id
FROM xla_transaction_entities ent,
xla_ae_headers AEH,
xla_ae_lines AEL,
gl_import_references R
WHERE ent.application_id = 222
AND ent.entity_id = aeh.entity_id
AND aeh.ae_header_id = ael.ae_header_id
AND (aeh.ledger_id = g_parameter_rec.set_of_books_id --bug8680825
OR
aeh.ledger_id IN (SELECT from_ledger_id
FROM gl_consolidation
WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
AND ael.application_id = 222
AND R.gl_sl_link_id = AEL.gl_sl_link_id
AND R.je_header_id = j_line.je_header_id
AND R.je_line_num = j_line.je_line_num;
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,
-- Bug 9441034 Start
reverse_rec_tbl(j_line).accounted_dr,
reverse_rec_tbl(j_line).accounted_cr,
reverse_rec_tbl(j_line).entered_dr,
reverse_rec_tbl(j_line).entered_cr
-- Bug 9441034 Start
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 );
UPDATE gl_je_lines gljl
SET co_processed_flag = 'Y'
WHERE gljl.je_header_id = reverse_rec_tbl(j_line).je_header_id
AND gljl.je_line_num = reverse_rec_tbl(j_line).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);