[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM( cbd.amount ) Amount,
cbd.source Source,
cbd.source_id SourceId,
NVL(SUM( DECODE(cbd.source,
'UNA', cbd.amount, 0 )),0) UnallocatedAmt
FROM igi_ar_cash_basis_dists cbd
WHERE cbd.payment_schedule_id = p_ps_id
AND cbd.type = p_type
AND (cbd.posting_control_id+0 > 0
or
cbd.posting_control_id+0 = - ( p_req_id +100 ))
GROUP BY cbd.source,
cbd.source_id
ORDER BY DECODE( cbd.source, 'GL', 1,
'ADJ',2,
'UNA',3 ),
cbd.source_id;
SELECT ctlgd.cust_trx_line_gl_dist_id,
ctlgd.amount amount,
ctlgd.code_combination_id ccid,
substrb(decode(ctlgd.account_class,
'REV','LINE',
ctlgd.account_class),1,15) accntclass
FROM ra_cust_trx_line_gl_dist ctlgd
WHERE ctlgd.customer_trx_id = cp_ctid
AND ctlgd.account_class IN ( 'REV', 'TAX', 'FREIGHT','CHARGES' ) -- we are only interested in these classes
AND ctlgd.account_class = DECODE
(
cp_type,
'LINE', 'REV',
'TAX', 'TAX',
'FREIGHT', 'FREIGHT',
'CHARGES', 'CHARGES',
ctlgd.account_class
)
AND ctlgd.cust_trx_line_gl_dist_id+0 < p_Report.NxtCustTrxLineGlDistId
ORDER BY ctlgd.cust_trx_line_gl_dist_id;
SELECT a.adjustment_id adjustment_id,
DECODE
(
cp_type,
'LINE', a.line_adjusted,
'TAX', a.tax_adjusted,
'FREIGHT', a.freight_adjusted,
'CHARGES', a.receivables_charges_adjusted,
a.amount
) amount,
a.code_combination_id ccid,
substrb(a.type,1,15) accntclass
FROM ar_adjustments a,
ra_customer_trx ct,
ra_cust_trx_types ctt
WHERE a.payment_schedule_id = cp_ps_id
AND a.receivables_trx_id <> -1
AND a.status = 'A'
AND a.customer_trx_id = ct.customer_trx_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND (
( ctt.creation_sign = 'N'
AND
DECODE
(
cp_type,
'LINE', a.line_adjusted,
'TAX', a.tax_adjusted,
'FREIGHT', a.freight_adjusted,
'CHARGES', a.receivables_charges_adjusted,
a.amount
) < 0
)
OR
( ctt.creation_sign <> 'N'
AND
DECODE
(
cp_type,
'LINE', a.line_adjusted,
'TAX', a.tax_adjusted,
'FREIGHT', a.freight_adjusted,
'CHARGES', a.receivables_charges_adjusted,
a.amount
) > 0
)
)
AND a.adjustment_id+0 < p_Report.NxtAdjustmentId
ORDER BY a.adjustment_id;
SELECT ps.customer_trx_id,
NVL(tl.relative_amount, 100 )/NVL( t.base_amount, 100 ),
t.first_installment_code,
ps.invoice_currency_code,
NVL
(
DECODE
(
p_Type,
'LINE', ps.amount_line_items_original,
'TAX', ps.tax_original,
'FREIGHT', ps.freight_original,
'CHARGES', ps.receivables_charges_charged,
'INVOICE', ps.amount_due_original,
0
),
0
),
DECODE
(
MIN(tl_first.sequence_num),
tl.sequence_num, 'Y',
'N'
) first_installment_flag
INTO l_customer_trx_id,
l_term_fraction,
l_FirstInstallmentCode,
l_currency_code,
l_AmountReconcile,
l_FirstInstallmentFlag
FROM ar_payment_schedules ps,
ra_terms t,
ra_terms_lines tl,
ra_terms_lines tl_first
WHERE ps.payment_schedule_id = p_ps_id
AND tl.term_id(+) = ps.term_id
AND tl.sequence_num(+) = ps.terms_sequence_number
AND t.term_id(+) = tl.term_id
AND tl_first.term_id(+) = t.term_id
GROUP BY ps.customer_trx_id,
tl.relative_amount,
t.base_amount,
t.first_installment_code,
ps.invoice_currency_code,
ps.amount_line_items_original,
ps.tax_original,
ps.freight_original,
ps.receivables_charges_charged,
ps.amount_due_original,
tl.sequence_num;
WriteToLogFile( l_excep_level, 'CurrentRevDistribution','Exception:CurrentRevDistribution.Select PS Details:' );
SELECT nvl(sum(nvl(receivables_charges_adjusted,0)),0)
INTO charges_adjusted
FROM ar_adjustments
WHERE payment_schedule_id = p_ps_id
AND status = 'A'
AND type in ('INVOICE','CHARGES');
| Creates a distribution by inserting a record into |
| igi_ar_cash_basis_dists, and a record into igi_ar_journal_interim |
| PARAMETERS |
| |
| EXCEPTIONS RAISED |
| |
| ERRORS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| 23-Jul-1993 Alan Fothergill Created |
*---------------------------------------------------------------------------*/
PROCEDURE CreateDistribution( p_Report IN ReportParametersType,
p_Receipt IN ReceiptType,
p_Trx IN TrxType,
p_App IN ApplicationType,
p_Amount IN NUMBER,
p_AcctdAmount IN NUMBER,
p_Source IN VARCHAR2,
p_SourceId IN NUMBER,
p_Type IN VARCHAR2,
p_Ccid IN NUMBER,
p_AccntClass IN VARCHAR2 ) IS
CashBasisDistributionId igi_ar_cash_basis_dists.cash_basis_distribution_id%TYPE;
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
p_Report.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
p_Report.CashSetOfBooksId, -- set_of_books_id
'Receivables', -- user_je_source_name
'Trade Receipts', -- user_je_category_name
p_App.TrxDate, -- trx_date
p_App.GlDate, -- accounting_date
p_Receipt.CurrencyCode, -- currency_code
cbd.code_combination_id, -- code_combination_id
DECODE
(
SIGN( cbd.amount ),
-1, -cbd.amount,
NULL
), -- entered_dr
DECODE
(
SIGN( cbd.amount ),
-1, NULL,
cbd.amount
), -- entered_cr
DECODE
(
SIGN( cbd.acctd_amount ),
-1, -cbd.acctd_amount,
NULL
), -- accounted_dr
DECODE
(
SIGN( cbd.acctd_amount ),
-1, NULL,
cbd.acctd_amount
), -- accounted_cr
p_App.CatMeaning, -- reference10,
p_Report.ReqId, -- reference21,
p_Receipt.CashReceiptId, -- reference22,
cbd.cash_basis_distribution_id, -- reference23,
p_Receipt.ReceiptNumber, -- reference24,
p_Trx.TrxNumber, -- reference25,
p_Receipt.CustomerNumber, -- reference26,
p_Receipt.PayFromCustomer, -- reference27,
DECODE(
P_App.AppType,
'CM', 'CMAPP',
'CASH','TRADE' ), -- reference28,
DECODE(
P_App.AppType,
'CASH', 'TRADE_APP',
'CM', DECODE(
p_Trx.CmPsIdFlag,
'Y', 'CMAPP_REC',
'CMAPP_APP' )), -- reference29,
'AR_CASH_BASIS_DISTRIBUTIONS' -- reference30
FROM igi_ar_cash_basis_dists cbd
WHERE cbd.posting_control_id+0 = p_App.PostingControlId
AND cbd.receivable_application_id = p_App.ReceivableApplicationId
AND cbd.payment_schedule_id = p_Trx.PaymentScheduleId
AND cbd.type = p_Type;
WriteToLogFile(l_excep_level, 'CreateDistribution', 'Exception:CreateDistribution.InsertPostedAR:' );
SELECT igi_ar_cash_basis_dists_s.NEXTVAL
INTO CashBasisDistributionId
FROM dual;
INSERT INTO igi_ar_cash_basis_dists
(
cash_basis_distribution_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
receivable_application_id,
source,
source_id,
type,
payment_schedule_id,
gl_date,
currency_code,
amount,
acctd_amount,
code_combination_id,
posting_control_id,
gl_posted_date
)
VALUES
(
CashBasisDistributionId,
p_Report.CreatedBy,
TRUNC( SYSDATE ),
p_Report.CreatedBy,
TRUNC( SYSDATE ),
p_App.ReceivableApplicationId,
p_Source,
p_SourceId,
p_Type,
p_Trx.PaymentScheduleId,
p_App.GlDate,
p_Receipt.CurrencyCode,
p_Amount,
p_AcctdAmount,
p_Ccid,
- ( p_Report.ReqId +1000 ),
TRUNC( SYSDATE )
);
WriteToLogFile( l_excep_level, 'CreateDistribution','Exception:CreateDistribution.InsertCBD:' );
WritetoLogFile ( l_state_level, 'CreateDistribution','CreateDistribution : Insert into AR_JOURNAL_INTERIM...');
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
)
VALUES
(
'NEW', -- status
'A', -- actual flag
p_Report.ReqId, -- request_id
p_Report.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
p_Report.CashSetOfBooksId, -- set_of_books_id
'Receivables', -- user_je_source_name
'Trade Receipts', -- user_je_category_name
p_App.TrxDate, -- trx_date
p_App.GlDate, -- accounting_date
p_Receipt.CurrencyCode, -- currency_code
p_Ccid, -- code_combination_id
DECODE
(
SIGN( p_Amount ),
-1, -p_Amount,
NULL
), -- entered_dr
DECODE
(
SIGN( p_Amount ),
-1, NULL,
p_Amount
), -- entered_cr
DECODE
(
SIGN( p_AcctdAmount ),
-1, -p_AcctdAmount,
NULL
), -- accounted_dr
DECODE
(
SIGN( p_AcctdAmount ),
-1, NULL,
p_AcctdAmount
), -- accounted_cr
p_App.CatMeaning, -- reference10,
p_Report.ReqId, -- reference21,
p_Receipt.CashReceiptId, -- reference22,
CashBasisDistributionId, -- reference23,
p_Receipt.ReceiptNumber, -- reference24,
p_Trx.TrxNumber, -- reference25,
p_Receipt.CustomerNumber, -- reference26,
p_Receipt.PayFromCustomer, -- reference27,
DECODE(
P_App.AppType,
'CM', 'CMAPP',
'CASH','TRADE' ), -- reference28,
DECODE(
P_App.AppType,
'CASH', 'TRADE_APP',
'CM', DECODE(
p_Trx.CmPsIdFlag,
'Y', 'CMAPP_REC',
'CMAPP_APP' )), -- reference29,
'AR_CASH_BASIS_DISTRIBUTIONS' -- reference30
);
WriteToLogFile(l_excep_level, 'CreateDistribution', 'Exception:CreateDistribution.InsertAR:' );
SELECT DISTINCT ra.ROWID RaRowid,
cr.cash_receipt_id CashReceiptId,
cr.receipt_number ReceiptNumber,
cr.pay_from_customer PayFromCustomer,
decode(ra.status,'UNID',null,
hz_cust_accounts.account_number ) CustomerNumber, -- Bug 3902175
cr.currency_code CurrencyCode,
ra.receivable_application_id ReceivableApplicationId,
ra.gl_date GlDate,
ra.apply_date TrxDate,
ra.amount_applied Amount,
ra.amount_applied_from AmountAppFrom,
ra.acctd_amount_applied_from AcctdAmount,
ra.code_combination_id CodeCombinationId,
ra.status Status,
l_cat.meaning CatMeaning
FROM ar_receivable_applications ra,
igi_ar_rec_applications igira,
ar_cash_receipts cr,
hz_parties, -- Bug 3902175
hz_cust_accounts, -- Bug 3902175
ar_lookups l_cat,
xla_ae_headers xah
WHERE ra.gl_date BETWEEN p_Report.GlDateFrom
AND p_Report.GLDateTo
AND igira.receivable_application_id = ra.receivable_application_id
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status <> 'APP'
AND cr.cash_receipt_id = ra.cash_receipt_id
AND (
(cr.pay_from_customer = hz_cust_accounts.cust_account_id -- bug 3902175
AND ra.status <> 'UNID')
OR
ra.status = 'UNID'
)
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 = decode(ra.amount_applied_from,
null,'TRADE_APP','CCURR_APP')
AND cr.currency_code = DECODE( p_Report.Currency,
null,cr.currency_code,
p_Report.Currency)
AND ra.application_type||'' = 'CASH'
AND (p_Report.Trade = 'Y'
OR p_Report.Ccurr = 'Y')
/* AND ( igira.arc_posting_control_id = DECODE( p_Report.PostedStatus,
'BOTH', igira.arc_posting_control_id,
'UNPOSTED', -3,
-8888 )
OR
igira.arc_posting_control_id <> decode( p_Report.PostedStatus,
'BOTH', -8888,
'POSTED', -3,
igira.arc_posting_control_id) )
*/
AND (
(ra.posting_control_id > 0 AND p_Report.CallingMode = 'CBR') OR
(p_Report.CallingMode = 'ARC')
)
/* AND NVL(igira.arc_gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
BETWEEN
DECODE( p_Report.PostedStatus,
'BOTH', nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateFrom ,
null, nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom))
AND
DECODE( p_Report.PostedStatus,
'BOTH', nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateTo,
null, nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo))
*/
AND ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId
AND xah.event_id = ra.event_id
AND xah.application_id = l_xah_ar_application_id
AND xah.ledger_id = p_Report.CashSetOfBooksId
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;
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
)
VALUES
(
'NEW', -- status
'A', -- actual flag
p_Report.ReqId, -- request_id
p_Report.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
p_Report.CashSetOfBooksId, -- set_of_books_id
'Receivables', -- user_je_source_name
decode(RRa.AmountAppFrom,
null,'Trade Receipts','Cross Currency'), -- user_je_category_name
RRa.TrxDate, -- trx_date
RRa.GlDate, -- accounting_date
RRa.CurrencyCode, -- currency_code
RRa.CodeCombinationId, -- code_combination_id
DECODE
(
SIGN( RRa.amount ),
-1, -nvl(RRa.AmountAppFrom,RRa.amount),
NULL
), -- entered_dr
DECODE
(
SIGN( RRa.amount ),
-1, NULL,
nvl(RRa.AmountAppFrom,RRa.amount)
), -- entered_cr
DECODE
(
SIGN( RRa.AcctdAmount ),
-1, -RRa.AcctdAmount,
NULL
), -- accounted_dr
DECODE
(
SIGN( RRa.AcctdAmount ),
-1, NULL,
RRa.AcctdAmount
), -- accounted_cr
RRa.CatMeaning, -- reference10
p_Report.ReqId, -- reference21
RRa.CashReceiptId, -- reference22
RRa.ReceivableApplicationId, -- reference23
RRa.ReceiptNumber, -- reference24
NULL, -- reference25
RRa.CustomerNumber, -- reference26
RRa.PayFromCustomer, -- reference27
decode(RRa.AmountAppFrom,
null,'TRADE','CROSS CURR'), -- reference28
decode(RRa.AmountAppFrom,
null,'TRADE_APP','CCURR_APP'), -- reference29
'AR_RECEIVABLE_APPLICATIONS' -- reference30
);
WriteToLogFile( l_excep_level,'ReportNonDistApplications','Exception:ReportNonDistApplications.INSERT:' );
WriteToLogFile( l_state_level,'ReportNonDistApplications',' '||l_Count||' lines selected' );
| We need to have ORDER BY clause in the select statement because |
| when comparing with GL Transfer entries, they need to match, |
| If order by is not used, there will be rounding difference. |
| |
| PARAMETERS |
| |
| EXCEPTIONS RAISED |
| |
| ERRORS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| 23-Jul-1993 Alan Fothergill Created |
*---------------------------------------------------------------------------*/
PROCEDURE ReportDistributedApplications( p_Report IN ReportParametersType ) IS
CURSOR CRa IS
SELECT ra.ROWID ra_rowid,
DECODE(
ra.application_type,
'CM', ctcm.customer_trx_id,
'CASH',cr.cash_receipt_id ) CashReceiptId,
DECODE(
ra.application_type,
'CM', ctcm.trx_number,
'CASH',cr.receipt_number ) ReceiptNumber,
DECODE(
ra.application_type,
'CM', ctcm.bill_to_customer_id,
'CASH',cr.pay_from_customer ) PayFromCustomer,
hz_cust_accounts.account_number CustomerNumber, -- Bug 3902175
ct.invoice_currency_code CurrencyCode,
DECODE(
ra.application_type,
'CM', NVL(ctcm.exchange_rate,1),
'CASH',NVL(crh.exchange_rate,1) ) ExchangeRate,
DECODE(
l.lookup_code,
'1', 'N',
'2', 'Y'
) CmPsIdFlag,
DECODE(
l.lookup_code,
'1', ra.applied_payment_schedule_id,
'2', ra.payment_schedule_id
) PaymentScheduleId,
ctt.type Class,
ct.trx_number TrxNumber,
ra.receivable_application_id ReceivableApplicationId,
ra.apply_date TrxDate,
ra.gl_date GlDate,
ra.application_type AppType,
DECODE(
l.lookup_code,
'1', ra.amount_applied,
'2', -ra.amount_applied
) Amount,
DECODE(
l.lookup_code,
'1', ra.acctd_amount_applied_from,
'2', -ra.acctd_amount_applied_from
) AcctdAmount,
DECODE(
l.lookup_code,
'1', NVL(ra.line_applied,0),
'2', NVL(-ra.line_applied,0)
) LineApplied,
DECODE(
l.lookup_code,
'1', NVL(ra.tax_applied,0),
'2', NVL(-ra.tax_applied,0)
) TaxApplied,
DECODE(
l.lookup_code,
'1', NVL(ra.freight_applied,0),
'2', NVL(-ra.freight_applied,0)
) FreightApplied,
DECODE(
l.lookup_code,
'1', NVL(ra.receivables_charges_applied,0),
'2', NVL(-ra.receivables_charges_applied,0)
) ChargesApplied,
l_cat.meaning CatMeaning,
igira.arc_posting_control_id PostingControlId
FROM ar_receivable_applications ra,
igi_ar_rec_applications igira,
ra_cust_trx_types ctt,
ra_customer_trx ct,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ra_customer_trx ctcm,
ar_lookups l,
ar_lookups l_cat,
hz_parties, -- Bug 3902175
hz_cust_accounts, -- Bug 3902175
xla_ae_headers xah
WHERE ra.gl_date BETWEEN p_Report.GlDateFrom
AND p_Report.GLDateTo
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status||'' = 'APP'
AND ra.receivable_application_id = igira.receivable_application_id
AND ra.cash_receipt_id = cr.cash_receipt_id(+)
AND ra.cash_receipt_history_id = crh.cash_receipt_history_id(+)
AND ra.customer_trx_id = ctcm.customer_trx_id(+)
AND ctcm.previous_customer_trx_id IS NULL
AND ra.applied_customer_trx_id = ct.customer_trx_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND hz_parties.party_id = hz_cust_accounts.party_id -- Bug 3902175
AND l.lookup_type = 'AR_CARTESIAN_JOIN'
AND (
( l.lookup_code ='1' )
OR
( l.lookup_code = '2'
AND
ra.application_type = 'CM' )
)
AND hz_cust_accounts.cust_account_id = DECODE( ra.application_type, -- Bug 3902175
'CM', ctcm.bill_to_customer_id,
cr.pay_from_customer )
AND l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
AND l_cat.lookup_code = decode( ra.application_type,
'CM', decode( l.lookup_code,
'1', 'CMAPP_APP',
'2', 'CMAPP_REC'),
'TRADE_APP')
AND ct.invoice_currency_code = DECODE( p_Report.Currency,
null,ct.invoice_currency_code,
p_Report.Currency)
AND ( ( p_Report.Trade = 'Y' AND ra.application_type||'' = 'CASH' )
OR
( p_Report.CMApp = 'Y' AND ra.application_type||'' = 'CM' ) )
/* AND ( igira.arc_posting_control_id = DECODE( p_Report.PostedStatus,
'BOTH', igira.arc_posting_control_id,
'UNPOSTED', -3,
-8888 )
OR
igira.arc_posting_control_id <> decode( p_Report.PostedStatus,
'BOTH', -8888,
'POSTED', -3,
igira.arc_posting_control_id) )
*/
AND (
(ra.posting_control_id > 0 AND p_Report.CallingMode = 'CBR') OR
(p_Report.CallingMode = 'ARC')
)
/* AND NVL(igira.arc_gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
BETWEEN
DECODE( p_Report.PostedStatus,
'BOTH', nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateFrom ,
null, nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom))
AND
DECODE( p_Report.PostedStatus,
'BOTH', nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateTo,
null, nvl(igira.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo))
*/
AND ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId
AND xah.event_id = crh.event_id
AND xah.application_id = l_xah_ar_application_id
AND xah.ledger_id = p_Report.CashSetOfBooksId
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
ORDER BY ra.receivable_application_id, l.lookup_code;
select 'x'
from ar_payment_schedules
where payment_schedule_id = fp_ps_id
;
WriteToLogFile( l_state_level,'ReportDistributedApplications',' '||l_Count||' lines selected' );
| This is implemented as two cursors one to select cash receipt history|
| the other to select reversals. It had to be implemented this way |
| because FOR UPDATE OF is not allowed in a UNION |
| The two selects must be maintained in parallel, as the InsertIntoAR |
| relies on the ROWTYPE of each select cursor being the same |
| |
| HISTORY |
| 12-Apr-1994 D Chu Created |
| 21-Mar-1995 C Aldamiz Modified for 10.6 |
*---------------------------------------------------------------------------*/
PROCEDURE ReportCashReceiptHistory( p_Report IN ReportParametersType ) IS
CURSOR CCrh IS
SELECT crh.ROWID CrhRowid,
crh.cash_receipt_history_id CashReceiptHistoryId,
crh.cash_receipt_id CashReceiptId,
cr.receipt_number ReceiptNumber,
cr.pay_from_customer PayFromCustomer,
cust.customer_number CustomerNumber, -- Bug 3902175
DECODE
(
cr.type,
'MISC', 'MISC',
'TRADE'
) ModifiedType,
nvl(d.amount_dr, -d.amount_cr) Amount,
nvl(d.acctd_amount_dr, -d.acctd_amount_cr) AcctdAmount,
d.code_combination_id AccountCodeCombinationId,
crh.gl_date GlDate,
crh.trx_date TrxDate,
cr.currency_code CurrencyCode,
DECODE
(
cr.type,
'MISC', 'Misc Receipts',
'Trade Receipts'
) Category,
l_cat.meaning CatMeaning,
d.source_type SourceType
FROM ar_cash_receipt_history crh,
igi_ar_cash_receipt_hist igicrh,
ar_cash_receipts cr,
(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_lookups l_cat,
ar_distributions d,
xla_ae_headers xah
WHERE crh.gl_date BETWEEN p_Report.GlDateFrom
AND p_Report.GLDateTo
AND crh.postable_flag = 'Y'
AND cr.cash_receipt_id = crh.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_')||'CASH'
AND cr.currency_code = DECODE( p_Report.Currency,
null,cr.currency_code,
p_Report.Currency)
AND ( ( p_Report.Trade = 'Y' AND cr.type = 'CASH' )
OR
( p_Report.Misc = 'Y' AND cr.type = 'MISC' ) )
/* AND ( igicrh.arc_posting_control_id = DECODE( p_Report.PostedStatus,
'BOTH', igicrh.arc_posting_control_id,
'UNPOSTED', -3,
-8888 )
OR
igicrh.arc_posting_control_id <> decode( p_Report.PostedStatus,
'BOTH', -8888,
'POSTED', -3,
igicrh.arc_posting_control_id))
*/
AND (
(crh.posting_control_id > 0 AND p_Report.CallingMode = 'CBR') OR
(p_Report.CallingMode = 'ARC')
)
/* AND NVL(igicrh.arc_gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
BETWEEN
DECODE( p_Report.PostedStatus,
'BOTH', nvl(igicrh.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(igicrh.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateFrom ,
null, nvl(igicrh.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom))
AND
DECODE( p_Report.PostedStatus,
'BOTH', nvl(igicrh.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(igicrh.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateTo,
null, nvl(igicrh.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo))
*/
AND crh.cash_receipt_history_id = d.source_id
AND d.source_table = 'CRH'
AND crh.cash_receipt_history_id = igicrh.cash_receipt_history_id
AND crh.cash_receipt_history_id+0 < p_Report.NxtCashReceiptHistoryId
AND xah.event_id = crh.event_id
AND xah.application_id = l_xah_ar_application_id
AND xah.ledger_id = p_Report.CashSetOfBooksId
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;
PROCEDURE InsertIntoAR( RCrh IN CCrh%ROWTYPE ) IS
BEGIN
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
)
VALUES
(
'NEW', -- status
'A', -- actual flag
p_Report.ReqId, -- request_id
p_Report.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
p_Report.CashSetOfBooksId, -- set_of_books_id
'Receivables', -- user_je_source_name
RCrh.Category, -- user_je_category_name
RCrh.TrxDate, -- trx_date
RCrh.GlDate, -- accounting_date
RCrh.CurrencyCode, -- currency_code
RCrh.AccountCodeCombinationId, -- code_combination_id
DECODE
(
SIGN( RCrh.Amount ),
-1, NULL,
RCrh.Amount
), -- entered_dr
DECODE
(
SIGN( RCrh.Amount ),
-1, -RCrh.Amount,
NULL
), -- entered_cr
DECODE
(
SIGN( RCrh.AcctdAmount ),
-1, NULL,
RCrh.AcctdAmount
), -- accounted_dr
DECODE
(
SIGN( RCrh.AcctdAmount ),
-1, -RCrh.AcctdAmount,
NULL
), -- accounted_cr
RCrh.CatMeaning, -- reference10
p_Report.ReqId, -- reference21
RCrh.CashReceiptId, -- reference22
RCrh.CashReceiptHistoryId, -- reference23
RCrh.ReceiptNumber, -- reference24
NULL, -- reference25
RCrh.CustomerNumber, -- reference26
RCrh.PayFromCustomer, -- reference27
RCrh.ModifiedType, -- reference28
RCrh.ModifiedType||'_'||RCrh.SourceType, -- reference29
'AR_CASH_RECEIPT_HISTORY' -- reference30
);
WriteToLogFile( l_excep_level,'ReportCashReceiptHistory','InsertIntoAR:' );
InsertIntoAR( RCrh );
WriteToLogFile(l_state_level,'ReportCashReceiptHistory', ' '||l_Count||' lines selected' );
SELECT mcd.ROWID McdRowid,
mcd.misc_cash_distribution_id MiscCashDistributionId,
cr.cash_receipt_id CashReceiptId,
cr.receipt_number ReceiptNumber,
mcd.amount amount,
mcd.acctd_amount acctd_amount,
mcd.code_combination_id code_combination_id,
mcd.gl_date gl_date,
mcd.apply_date trx_date,
cr.currency_code currency_code,
'Misc Receipts' category,
l_cat.meaning CatMeaning
FROM ar_misc_cash_distributions mcd,
igi_ar_misc_cash_dists igimcd,
ar_cash_receipts cr,
ar_lookups l_cat,
xla_ae_headers xah
WHERE mcd.gl_date BETWEEN p_Report.GlDateFrom AND p_Report.GLDateTo
AND cr.cash_receipt_id = mcd.cash_receipt_id
AND mcd.misc_cash_distribution_id = igimcd.misc_cash_distribution_id
AND l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
AND l_cat.lookup_code = 'MISC_MISC'
AND cr.currency_code = DECODE( p_Report.Currency,
null,cr.currency_code,
p_Report.Currency)
AND p_Report.Misc = 'Y'
/* AND ( igimcd.arc_posting_control_id = DECODE( p_Report.PostedStatus,
'BOTH', igimcd.arc_posting_control_id,
'UNPOSTED', -3,
-8888 )
OR
igimcd.arc_posting_control_id <> decode( p_Report.PostedStatus,
'BOTH', -8888,
'POSTED', -3,
igimcd.arc_posting_control_id) )
*/
AND (
(mcd.posting_control_id > 0 AND p_Report.CallingMode = 'CBR') OR
(p_Report.CallingMode = 'ARC')
)
/* AND NVL(igimcd.arc_gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
BETWEEN
DECODE( p_Report.PostedStatus,
'BOTH', nvl(igimcd.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(igimcd.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateFrom ,
null, nvl(igimcd.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom))
AND
DECODE( p_Report.PostedStatus,
'BOTH', nvl(igimcd.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(igimcd.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateTo,
null, nvl(igimcd.arc_gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo))
*/
AND mcd.misc_cash_distribution_id+0 < p_Report.NxtMiscCashDistributionId
AND xah.event_id = mcd.event_id
AND xah.application_id = l_xah_ar_application_id
AND xah.ledger_id = p_Report.CashSetOfBooksId
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;
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,
reference28,
reference29,
reference30
)
VALUES
(
'NEW', -- status
'A', -- actual flag
p_Report.ReqId, -- request_id
p_Report.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
p_Report.CashSetOfBooksId, -- set_of_books_id
'Receivables', -- user_je_source_name
RMcd.category, -- user_je_category_name
RMcd.trx_date, -- trx_date
RMcd.gl_date, -- accounting_date
RMcd.currency_code, -- currency_code
RMcd.code_combination_id, -- code_combination_id
DECODE
(
SIGN( RMcd.amount ),
-1, -RMcd.amount,
NULL
), -- entered_dr
DECODE
(
SIGN( RMcd.amount ),
-1, NULL,
RMcd.amount
), -- entered_cr
DECODE
(
SIGN( RMcd.acctd_amount ),
-1, -RMcd.acctd_amount,
NULL
), -- accounted_dr
DECODE
(
SIGN( RMcd.acctd_amount ),
-1, NULL,
RMcd.acctd_amount
), -- accounted_cr
RMcd.CatMeaning, -- reference10
p_Report.ReqId, -- reference21
RMcd.CashReceiptId, -- reference22
RMcd.MiscCashDistributionId, -- reference23
RMcd.ReceiptNumber, -- reference24
'MISC', -- reference28
'MISC_MISC', -- reference29
'AR_MISC_CASH_DISTRIBUTIONS' -- reference30
);
WriteToLogFile( l_state_level,'ReportMiscCashDistributions',' '||l_Count||' lines selected' );
DELETE FROM igi_ar_journal_interim
WHERE reference22 = p_BalanceId
AND reference28 = p_CategoryCode
AND set_of_books_id = p_Report.CashSetOfBooksId
AND request_id = p_Report.ReqId;
| Checks that the records inserted into igi_ar_journal_interim balance for each |
| BalanceId (reference22). |
| Any BalanceId that fails to balance will be reported on |
| (via WriteToLogFile), and will be deleted with ClearOOB |
| PARAMETERS |
| |
| EXCEPTIONS RAISED |
| |
| ERRORS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| 23-Jul-1993 Alan Fothergill Created |
*---------------------------------------------------------------------------*/
PROCEDURE CheckBalance( p_Report IN ReportParametersType ) IS
CURSOR CBal IS
SELECT MIN(i.currency_code) CurrencyCode,
i.reference22 BalanceId,
i.reference28 CategoryCode,
SUM(nvl(i.entered_dr,0)) SumEnteredDr,
SUM(nvl(i.entered_cr,0)) SumEnteredCr,
SUM(nvl(i.accounted_dr,0)) SumAccountedDr,
SUM(nvl(i.accounted_cr,0)) SumAccountedCr
FROM igi_ar_journal_interim i
WHERE i.request_id = p_Report.ReqId
AND i.set_of_books_id = p_Report.CashSetOfBooksId
GROUP BY i.reference28,
i.reference22
HAVING SUM( NVL(i.entered_dr,0) ) <> SUM( NVL(i.entered_cr, 0 ))
OR SUM( NVL(i.accounted_dr,0)) <> SUM( NVL(i.accounted_cr, 0));
SELECT i.entered_dr EnteredDr,
i.entered_cr EnteredCr,
i.accounted_dr AccountedDr,
i.accounted_cr AccountedCr,
i.reference30 TableName,
i.reference23 Id
FROM igi_ar_journal_interim i
WHERE i.request_id = p_Report.ReqId
AND i.set_of_books_id = p_Report.CashSetOfBooksId
AND i.reference22 = p_BalanceId
AND i.reference28 = p_CategoryCode
ORDER BY i.reference30,
i.reference23;
SELECT cbd.receivable_application_id
INTO l_ReceivableApplicationId
FROM igi_ar_cash_basis_dists cbd
WHERE cbd.cash_basis_distribution_id = RInt.Id;
| DeleteFromCBD |
| DESCRIPTION |
| Delete recrods from igi_ar_cash_basis_dists inserted this run |
| PARAMETERS |
| |
| EXCEPTIONS RAISED |
| |
| ERRORS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| 13-Apr-1994 D Chu Created |
*---------------------------------------------------------------------------*/
PROCEDURE DeleteFromCBD( p_Report IN ReportParametersType ) IS
BEGIN
--
DELETE FROM igi_ar_cash_basis_dists
WHERE posting_control_id = - ( p_Report.ReqId +1000 );
WriteToLogFile( l_excep_level,'DeleteFromCBD','DeleteFromCBD' );
END DeleteFromCBD;
SELECT column_id
FROM user_tab_columns
WHERE table_name = 'AR_CASH_BASIS_DISTRIBUTIONS'
AND column_name = 'CUSTOMER_TRX_LINE_ID';
WriteToLogFile( l_excep_level,'DeleteFromCBD','CheckUpgradedCustomer:' );
WriteToLogFile ( l_state_level,'DeleteFromCBD',' >> Report Cash Receipt History Data ');
WriteToLogFile (l_state_level,'DeleteFromCBD', ' >> Report MIsc Cash Distributions Data ');
WriteToLogFile (l_state_level,'DeleteFromCBD', ' >> Report Non Dist Applications Data ');
WriteToLogFile ( l_state_level,'DeleteFromCBD',' >> Report Distributed Applications Data ');
WriteToLogFile (l_state_level,'DeleteFromCBD', ' >> Check the Balance ');
WriteToLogFile (l_state_level,'DeleteFromCBD', ' >> Delete from Cash Basis Distributions ');
DeleteFromCBD( p_Report );
WriteToLogFile(l_excep_level,'DeleteFromCBD', 'Exception:IGIRCBJO.Report( p_Report ):'||sqlerrm );
FND_LOG.MESSAGE ( l_unexp_level,l_path || 'DeleteFromCBD', TRUE);
UPDATE fnd_concurrent_requests
SET has_sub_request = 'Y'
-- , status_code = 'W'-- This does not work! The parent
-- request restarts ad infinitum if
-- status_code set to 'W' (Paused).
WHERE request_id = p_Report.ReqId;
UPDATE fnd_concurrent_requests
SET status_code = 'I'
WHERE request_id = l_DetAccRequestId
AND status_code = 'Z';
UPDATE fnd_concurrent_requests
SET status_code = 'I'
WHERE request_id = l_DetCatRequestId
AND status_code = 'Z';
UPDATE fnd_concurrent_requests
SET status_code = 'I'
WHERE request_id = l_SumAccRequestId
AND status_code = 'Z';
UPDATE fnd_concurrent_requests
SET status_code = 'I'
WHERE request_id = l_SumCatRequestId
AND status_code = 'Z';
DELETE igi_ar_journal_interim
WHERE request_id = p_Report.ReqId;
select count(*) ct
from igi_ar_journal_interim
where request_id = p_request_id
;
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;
WriteToLogFile ( l_event_level,'Report','BEGIN insert (data) into AR_JOURNAL_INTERIM ');
WriteToLogFile (l_event_level,'Report', 'END (Successful) insert (data) into AR_JOURNAL_INTERIM ');