[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO
igi_ar_journal_interim
(
status,
actual_flag,
request_id,
created_by,
date_created,
set_of_books_id,
je_source_name,
je_category_name,
transaction_date,
accounting_date,
currency_code,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference10,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30
)
SELECT
'NEW' status,
'A' actual_flag,
p_Report.ReqId request_id,
fnd_global.user_id created_by,
trunc(sysdate) date_created,
p_Report.SetOfBooksId sob_id,
'Receivables' source,
'Adjustment' category,
adj.apply_date trx_date,
adj.gl_date gl_date,
ct.invoice_currency_code currency,
ard.code_combination_id ccid,
ard.amount_dr entered_dr,
ard.amount_cr entered_cr,
ard.acctd_amount_dr acctd_dr,
ard.acctd_amount_cr acctd_cr,
l_cat.meaning ref10,
to_char(p_Report.ReqId) ref21,
to_char(adj.adjustment_id) ref22,
to_char(ard.line_id) ref23,
to_char(null) ref24,
ct.trx_number ref25,
hz_cust_accounts.account_number ref26, -- Bug 3902175
ct.bill_to_customer_id ref27,
'ADJ' ref28,
'ADJ' || ard.source_type ref29,
'AR_ADJUSTMENTS' ref30
FROM
ra_customer_trx_all ct,
ra_cust_trx_types_all ctt,
ar_distributions_all ard,
hz_parties, -- Bug 3902175
hz_cust_accounts, -- Bug 3902175
ar_adjustments_all adj,
ar_lookups l_cat,
xla_ae_headers xah
WHERE
adj.adjustment_id +0 < p_Report.NxtAdjustmentId
and adj.set_of_books_id = p_Report.SetOfBooksId
and nvl(adj.postable,'Y') = 'Y'
and adj.adjustment_id = ard.source_id
and ard.source_table = 'ADJ'
and adj.customer_trx_id = ct.customer_trx_id
and ctt.cust_trx_type_id = ct.cust_trx_type_id
and hz_cust_accounts.cust_account_id = ct.bill_to_customer_id -- Bug 3902175
and hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
and l_cat.lookup_code = 'ADJ_' || ard.source_type
and ct.invoice_currency_code = decode( p_Report.FuncCurr,
null,ct.invoice_currency_code,
p_Report.FuncCurr)
and p_Report.adj = 'Y'
and adj.gl_date between p_Report.GlDateFrom
and p_Report.GlDateTo
and adj.posting_control_id > 0
and xah.event_id = adj.event_id
and xah.application_id = l_xah_ar_application_id
and xah.ledger_id = adj.set_of_books_id
and xah.ledger_id = p_Report.SetOfBooksId
and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah.gl_transfer_status_code = 'Y'
and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
/* Added for bug 6647672 start */
and NOT EXISTS ( select 'Y'
from xla_ae_headers xah2
where xah2.event_id = adj.event_id
and xah2.application_id = l_xah_ar_application_id
and xah2.ledger_id = adj.set_of_books_id
and xah2.ledger_id = p_Report.CashSetOfBooksId
and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah2.gl_transfer_status_code = 'Y'
and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo );
/* and exists ( select 'x'
from igi_ar_adjustments
where a djustment_id = adj.adjustment_id
and arc_posting_control_id = -3
)
and nvl(adj.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
between
decode(p_Report.PostedDateFrom ,
null, nvl(adj.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom )
and
decode( p_Report.PostedDateTo ,
null, nvl(adj.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo );
INSERT INTO
igi_ar_journal_interim
(
status,
actual_flag,
request_id,
created_by,
date_created,
set_of_books_id,
je_source_name,
je_category_name,
transaction_date,
accounting_date,
currency_code,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference10,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30
)
SELECT
'NEW' status,
'A' actual_flag,
p_Report.ReqId request_id,
fnd_global.user_id created_by,
trunc(sysdate) date_created,
p_Report.SetOfBooksId sob_id,
'Receivables' source,
decode(ctt.type,
'CM', 'Credit Memos',
'DM', 'Debit Memos',
'CB', 'Chargebacks',
'Sales Invoices') category,
ct.trx_date trx_date,
ctlgd.gl_date gl_date,
ct.invoice_currency_code currency,
ctlgd.code_combination_id ccid,
decode(ctlgd.account_class,
'REC', decode(sign(nvl(ctlgd.amount,0)),
-1,null,nvl(ctlgd.amount,0)),
decode(sign(nvl(ctlgd.amount,0)),
-1,-nvl(ctlgd.amount,0),null)) entered_dr,
decode(ctlgd.account_class,
'REC', decode(sign(nvl(ctlgd.amount,0)),
-1,-nvl(ctlgd.amount,0),null),
decode(sign(nvl(ctlgd.amount,0)),
-1,null,nvl(ctlgd.amount,0))) entered_cr,
decode(ctlgd.account_class,
'REC', decode(sign(nvl(ctlgd.acctd_amount,0)),
-1,null,nvl(ctlgd.acctd_amount,0)),
decode(sign(nvl(ctlgd.acctd_amount,0)),
-1,-nvl(ctlgd.acctd_amount,0),null)) acctd_dr,
decode(ctlgd.account_class,
'REC', decode(sign(nvl(ctlgd.acctd_amount,0)),
-1,-nvl(ctlgd.acctd_amount,0),null),
decode(sign(nvl(ctlgd.acctd_amount,0)),
-1,null,nvl(ctlgd.acctd_amount,0))) acctd_cr,
l_cat.meaning ref10,
to_char(p_Report.ReqId) ref21,
to_char(ct.customer_trx_id) ref22,
to_char(ctlgd.cust_trx_line_gl_dist_id) ref23,
to_char(null) ref24,
ct.trx_number ref25,
hz_cust_accounts.account_number ref26, -- Bug 3902175
to_char(ct.bill_to_customer_id) ref27,
decode(ctt.type,
'CM', 'CM',
'DM', 'DM',
'CB', 'CB',
'INV') ref28,
decode(ctt.type,
'CM', 'CM_',
'DM', 'DM_',
'CB', 'CB_',
'INV_')||ctlgd.account_class ref29,
'RA_CUST_TRX_LINE_GL_DIST' ref30
FROM
ar_lookups l_cat,
ra_cust_trx_types ctt,
hz_parties, -- Bug 3902175
hz_cust_accounts, -- Bug 3902175
ra_customer_trx_all ct,
ra_cust_trx_line_gl_dist ctlgd,
xla_ae_headers xah
WHERE
ctlgd.cust_trx_line_gl_dist_id+0 < p_Report.NxtCustTrxLineGlDistId
and ctlgd.set_of_books_id = p_Report.SetOfBooksId
and ctlgd.account_set_flag = 'N'
and ctlgd.customer_trx_id = ct.customer_trx_id
and ct.complete_flag = 'Y'
and ct.cust_trx_type_id = ctt.cust_trx_type_id
and hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
and hz_cust_accounts.cust_account_id = ct.bill_to_customer_id -- Bug 3902175
and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
and l_cat.lookup_code = decode(ctt.type,
'CM', 'CM_',
'DM', 'DM_',
'CB', 'CB_',
'INV_')||nvl(ctlgd.account_class,'REV')
and ct.invoice_currency_code = decode( p_Report.FuncCurr,
null,ct.invoice_currency_code,
p_Report.FuncCurr)
and ( ('Y' = 'Y' and ctt.type in ( 'INV','GUAR','DEP' ))
OR
('Y' = 'Y' and ctt.type = 'DM' )
OR
('Y' = 'Y' and ctt.type = 'CB' )
OR
('Y' = 'Y' and ctt.type = 'CM' )
)
and ctlgd.gl_date between p_Report.GlDateFrom
and p_Report.GlDateTo
and ctlgd.posting_control_id > 0
and xah.event_id = ctlgd.event_id
and xah.application_id = l_xah_ar_application_id
and xah.ledger_id = ctlgd.set_of_books_id
and xah.ledger_id = p_Report.SetOfBooksId
and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah.gl_transfer_status_code = 'Y'
and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
/* Added for bug 6647672 start */
and NOT EXISTS ( select 'Y'
from xla_ae_headers xah2
where xah2.event_id = ctlgd.event_id
and xah2.application_id = l_xah_ar_application_id
and xah2.ledger_id = ctlgd.set_of_books_id
and xah2.ledger_id = p_Report.CashSetOfBooksId
and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah2.gl_transfer_status_code = 'Y'
and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo ) ;
/* and exists ( select 'x'
from igi_ar_cash_basis_dists_all cbd
where cbd.source = 'GL'
and cbd.source_id = ctlgd.cust_trx_line_gl_dist_id
and ( ( exists( select 'x'
from igi_ar_rec_applications_all
where receivable_application_id = cbd.receivable_application_id
and arc_posting_control_id = -3
)
) or
( cbd.receivable_application_id_cash is not null and
( exists
( select receivable_application_id
from igi_ar_rec_applications_all
where receivable_application_id =cbd.receivable_application_id
and arc_posting_control_id = -3
)
)
)
)
)
and nvl(ctlgd.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
between
decode( p_Report.PostedDateFrom ,
null, nvl(ctlgd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom )
and
decode( p_Report.PostedDateTo,
null, nvl(ctlgd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo );
INSERT INTO
igi_ar_journal_interim
(
status,
actual_flag,
request_id,
created_by,
date_created,
set_of_books_id,
je_source_name,
je_category_name,
transaction_date,
accounting_date,
currency_code,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference10,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30
)
SELECT
'NEW' status,
'A' actual_flag,
p_Report.ReqId request_id,
fnd_global.user_id created_by,
trunc(sysdate) date_created,
p_Report.SetOfBooksId sob_id,
'Receivables' source_name,
decode(cr.type,
'MISC', 'Misc Receipts',
'Trade Receipts') category,
crh.trx_date trx_date,
crh.gl_date gl_date,
cr.currency_code currency,
ard.code_combination_id ccid,
to_number(ard.amount_dr) entered_dr,
to_number(ard.amount_cr) entered_cr,
to_number(ard.acctd_amount_dr) acctd_dr,
to_number(ard.acctd_amount_cr) acctd_cr,
l_cat.meaning ref10,
to_char(p_Report.ReqId) ref21,
decode(cr.type,
'CASH',to_char(cr.cash_receipt_id)||'C'||
to_char(crh.cash_receipt_history_id),
'MISC',to_char(cr.cash_receipt_id)) ref22,
to_char(ard.line_id) ref23,
cr.receipt_number ref24,
decode(cr.type,
'CASH',to_char(null),
'MISC',to_char(crh.cash_receipt_history_id)) ref25,
cust.customer_number ref26, -- Bug 3902175
to_char(cr.pay_from_customer) ref27,
decode( cr.type,
'MISC', 'MISC',
'TRADE') ref28,
decode( cr.type,
'MISC', 'MISC_',
'TRADE_')||ard.source_type ref29,
'AR_CASH_RECEIPT_HISTORY' ref30
FROM
ar_lookups l_cat,
(Select hz_cust_accounts.account_number customer_number,hz_cust_accounts.cust_account_id customer_id
from hz_parties,hz_cust_accounts where hz_parties.party_id = hz_cust_accounts.party_id) cust, -- bug 3902175
ar_distributions ard,
ar_cash_receipts cr,
ar_cash_receipt_history_all crh,
xla_ae_headers xah
WHERE crh.cash_receipt_history_id+0 < p_Report.NxtCashReceiptHistoryId
and crh.cash_receipt_history_id = ard.source_id
and ard.source_table = 'CRH'
and cr.set_of_books_id = p_Report.SetOfBooksId
and crh.postable_flag = 'Y'
and crh.cash_receipt_id = cr.cash_receipt_id
and cust.customer_id(+) = cr.pay_from_customer -- Bug 3902175
and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
and l_cat.lookup_code = decode( cr.type,
'MISC', 'MISC_',
'TRADE_')||ard.source_type
and cr.currency_code = decode( p_Report.FuncCurr,
null,cr.currency_code,
p_Report.FuncCurr )
and ( (p_Report.trade = 'Y' and cr.type <> 'MISC')
OR
(p_Report.misc = 'Y' and cr.type = 'MISC' ))
and crh.gl_date between p_Report.GlDateFrom
and p_Report.GldateTo
and crh.posting_control_id > 0
and xah.event_id = crh.event_id
and xah.application_id = l_xah_ar_application_id
and xah.ledger_id = cr.set_of_books_id
and xah.ledger_id = p_Report.SetOfBooksId
and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah.gl_transfer_status_code = 'Y'
and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
/* Added for bug 6647672 start */
and NOT EXISTS ( select 'Y'
from xla_ae_headers xah2
where xah2.event_id = crh.event_id
and xah2.application_id = l_xah_ar_application_id
and xah2.ledger_id = cr.set_of_books_id
and xah2.ledger_id = p_Report.CashSetOfBooksId
and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah2.gl_transfer_status_code = 'Y'
and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo ) ;
and exists ( select 'x'
from igi_ar_cash_receipt_hist_all
where cash_receipt_history_id = crh.cash_receipt_history_id
and arc_posting_control_id = -3
)
and nvl(crh.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
between
decode( p_Report.PostedDateFrom ,
null, nvl(crh.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom )
and
decode( p_Report.PostedDateTo,
null, nvl(crh.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo );
INSERT INTO
igi_ar_journal_interim
(
status,
actual_flag,
request_id,
created_by,
date_created,
set_of_books_id,
je_source_name,
je_category_name,
transaction_date,
accounting_date,
currency_code,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference10,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30
)
SELECT
'NEW' status,
'A' actual_flag,
p_Report.ReqId request_id,
fnd_global.user_id created_by,
trunc(sysdate) date_created,
p_Report.SetOfBooksId sob_id,
'Receivables' source,
decode(ra.amount_applied_from,
null,'Trade Receipts','Cross Currency') category,
ra.apply_date trx_date,
ra.gl_date gl_date,
cr.currency_code currency,
ard.code_combination_id ccid,
ard.amount_dr entered_dr,
ard.amount_cr entered_cr,
ard.acctd_amount_dr acctd_dr,
ard.acctd_amount_cr acctd_cr,
decode(ard.source_type,
'EXCH_GAIN',to_char(ard.code_combination_id),
'EXCH_LOSS',to_char(ard.code_combination_id),
null) ref1,
l_cat.meaning ref10,
to_char(p_Report.ReqId) ref21,
decode(ra.application_type,
'CASH',to_char(cr.cash_receipt_id)||'C'||
to_char(ra.receivable_application_id),
'CM', to_char(ra.receivable_application_id)) ref22,
to_char(ard.line_id) ref23,
cr.receipt_number ref24,
ctinv.trx_number ref25,
cust.customer_number ref26, -- Bug 3902175
to_char(cr.pay_from_customer) ref27,
decode(ra.amount_applied_from,
null,'TRADE','CCURR') ref28,
decode(ra.amount_applied_from,
null, 'TRADE_',
'CCURR_') || ard.source_type ref29,
'AR_RECEIVABLE_APPLICATIONS' ref30
FROM
ar_receivable_applications ra,
ar_cash_receipts cr,
ar_distributions ard,
ra_customer_trx ctinv,
ar_lookups l_cat,
ar_posting_control pc,
ar_system_parameters sp,
gl_sets_of_books gl,
(Select hz_cust_accounts.account_number customer_number,hz_cust_accounts.cust_account_id customer_id
from hz_parties,hz_cust_accounts where hz_parties.party_id = hz_cust_accounts.party_id) cust, -- bug 3902175
xla_ae_headers xah
WHERE
ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId
and ard.source_table = 'RA'
and ard.source_id = ra.receivable_application_id
and nvl(ra.postable,'Y') = 'Y'
and nvl(ra.confirmed_flag,'Y') = 'Y'
and ra.cash_receipt_id = cr.cash_receipt_id(+)
and ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
and cust.customer_id(+) = cr.pay_from_customer -- Bug 3902175
and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
and l_cat.lookup_code = decode(ra.amount_applied_from,
null, 'TRADE_',
'CCURR_') || ard.source_type
and sp.set_of_books_id = p_Report.SetOfBooksId
and sp.set_of_books_id = gl.set_of_books_id
and ra.set_of_books_id = sp.set_of_books_id
and pc.posting_control_id(+) = ra.posting_control_id
and cr.currency_code = decode( p_Report.FuncCurr,
null,cr.currency_code,
p_Report.FuncCurr)
and (p_Report.Trade = 'Y' OR p_Report.ccurr = 'Y')
and ra.gl_date between p_Report.GlDateFrom
and p_Report.GlDateFrom
and ra.posting_control_id > 0
and xah.event_id = ra.event_id
and xah.application_id = l_xah_ar_application_id
and xah.ledger_id = ra.set_of_books_id
and xah.ledger_id = p_Report.SetOfBooksId
and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah.gl_transfer_status_code = 'Y'
and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
/* Added for bug 6647672 start */
and NOT EXISTS ( select 'Y'
from xla_ae_headers xah2
where xah2.event_id = ra.event_id
and xah2.application_id = l_xah_ar_application_id
and xah2.ledger_id = ra.set_of_books_id
and xah2.ledger_id = p_Report.CashSetOfBooksId
and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah2.gl_transfer_status_code = 'Y'
and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo )
/* end bug 6647672 */
/* and exists ( select 'x'
from igi_ar_rec_applications_all
where receivable_application_id = ra.receivable_application_id
and arc_posting_control_id = -3
)
and nvl(ra.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
between
decode( p_Report.PostedDateTo ,
null, nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
fnd_date.canonical_to_date(p_Report.PostedDateTo))
and
decode( p_Report.PostedDateTo,
null, nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
fnd_date.canonical_to_date(p_Report.PostedDateTo))
*/
UNION ALL
SELECT
'NEW' status,
'A' actual_flag,
p_Report.ReqId request_id,
fnd_global.user_id created_by,
trunc(sysdate) date_created,
p_Report.SetOfBooksId sob_id,
'Receivables' source,
'Credit Memo Applications' category,
ra.apply_date trx_date,
ra.gl_date gl_date,
ctcm.invoice_currency_code currency,
ard.code_combination_id ccid,
ard.amount_dr entered_dr,
ard.amount_cr entered_cr,
ard.acctd_amount_dr acctd_dr,
ard.acctd_amount_cr acctd_cr,
decode(ard.source_type,
'EXCH_GAIN',to_char(ard.code_combination_id),
'EXCH_LOSS',to_char(ard.code_combination_id),
null) ref1,
l_cat.meaning ref10,
to_char(p_Report.ReqId) ref21,
to_char(ra.receivable_application_id) ref22,
to_char(ard.line_id) ref23,
ctcm.trx_number ref24,
ctinv.trx_number ref25,
hz_cust_accounts.account_number ref26, -- Bug 3902175
to_char(ctcm.bill_to_customer_id) ref27,
'CMAPP' ref28,
'CMAPP_' || ard.source_type ref29,
'AR_RECEIVABLE_APPLICATIONS' ref30
FROM
ar_receivable_applications ra,
ra_customer_trx ctcm,
ar_distributions ard,
ra_cust_trx_line_gl_dist ctlgdcm,
ra_customer_trx ctinv,
ar_lookups l_cat,
ar_posting_control pc,
ar_system_parameters sp,
gl_sets_of_books gl,
hz_parties,
hz_cust_accounts,
xla_ae_headers xah
WHERE
ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId
and ard.source_table = 'RA'
and ard.source_id = ra.receivable_application_id
and nvl(ra.postable,'Y') = 'Y'
and nvl(ra.confirmed_flag,'Y') = 'Y'
and ra.status||'' = 'APP'
and ra.customer_trx_id = ctcm.customer_trx_id
and ra.customer_trx_id = ctlgdcm.customer_trx_id
and ctlgdcm.account_class = 'REC'
and ctlgdcm.latest_rec_flag = 'Y'
and ra.applied_customer_trx_id = ctinv.customer_trx_id
and hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
and hz_cust_accounts.cust_account_id = ctcm.bill_to_customer_id -- Bug 3902175
and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
and l_cat.lookup_code = 'CMAPP_' || ard.source_type
and sp.set_of_books_id = p_Report.SetOfBooksId
and sp.set_of_books_id = gl.set_of_books_id
and ra.set_of_books_id = sp.set_of_books_id
and pc.posting_control_id(+) = ra.posting_control_id
and ctcm.invoice_currency_code = decode( p_Report.FuncCurr,
null,ctcm.invoice_currency_code,
p_Report.FuncCurr)
and p_Report.CMApp = 'Y'
and ra.gl_date between p_Report.GlDateFrom
and p_Report.GLDateTo
and ra.posting_control_id > 0
and xah.event_id = ra.event_id
and xah.application_id = l_xah_ar_application_id
and xah.ledger_id = ra.set_of_books_id
and xah.ledger_id = p_Report.SetOfBooksId
and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah.gl_transfer_status_code = 'Y'
and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
/* Added for bug 6647672 start */
and NOT EXISTS ( select 'Y'
from xla_ae_headers xah2
where xah2.event_id = ra.event_id
and xah2.application_id = l_xah_ar_application_id
and xah2.ledger_id = ra.set_of_books_id
and xah2.ledger_id = p_Report.CashSetOfBooksId
and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah2.gl_transfer_status_code = 'Y'
and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo );
/* and exists ( select 'x'
from igi_ar_rec_applications_all
where receivable_application_id = ra.receivable_application_id
and arc_posting_control_id = -3
)
and nvl(ra.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
between
decode( p_Report.PostedDateTo ,
null, nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
fnd_date.canonical_to_date(p_Report.PostedDateTo))
and
decode( p_Report.PostedDateTo,
null, nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
fnd_date.canonical_to_date(p_Report.PostedDateTo));
INSERT INTO
igi_ar_journal_interim
(
status,
actual_flag,
request_id,
created_by,
date_created,
set_of_books_id,
je_source_name,
je_category_name,
transaction_date,
accounting_date,
currency_code,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference10,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30
)
SELECT
'NEW' status,
'A' actual_flag,
p_Report.ReqId request_id,
fnd_global.user_id created_by,
trunc(sysdate) date_created,
p_Report.SetOfBooksId sob_id,
'Receivables' source_name,
'Misc Receipts' category,
mcd.apply_date trx_date,
mcd.gl_date gl_date,
cr.currency_code currency,
mcd.code_combination_id ccid,
ard.amount_dr entered_dr,
ard.amount_cr entered_cr,
ard.acctd_amount_dr acctd_dr,
ard.acctd_amount_cr acctd_cr,
l_cat.meaning ref10,
to_char(p_Report.ReqId) ref21,
to_char(cr.cash_receipt_id) ref22,
to_char(ard.line_id) ref23,
cr.receipt_number ref24,
to_char(mcd.misc_cash_distribution_id) ref25,
null ref26,
null ref27,
'MISC' ref28,
'MISC_' || ard.source_type ref29,
'AR_MISC_CASH_DISTRIBUTIONS' ref30
FROM
ar_misc_cash_distributions mcd,
ar_distributions ard,
ar_cash_receipts cr,
ar_lookups l_cat,
xla_ae_headers xah
WHERE mcd.misc_cash_distribution_id+0 < p_Report.NxtMiscCashDistributionId
and mcd.set_of_books_id = p_Report.SetOfBooksId
and mcd.cash_receipt_id = cr.cash_receipt_id
and ard.source_table = 'MCD'
and ard.source_id = mcd.misc_cash_distribution_id
and l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
and l_cat.lookup_code = 'MISC_' || ard.source_type
and cr.currency_code = decode( p_Report.FuncCurr,
null,cr.currency_code,
p_Report.FuncCurr)
and p_Report.Misc = 'Y'
and mcd.gl_date between
p_Report.GlDateFrom
and
p_Report.GlDateTo
and ( mcd.posting_control_id > 0 )
and xah.event_id = mcd.event_id
and xah.application_id = l_xah_ar_application_id
and xah.ledger_id = mcd.set_of_books_id
and xah.ledger_id = p_Report.SetOfBooksId
and xah.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah.gl_transfer_status_code = 'Y'
and xah.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo
/* Added for bug 6647672 start */
and NOT EXISTS ( select 'Y'
from xla_ae_headers xah2
where xah2.event_id = mcd.event_id
and xah2.application_id = l_xah_ar_application_id
and xah2.ledger_id = mcd.set_of_books_id
and xah2.ledger_id = p_Report.CashSetOfBooksId
and xah2.accounting_date between p_Report.GlDateFrom and p_Report.GlDateTo
and xah2.gl_transfer_status_code = 'Y'
and xah2.gl_transfer_date between p_Report.PostedDateFrom and p_Report.PostedDateTo );
/* and exists ( select 'x'
from igi_ar_misc_cash_dists_all
where misc_cash_distribution_id = mcd.misc_cash_distribution_id
and arc_posting_control_id = -3
)
and nvl(mcd.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
between
decode( p_Report.PostedDateTo ,
null, nvl(mcd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
fnd_date.canonical_to_date(p_Report.PostedDateTo))
and
decode( p_Report.PostedDateTo,
null, nvl(mcd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
fnd_date.canonical_to_date(p_Report.PostedDateTo));
select
max(je_category_name) cat_name,
max(currency_code) curr_code,
max(accounting_date) acctg_date,
max(nvl(reference24,reference25)) trx_num,
reference28 cat_code,
decode(instr(reference22,'C'),0, reference22,
substr(reference22,1,instr(reference22,'C')-1))
balance_id,
nvl(sum(nvl(entered_dr,0)),0) entered_dr,
nvl(sum(nvl(entered_cr,0)),0) entered_cr,
nvl(sum(nvl(accounted_dr,0)),0) accounted_dr,
nvl(sum(nvl(accounted_cr,0)),0) accounted_cr
from igi_ar_journal_interim
where je_source_name = 'Receivables'
and set_of_books_id = p_Report.SetOfBooksId
and request_id = p_Report.ReqId
group by
reference28,
decode(instr(reference22,'C'),0, reference22,
substr(reference22,1,instr(reference22,'C')-1))
having
( nvl(sum(nvl(entered_dr,0)),0)<> nvl(sum(nvl(entered_cr,0)),0)
OR
nvl(sum(nvl(accounted_dr,0)),0)<> nvl(sum(nvl(accounted_cr,0)),0));
delete from igi_ar_journal_interim iaji
where iaji.je_source_name = 'Receivables'
and iaji.set_of_books_id = p_Report.SetOfBooksId
and iaji.request_id = p_Report.ReqID
and iaji.reference23 = RBal.balance_id
and iaji.reference28 = RBal.cat_code
and iaji.je_category_name = RBal.cat_name
;
select distinct substr(application_column_name, 1, 15)
into l_segment_name
from fnd_segment_attribute_values
where segment_attribute_type = 'GL_ACCOUNT'
and attribute_value = 'Y'
and id_flex_num = p_coa
;
select count(*) ct
from igi_ar_journal_interim
where request_id = p_request_id
;
select start_date
from gl_period_statuses
where set_of_books_id = p_sob_id
and period_name = p_period_name
and application_id = ( select application_id
from fnd_application
where application_short_name = 'AR'
);
select end_date
from gl_period_statuses
where set_of_books_id = p_sob_id
and period_name = p_period_name
and application_id = ( select application_id
from fnd_application
where application_short_name = 'AR'
);
select currency_code
into l_Report.Currency
from gl_sets_of_books
where set_of_books_id = p_SetOfBooksId
;
select currency_code
into l_Report.FuncCurr
from gl_sets_of_books
where set_of_books_id = p_CashSetOfBooksId
;
SELECT sp.arc_cash_sob_id
, sob.currency_code
, sp.arc_unalloc_rev_ccid
INTO l_Report.CashSetOfBooksId
, l_Report.FuncCurr
, l_Report.UnallocatedRevCcid
FROM igi_ar_system_options sp
, gl_sets_of_books sob
WHERE sp.set_of_books_id = p_SetOfBooksID
AND sob.set_of_books_id = sp.set_of_books_id;
SELECT ar_cash_receipt_history_s.nextval
, ar_receivable_applications_s.nextval
, ar_misc_cash_distributions_s.nextval
, ar_adjustments_s.nextval
, ra_cust_trx_line_gl_dist_s.nextval
INTO l_Report.NxtCashReceiptHistoryId
, l_Report.NxtReceivableApplicationId
, l_Report.NxtMiscCashDistributionId
, l_Report.NxtAdjustmentId
, l_Report.NxtCustTrxLineGlDistId
FROM dual;
delete from igi_ar_journal_interim
-- where request_id = l_Report.ReqId
;
'Create data into CBR interface table -> IGI_CBR_ARC_INTERFACE_PKG.Insert_rows');
IGI_CBR_ARC_INTERFACE_PKG.Insert_Rows (l_Report.reqid, l_Report.CashSetOfBooksId);
'Delete records from IGI_AR_JOURNAL_INTERIM (IF not debug)');
delete from igi_ar_journal_interim
where request_id = l_Report.ReqId
;
delete from igi_cbr_arc_interface
where request_id = l_Report.ReqId
;