[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 ar_cash_basis_distributions 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;
arp_standard.debug( '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');
| Inserts a record into ar_journal_interim |
| PARAMETERS |
| |
| EXCEPTIONS RAISED |
| |
| ERRORS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| 02-Jul-2004 Hiroshi Yoshihara bug3718694 Created |
*---------------------------------------------------------------------------*/
PROCEDURE CreateInterim( p_interim_rec IN ar_journal_interim%ROWTYPE) IS
BEGIN
INSERT INTO
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,
org_id
)
VALUES
(
p_interim_rec.status,
p_interim_rec.actual_flag,
p_interim_rec.request_id,
p_interim_rec.created_by,
p_interim_rec.date_created,
p_interim_rec.set_of_books_id,
p_interim_rec.je_source_name,
p_interim_rec.je_category_name,
p_interim_rec.transaction_date,
p_interim_rec.accounting_date,
p_interim_rec.currency_code,
p_interim_rec.code_combination_id,
p_interim_rec.entered_dr,
p_interim_rec.entered_cr,
p_interim_rec.accounted_dr,
p_interim_rec.accounted_cr,
p_interim_rec.reference10,
p_interim_rec.reference21,
p_interim_rec.reference22,
p_interim_rec.reference23,
p_interim_rec.reference24,
p_interim_rec.reference25,
p_interim_rec.reference26,
p_interim_rec.reference27,
p_interim_rec.reference28,
p_interim_rec.reference29,
p_interim_rec.reference30,
p_interim_rec.org_id
);
| Creates a distribution by inserting a record into |
| ar_cash_basis_distributions, and a record into 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 ar_cash_basis_distributions.cash_basis_distribution_id%TYPE;
INSERT INTO
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,
org_id
)
SELECT
'NEW', -- status
'A', -- actual flag
p_Report.ReqId, -- request_id
p_Report.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
p_Report.SetOfBooksId, -- 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
cbd.org_id
FROM ar_cash_basis_distributions 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;
arp_standard.debug( 'Exception:CreateDistribution.InsertPostedAR:' );
SELECT ar_cash_basis_distributions_s.NEXTVAL
INTO CashBasisDistributionId
FROM dual;
INSERT INTO ar_cash_basis_distributions
(
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,
org_id
)
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 +100 ),
TRUNC( SYSDATE ),
p_trx.OrgId
);
arp_standard.debug( 'Exception:CreateDistribution.InsertCBD:' );
-- bug3718694 Call CreateInterim procedure to insert record into
-- ar_journal_interim .
DECLARE
l_interim_rec ar_journal_interim%ROWTYPE;
arp_standard.debug( 'Exception:CreateDistribution.InsertAR:' );
SELECT ra.ROWID RaRowid,
cr.cash_receipt_id CashReceiptId,
cr.receipt_number ReceiptNumber,
cr.pay_from_customer PayFromCustomer,
cust.account_number CustomerNumber,
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,
cr.org_id OrgId
FROM ar_receivable_applications ra,
ar_cash_receipts cr,
hz_cust_accounts cust,
ar_lookups l_cat
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 cr.cash_receipt_id = ra.cash_receipt_id
AND cr.pay_from_customer = cust.cust_account_id
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 ( ra.posting_control_id = DECODE( p_Report.PostedStatus,
'BOTH', ra.posting_control_id,
'UNPOSTED', -3,
-8888 )
OR
ra.posting_control_id <> decode( p_Report.PostedStatus,
'BOTH', -8888,
'POSTED', -3,
ra.posting_control_id) )
AND NVL(ra.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
BETWEEN
DECODE( p_Report.PostedStatus,
'BOTH', nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateFrom ,
null, nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom))
AND
DECODE( p_Report.PostedStatus,
'BOTH', nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateTo,
null, nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo))
AND ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId;
-- bug3718694 Call CreateInterim procedure to insert record into
-- ar_journal_interim .
DECLARE
l_interim_rec ar_journal_interim%ROWTYPE;
arp_standard.debug( 'Exception:ReportNonDistApplications.INSERT:' );
arp_standard.debug( ' '||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,
cust.account_number CustomerNumber,
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,
ra.posting_control_id PostingControlId,
ct.org_id OrgID
FROM ar_receivable_applications ra,
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_cust_accounts cust
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.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 l.lookup_type = 'AR_CARTESIAN_JOIN'
AND (
( l.lookup_code ='1' )
OR
( l.lookup_code = '2'
AND
ra.application_type = 'CM' )
)
AND cust.cust_account_id = DECODE( ra.application_type,
'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 ( ra.posting_control_id = DECODE( p_Report.PostedStatus,
'BOTH', ra.posting_control_id,
'UNPOSTED', -3,
-8888 )
OR
ra.posting_control_id <> decode( p_Report.PostedStatus,
'BOTH', -8888,
'POSTED', -3,
ra.posting_control_id) )
AND NVL(ra.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
BETWEEN
DECODE( p_Report.PostedStatus,
'BOTH', nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateFrom ,
null, nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom))
AND
DECODE( p_Report.PostedStatus,
'BOTH', nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateTo,
null, nvl(ra.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo))
AND ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId
ORDER BY ra.receivable_application_id, l.lookup_code;
arp_standard.debug( ' '||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.account_number CustomerNumber,
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,
cr.org_id OrgId
FROM ar_cash_receipt_history crh,
ar_cash_receipts cr,
hz_cust_accounts cust,
ar_lookups l_cat,
ar_distributions d
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.cust_account_id(+) = cr.pay_from_customer
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 ( crh.posting_control_id = DECODE( p_Report.PostedStatus,
'BOTH', crh.posting_control_id,
'UNPOSTED', -3,
-8888 )
OR
crh.posting_control_id <> decode( p_Report.PostedStatus,
'BOTH', -8888,
'POSTED', -3,
crh.posting_control_id))
AND NVL(crh.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
BETWEEN
DECODE( p_Report.PostedStatus,
'BOTH', nvl(crh.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(crh.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', 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.PostedStatus,
'BOTH', nvl(crh.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(crh.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateTo,
null, nvl(crh.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+0 < p_Report.NxtCashReceiptHistoryId;
-- bug3718694 Call CreateInterim procedure to insert record into
-- ar_journal_interim .
PROCEDURE InsertIntoAR( RCrh IN CCrh%ROWTYPE ) IS
l_interim_rec ar_journal_interim%ROWTYPE;
arp_standard.debug( 'InsertIntoAR:' );
InsertIntoAR( RCrh );
arp_standard.debug( ' '||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,
cr.org_id OrgId
FROM ar_misc_cash_distributions mcd,
ar_cash_receipts cr,
ar_lookups l_cat
WHERE mcd.gl_date BETWEEN p_Report.GlDateFrom
AND p_Report.GLDateTo
AND cr.cash_receipt_id = mcd.cash_receipt_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 ( mcd.posting_control_id = DECODE( p_Report.PostedStatus,
'BOTH', mcd.posting_control_id,
'UNPOSTED', -3,
-8888 )
OR
mcd.posting_control_id <> decode( p_Report.PostedStatus,
'BOTH', -8888,
'POSTED', -3,
mcd.posting_control_id) )
AND NVL(mcd.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
BETWEEN
DECODE( p_Report.PostedStatus,
'BOTH', nvl(mcd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(mcd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateFrom ,
null, nvl(mcd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateFrom))
AND
DECODE( p_Report.PostedStatus,
'BOTH', nvl(mcd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'UNPOSTED',nvl(mcd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
'POSTED', decode( p_Report.PostedDateTo,
null, nvl(mcd.gl_posted_date,to_date('01-01-1952',
'DD-MM-YYYY')),
p_Report.PostedDateTo))
AND mcd.misc_cash_distribution_id+0 < p_Report.NxtMiscCashDistributionId;
-- bug3718694 Call CreateInterim procedure to insert record into
-- ar_journal_interim .
l_interim_rec := l_interim_rec_null ;
arp_standard.debug( ' '||l_Count||' lines selected' );
DELETE FROM ar_journal_interim
WHERE reference22 = p_BalanceId
AND reference28 = p_CategoryCode
AND set_of_books_id = p_Report.SetOfBooksId
AND request_id = p_Report.ReqId;
| Checks that the records inserted into ar_journal_interim balance for each |
| BalanceId (reference22). |
| Any BalanceId that fails to balance will be reported on |
| (via arp_standard.debug), 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 ar_journal_interim i
WHERE i.request_id = p_Report.ReqId
AND i.set_of_books_id = p_Report.SetOfBooksId
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 ar_journal_interim i
WHERE i.request_id = p_Report.ReqId
AND i.set_of_books_id = p_Report.SetOfBooksId
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 ar_cash_basis_distributions cbd
WHERE cbd.cash_basis_distribution_id = RInt.Id;
| DeleteFromCBD |
| DESCRIPTION |
| Delete recrods from ar_cash_basis_distributions 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 ar_cash_basis_distributions
WHERE posting_control_id = - ( p_Report.ReqId +100 );
arp_standard.debug( 'DeleteFromCBD' );
END DeleteFromCBD;
SELECT column_id
FROM user_tab_columns
WHERE table_name = 'AR_CASH_BASIS_DISTRIBUTIONS'
AND column_name = 'CUSTOMER_TRX_LINE_ID';
DeleteFromCBD( p_Report );