[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Insert record into gl_interface |
| PARAMETERS |
| |
| EXCEPTIONS RAISED |
| |
| ERRORS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| 13-JUL-2004 Hiroshi Yoshihara bug3692482 Created |
*---------------------------------------------------------------------------*/
PROCEDURE CreateInterface( p_interface_rec IN gl_interface%ROWTYPE) IS
BEGIN
INSERT INTO gl_interface
(
created_by,
date_created,
status,
actual_flag,
group_id,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
subledger_doc_sequence_id,
subledger_doc_sequence_value,
ussgl_transaction_code,
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
)
VALUES
(
p_interface_rec.created_by,
p_interface_rec.date_created,
p_interface_rec.status,
p_interface_rec.actual_flag,
p_interface_rec.group_id,
p_interface_rec.set_of_books_id,
p_interface_rec.user_je_source_name,
p_interface_rec.user_je_category_name,
p_interface_rec.accounting_date,
p_interface_rec.subledger_doc_sequence_id,
p_interface_rec.subledger_doc_sequence_value,
p_interface_rec.ussgl_transaction_code,
p_interface_rec.currency_code,
p_interface_rec.code_combination_id,
p_interface_rec.entered_dr,
p_interface_rec.entered_cr,
p_interface_rec.accounted_dr,
p_interface_rec.accounted_cr,
p_interface_rec.reference1,
p_interface_rec.reference10,
p_interface_rec.reference21,
p_interface_rec.reference22,
p_interface_rec.reference23,
p_interface_rec.reference24,
p_interface_rec.reference25,
p_interface_rec.reference26,
p_interface_rec.reference27,
p_interface_rec.reference28,
p_interface_rec.reference29,
p_interface_rec.reference30
);
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
GROUP BY cbd.source,
cbd.source_id
ORDER BY DECODE( cbd.source, 'GL', 1,
'ADJ',2,
'UNA',3 ),
cbd.source_id;
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_dists_mrc_v cbd
WHERE cbd.payment_schedule_id = p_ps_id
AND cbd.type = p_type
AND cbd.posting_control_id+0 > 0
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')
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_Post.NxtCustTrxLineGlDistId
ORDER BY ctlgd.cust_trx_line_gl_dist_id;
SELECT a.adjustment_id adjustment_id,
DECODE
(
cp_type,
'LINE', nvl(a.line_adjusted,0),
'TAX', nvl(a.tax_adjusted,0),
'FREIGHT', nvl(a.freight_adjusted,0),
'CHARGES', nvl(a.receivables_charges_adjusted,0),
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.type = cp_type
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 a.adjustment_id+0 < p_Post.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');
| Creates a distribution by inserting a record into |
| ar_cash_basis_distributions, and a record into gl_interface |
| PARAMETERS |
| |
| EXCEPTIONS RAISED |
| |
| ERRORS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| 23-Jul-1993 Alan Fothergill Created |
*---------------------------------------------------------------------------*/
PROCEDURE CreateDistribution( p_Post IN PostingParametersType,
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,
p_AmountAppFrom IN NUMBER ) IS
CashBasisDistributionId ar_cash_basis_distributions.cash_basis_distribution_id%TYPE;
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,
receivable_application_id_cash,
org_id
)
VALUES
(
CashBasisDistributionId,
p_Post.CreatedBy,
TRUNC( SYSDATE ),
p_Post.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_Post.PostingControlId,
p_Post.GlPostedDate,
NULL,
p_Trx.OrgId
);
arp_standard.debug( 'Exception:CreateDistribution.InsertCBD:' );
/* bug3692482 replace insert stmt with CreateInterface procedure */
l_gl_interface := l_gl_interface_null;
arp_standard.debug( 'Exception:CreateDistribution.InsertGl:' );
| 20-Aug-1993 Alan Fothergill Placed exception handler around insert |
| statement |
| 16-JAN-2002 R Kader Modified the cursor CRa to fetch the |
| ACTIVITY records also. |
| See bug 2177009 / 2187023 for details |
| 03-JUN-2003 M Raymond Removed MRC schema dependency
| 03-DEC-2004 M Raymond Changed source of AmountAppFrom
| for MRC rows - see bug 3904994 for dets.
*---------------------------------------------------------------------------*/
PROCEDURE PostNonDistApplications( p_Post IN PostingParametersType ) IS
CURSOR CRa IS
SELECT ra.ROWID RaRowid,
cr.cash_receipt_id CashReceiptId,
cr.receipt_number ReceiptNumber,
cr.doc_sequence_id CrDocSequenceId,
cr.doc_sequence_value CrDocSequenceValue,
cr.pay_from_customer PayFromCustomer,
cr.currency_code CurrencyCode,
ra.receivable_application_id ReceivableApplicationId,
ra.gl_date GlDate,
ra.ussgl_transaction_code UssglTransactionCode,
ra.amount_applied Amount,
ra.amount_applied_from AmountAppFrom,
ra.acctd_amount_applied_from AcctdAmount,
ra.code_combination_id CodeCombinationId,
ra.status Status
FROM ar_receivable_applications ra,
ar_cash_receipts cr
WHERE ra.posting_control_id = p_Post.UnpostedPostingControlId
AND ra.gl_date BETWEEN p_Post.GlDateFrom
AND p_Post.GlDateTo
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status <> 'APP' -- Bug 2187023
AND ra.application_type||'' = 'CASH'
AND cr.cash_receipt_id = ra.cash_receipt_id
AND ra.receivable_application_id+0 < p_Post.NxtReceivableApplicationId
FOR UPDATE OF ra.receivable_application_id;
SELECT ra_mrc.ROWID RaRowid,
cr.cash_receipt_id CashReceiptId,
cr.receipt_number ReceiptNumber,
cr.doc_sequence_id CrDocSequenceId,
cr.doc_sequence_value CrDocSequenceValue,
cr.pay_from_customer PayFromCustomer,
cr.currency_code CurrencyCode,
ra.receivable_application_id ReceivableApplicationId,
ra.gl_date GlDate,
ra.ussgl_transaction_code UssglTransactionCode,
ra_mrc.amount_applied Amount,
ra.amount_applied_from AmountAppFrom,
ra_mrc.acctd_amount_applied_from AcctdAmount,
ra.code_combination_id CodeCombinationId,
ra_mrc.status Status
FROM ar_receivable_applications ra,
ar_mc_receivable_apps ra_mrc,
ar_cash_receipts cr
WHERE ra_mrc.posting_control_id = p_Post.UnpostedPostingControlId
AND ra.gl_date BETWEEN p_Post.GlDateFrom
AND p_Post.GlDateTo
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status <> 'APP' -- Bug 2187023
AND ra.application_type||'' = 'CASH'
AND cr.cash_receipt_id = ra.cash_receipt_id
AND ra.receivable_application_id+0 < p_Post.NxtReceivableApplicationId
AND ra.receivable_application_id = ra_mrc.receivable_application_id
AND ra_mrc.set_of_books_id = p_Post.SetOfBooksId
FOR UPDATE OF ra_mrc.receivable_application_id;
/* bug3692482 replace insert stmt with CreateInterface procedure */
l_gl_interface := l_gl_interface_null ;
arp_standard.debug( 'Exception:PostNonDistApplications.INSERT:' );
UPDATE ar_receivable_applications
SET posting_control_id = p_Post.PostingControlId,
gl_posted_date = p_Post.GlPostedDate
WHERE rowid = RRa.RaRowid;
/* bug3692482 replace insert stmt with CreateInterface procedure */
-- l_gl_interface := l_gl_interface_null ;
| We need to have ORDER BY clause in the select statement because |
| when comparing with Journal Entry report, 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 |
| 22-NOV-2000 M Raymond Changed exchange rate calc in cursor
| for receipts (CRa) to utilize the
| trans_to_receipt_rate from the
| ar_receivable_applications table.
| See bug 1429867 for details.
| New exchange rate is calculated as:
| NVL(crh.exchange_rate,1) *
| NVL(ra.trans_to_receipt_rate,1)
| 18-APR-01 S.Nambiar Modified the sql to fetch activity record
| also
| 16-JAN-2002 R Kader Modified the cursor CRa not to fetch
| ACTIVITY records
| See bug 2177009 / 2187023 for details
| 03-JUN-2003 M Raymond Removed MRC schema dependency
| this one looks iffy!
*---------------------------------------------------------------------------*/
PROCEDURE PostDistributedApplications( p_Post IN PostingParametersType ) 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.doc_sequence_id,
'CASH',cr.doc_sequence_id ) CrDocSequenceId,
DECODE(
ra.application_type,
'CM', ctcm.doc_sequence_value,
'CASH',cr.doc_sequence_value ) CrDocSequenceValue,
DECODE(
ra.application_type,
'CM', ctcm.bill_to_customer_id,
'CASH',cr.pay_from_customer ) PayFromCustomer,
/*Bug3235636 ct.invoice_currency_code commented to take it from
get_currency_code*/
/*ct.invoice_currency_code CurrencyCode,*/
/*For Bug 4936298 change ar_ta_util_pub to arpt_sql_func_util*/
arpt_sql_func_util.get_currency_code(ra.application_type,ra.status,
'CURR_',cr.currency_code,ct.invoice_currency_code) CurrencyCode,
DECODE(
ra.application_type,
'CM', NVL(ctcm.exchange_rate,1),
'CASH',NVL(crh.exchange_rate,1) *
NVL(ra.trans_to_receipt_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.gl_date GlDate,
ra.ussgl_transaction_code UssglTransactionCode,
ra.application_type AppType,
DECODE(
l.lookup_code,
'1', ra.amount_applied,
'2', -ra.amount_applied
) Amount,
DECODE(
ra.application_type,
'CM',null,
'CASH',ra.amount_applied_from
) AmountAppFrom,
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,
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
WHERE ra.posting_control_id = p_Post.UnpostedPostingControlId
AND ra.gl_date BETWEEN p_Post.GlDateFrom
AND p_Post.GlDateTo
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status||'' = 'APP' -- Bug 2187023
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 ra.receivable_application_id+0 < p_Post.NxtReceivableApplicationId
ORDER BY ra.receivable_application_id, l.lookup_code
FOR UPDATE OF ra.receivable_application_id;
UPDATE ar_receivable_applications
SET posting_control_id = p_Post.PostingControlId,
gl_posted_date = p_Post.GlPostedDate
WHERE rowid = l_Rowid;
| 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 InsertIntoGl |
| relies on the ROWTYPE of each select cursor being the same |
| |
| HISTORY |
| 23-Jul-1993 Alan Fothergill Created |
| 21-Mar-1995 C Aldamiz Modified for 10.6
| 03-JUN-2003 M Raymond Removed MRC schema dependency
*---------------------------------------------------------------------------*/
PROCEDURE PostCashReceiptHistory( p_Post IN PostingParametersType ) 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,
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,
cr.currency_code CurrencyCode,
DECODE
(
cr.type,
'MISC', p_Post.UserMisc,
p_Post.UserTrade
) Category,
cr.doc_sequence_id DocSequenceId,
cr.doc_sequence_value DocSequenceValue,
cr.ussgl_transaction_code UssglTransactionCode,
d.source_type SourceType
FROM ar_cash_receipt_history crh,
ar_cash_receipts cr,
ar_distributions d
WHERE crh.gl_date BETWEEN p_Post.GlDateFrom
AND p_Post.GlDateTo
AND crh.posting_control_id = p_Post.UnpostedPostingControlId
AND crh.postable_flag = 'Y'
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.cash_receipt_history_id+0 < p_Post.NxtCashReceiptHistoryId
AND crh.cash_receipt_history_id = d.source_id
AND d.source_table = 'CRH'
FOR UPDATE OF crh.cash_receipt_history_id;
PROCEDURE InsertIntoGl( RCrh IN CCrh%ROWTYPE ) IS
l_gl_interface gl_interface%ROWTYPE ;
/* bug3692482 replace insert stmt with CreateInterface procedure */
l_gl_interface := l_gl_interface_null;
arp_standard.debug( 'InsertIntoGl:' );
InsertIntoGl( RCrh );
UPDATE ar_cash_receipt_history
SET posting_control_id = p_Post.PostingControlId,
gl_posted_date = p_Post.GlPostedDate
WHERE ROWID = RCrh.CrhRowid;
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,
cr.currency_code currency_code,
p_Post.UserMisc category,
cr.doc_sequence_id doc_sequence_id,
cr.doc_sequence_value doc_sequence_value,
mcd.ussgl_transaction_code ussgl_transaction_code
FROM ar_misc_cash_distributions mcd,
ar_cash_receipts cr
WHERE mcd.posting_control_id = p_Post.UnpostedPostingControlId
AND mcd.gl_date BETWEEN p_Post.GlDateFrom
AND p_Post.GlDateTo
AND cr.cash_receipt_id = mcd.cash_receipt_id
AND mcd.misc_cash_distribution_id+0 < p_Post.NxtMiscCashDistributionId
FOR UPDATE OF mcd.misc_cash_distribution_id;
SELECT mcd_mrc.ROWID McdRowid,
mcd_mrc.misc_cash_distribution_id MiscCashDistributionId,
cr.cash_receipt_id CashReceiptId,
cr.receipt_number ReceiptNumber,
mcd_mrc.amount amount,
mcd_mrc.acctd_amount acctd_amount,
mcd.code_combination_id code_combination_id,
mcd.gl_date gl_date,
cr.currency_code currency_code,
p_Post.UserMisc category,
cr.doc_sequence_id doc_sequence_id,
cr.doc_sequence_value doc_sequence_value,
mcd.ussgl_transaction_code ussgl_transaction_code
FROM ar_misc_cash_distributions mcd,
ar_mc_misc_cash_dists mcd_mrc,
ar_cash_receipts cr
WHERE mcd.posting_control_id = p_Post.UnpostedPostingControlId
AND mcd.gl_date BETWEEN p_Post.GlDateFrom
AND p_Post.GlDateTo
AND cr.cash_receipt_id = mcd.cash_receipt_id
AND mcd.misc_cash_distribution_id+0 < p_Post.NxtMiscCashDistributionId
AND mcd.misc_cash_distribution_id = mcd_mrc.misc_cash_distribution_id
AND mcd_mrc.set_of_books_id = p_Post.SetOfBooksId
FOR UPDATE OF mcd_mrc.misc_cash_distribution_id;
/* bug3692482 replace insert stmt with CreateInterface procedure */
l_gl_interface := l_gl_interface_null;
UPDATE ar_misc_cash_distributions
SET posting_control_id = p_Post.PostingControlId,
gl_posted_date = p_Post.GlPostedDate
WHERE ROWID = RMcd.McdRowid;
/* bug3692482 replace insert stmt with CreateInterface procedure */
l_gl_interface := l_gl_interface_null ;
UPDATE ar_cash_receipt_history
SET gl_posted_date = NULL,
posting_control_id = p_Post.UnpostedPostingControlId
WHERE posting_control_id = p_Post.PostingControlId
AND cash_receipt_id = p_BalanceId;
UPDATE ar_cash_receipt_history
SET reversal_gl_posted_date = NULL,
reversal_posting_control_id = p_Post.UnpostedPostingControlId
WHERE reversal_posting_control_id = p_Post.PostingControlId
AND cash_receipt_id = p_BalanceId;
UPDATE ar_mc_cash_receipt_hist
SET gl_posted_date = NULL,
posting_control_id = p_Post.UnpostedPostingControlId
WHERE posting_control_id = p_Post.PostingControlId
AND cash_receipt_id = p_BalanceId
AND set_of_books_id = p_Post.SetOfBooksId;
UPDATE ar_mc_cash_receipt_hist
SET reversal_gl_posted_date = NULL,
reversal_posting_control_id = p_Post.UnpostedPostingControlId
WHERE reversal_posting_control_id = p_Post.PostingControlId
AND cash_receipt_id = p_BalanceId
AND set_of_books_id = p_Post.SetOfBooksId;
UPDATE ar_misc_cash_distributions
SET gl_posted_date = NULL,
posting_control_id = p_Post.UnpostedPostingControlId
WHERE posting_control_id = p_Post.PostingControlId
AND cash_receipt_id = p_BalanceId;
UPDATE ar_mc_misc_cash_dists
SET gl_posted_date = NULL,
posting_control_id = p_Post.UnpostedPostingControlId
WHERE posting_control_id = p_Post.PostingControlId
AND cash_receipt_id = p_BalanceId
AND set_of_books_id = p_Post.SetOfBooksID;
UPDATE ar_receivable_applications
SET gl_posted_date = NULL,
posting_control_id = p_Post.UnpostedPostingControlId
WHERE posting_control_id = p_Post.PostingControlId
AND decode(p_CategoryCode,
'CMAPP',customer_trx_id,
'TRADE', cash_receipt_id) = p_BalanceId;
DELETE FROM ar_cash_basis_distributions
WHERE cash_basis_distribution_id IN (
SELECT reference23
FROM gl_interface
WHERE reference22 = p_BalanceId
AND reference28 = p_CategoryCode
AND set_of_books_id = p_Post.SetOfBooksId
AND group_id = p_Post.PostingControlId
AND user_je_source_name = p_Post.UserSource
AND reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
);
UPDATE ar_mc_receivable_apps
SET gl_posted_date = NULL,
posting_control_id = p_Post.UnpostedPostingControlId
WHERE posting_control_id = p_Post.PostingControlId
AND set_of_books_id = p_Post.SetOfBooksId
AND receivable_application_id IN (
SELECT ra.receivable_application_id
FROM ar_receivable_applications ra
WHERE p_CategoryCode = 'CMAPP'
AND ra.customer_trx_id = p_BalanceId
UNION
SELECT ra.receivable_application_id
FROM ar_receivable_applications ra
WHERE p_CategoryCode = 'TRADE'
AND ra.cash_receipt_id = p_BalanceId);
DELETE FROM ar_mc_cash_basis_dists_all
WHERE set_of_books_id = p_Post.SetOfBooksId
AND cash_basis_distribution_id IN (
SELECT reference23
FROM gl_interface
WHERE reference22 = p_BalanceId
AND reference28 = p_CategoryCode
AND set_of_books_id = p_Post.SetOfBooksId
AND group_id = p_Post.PostingControlId
AND user_je_source_name = p_Post.UserSource
AND reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
);
DELETE FROM gl_interface
WHERE reference22 = p_BalanceId
AND reference28 = p_CategoryCode
AND set_of_books_id = p_Post.SetOfBooksId
AND group_id = p_Post.PostingControlId
AND user_je_source_name = p_Post.UserSource;
| Checks that the records inserted into gl_interface 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_Post IN PostingParametersType ) 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 gl_interface i
WHERE i.group_id = p_Post.PostingControlId
AND i.user_je_source_name = p_Post.UserSource
AND i.set_of_books_id = p_Post.SetOfBooksId
AND i.accounting_date BETWEEN p_Post.GlDateFrom
AND p_Post.GlDateTo
GROUP BY i.reference28,
i.reference22
HAVING ( nvl(decode(i.reference28,'CCURR',
0,sum(nvl(entered_dr,0))),0)<>nvl(decode(i.reference28,'CCURR',
0,sum(nvl(entered_cr,0))),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 gl_interface i
WHERE i.group_id = p_Post.PostingControlId
AND i.user_je_source_name = p_Post.UserSource
AND set_of_books_id = p_Post.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;
SELECT column_id
FROM user_tab_columns
WHERE table_name = 'AR_CASH_BASIS_DISTRIBUTIONS'
AND column_name = 'CUSTOMER_TRX_LINE_ID';
SELECT nvl(mrc_sob_type_code,'P')
INTO p_Post.SetOfBooksType
FROM gl_sets_of_books
WHERE set_of_books_id = p_Post.SetOfBooksID;