[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into igi_plsql_control
( report_run_id
, entry_type
, sequence
, entry_text )
values ( pp_id
, 'IGIRCBER'||pp_post_id
, l_rep_sequence
, pp_line );
WriteToLog( l_event_level, 'WritetoOut' ,'Record number '||l_rep_sequence||' is inserted into igi_plsql_control.');
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.set_of_books_id = p_sob_id
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' ) -- 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_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;
WritetoLog(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 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 igi_ar_cash_basis_dists.cash_basis_distribution_id%TYPE;
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,
receivable_application_id_cash
)
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
);
WritetoLog( l_excep_level, 'CreateDistribution','Exception:CreateDistribution.InsertCBD:' );
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_Post.CreatedBy, -- created_by,
TRUNC( SYSDATE ), -- date_created,
'NEW', -- status,
'A', -- actual_flag,
p_Post.PostingControlId, -- group_id,
p_Post.CashSetOfBooksId, -- set_of_books_id,
p_Post.UserSource, -- user_je_source_name,
decode(p_AmountAppFrom,
null,p_Post.UserTrade,
p_Post.UserCcurr), -- user_je_category_name,
p_App.GlDate, -- accounting_date,
p_Receipt.DocSequenceId, -- subledger_doc_sequence_id,
p_Receipt.DocSequenceValue, -- subledger_doc_sequence_value,
p_App.UssglTransactionCode, -- ussgl_transaction_code,
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,
'AR '||p_Post.PostingControlId, -- reference1,
DECODE
(
p_Post.SummaryFlag,
'Y', NULL,
DECODE(
P_App.AppType,
'CM',
'CM '||p_Receipt.ReceiptNumber||p_Post.NlsAppApplied||' '||p_Trx.Class||
' '||p_trx.TrxNumber,
p_Post.NlsPreTradeApp||' '||p_Receipt.ReceiptNumber||p_Post.NlsAppApplied||' '||p_Trx.Class||
' '||p_trx.TrxNumber||p_Post.NlsPostTradeApp
)
), -- reference10,
p_Post.PostingControlId, -- reference21,
p_Receipt.CashReceiptId, -- reference22,
CashBasisDistributionId, -- reference23,
p_Receipt.ReceiptNumber, -- reference24,
p_Trx.TrxNumber, -- reference25,
p_Trx.Class, -- reference26,
p_Receipt.PayFromCustomer, -- reference27,
DECODE(
P_App.AppType,
'CM', 'CMAPP',
'CASH',
decode(p_AmountAppFrom,
null,'TRADE','CCURR' )), -- reference28,
DECODE(
P_App.AppType,
'CASH',
decode(p_AmountAppFrom,
null,'TRADE_APP_'||p_Trx.Class||'_'||p_Source||'_'||p_Type,
'CCURR_APP_'||p_Trx.Class||'_'||p_Source||'_'||p_Type),
'CM', DECODE(
p_Trx.CmPsIdFlag,
'Y', 'CMAPP_REC_CM_'||p_Source||'_'||p_Type,
'CMAPP_APP_'||p_Trx.Class||'_'||p_Source||'_'||p_Type )), -- reference29,
'AR_CASH_BASIS_DISTRIBUTIONS' -- reference30
);
WritetoLog( l_excep_level, 'CreateDistribution','Exception:CreateDistribution.InsertGl:' );
| 20-Aug-1993 Alan Fothergill Placed exception handler around insert |
| statement |
*---------------------------------------------------------------------------*/
PROCEDURE PostNonDistApplications( p_Post IN PostingParametersType ) IS
CURSOR CRa IS
SELECT ra.ROWID RaRowid,
igi_ra.ROWID IGIRaRowid,
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,
trunc(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,
igi_ar_rec_applications igi_ra,
ar_cash_receipts cr
WHERE ra.receivable_application_id = igi_ra.receivable_application_id
AND igi_ra.arc_posting_control_id = p_Post.UnpostedPostingControlId
AND trunc(ra.gl_date) >= p_Post.GlDateFrom
AND trunc(ra.gl_date) <= p_Post.GlDateTo
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status not in ( 'APP','ACTIVITY')
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 igi_ra.arc_posting_control_id;
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_Post.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
'NEW', -- status
'A', -- actual flag
p_Post.PostingControlId, -- group_id,
p_Post.CashSetOfBooksId, -- set_of_books_id
p_Post.UserSource, -- user_je_source_name
decode(RRa.AmountAppFrom,
null,p_Post.UserTrade,p_Post.UserCcurr), -- user_je_category_name
RRa.GlDate, -- accounting_date
RRA.CrDocSequenceId, -- subledger_doc_sequence_id
RRa.CrDocSequenceValue, -- subledger_doc_sequence_value
RRa.UssglTransactionCode, -- ussgl_transaction_code
RRa.CurrencyCode, -- currency_code
RRa.CodeCombinationId, -- code_combination_id
DECODE
(
SIGN( RRa.amount ),
-1, -RRa.amount,
NULL
), -- entered_dr
DECODE
(
SIGN( RRa.amount ),
-1, NULL,
RRa.amount
), -- entered_cr
DECODE
(
SIGN( RRa.AcctdAmount ),
-1, -RRa.AcctdAmount,
NULL
), -- accounted_dr
DECODE
(
SIGN( RRa.AcctdAmount ),
-1, NULL,
RRa.AcctdAmount
), -- accounted_cr
'AR '||to_char(p_Post.PostingControlId), -- reference1
DECODE
(
p_Post.SummaryFlag,
'Y', NULL,
p_Post.NlsPreTradeApp||' '||RRa.ReceiptNumber||
DECODE
(
RRa.Status,
'ACC', p_Post.NlsAppOnAcc,
'UNAPP', p_Post.NlsAppUnapp,
'UNID', p_Post.NlsAppUnid
)||p_Post.NlsPostTradeApp
), -- reference10
p_Post.PostingControlId, -- reference21
RRa.CashReceiptId, -- reference22
RRa.ReceivableApplicationId, -- reference23
RRa.ReceiptNumber, -- reference24
NULL, -- reference25
NULL, -- reference26
RRa.PayFromCustomer, -- reference27
decode(RRa.AmountAppFrom,
null,'TRADE','CCURR'), -- reference28
decode(RRa.AmountAppFrom,
null,'TRADE_APP','CCURR_APP'), -- reference29
'AR_RECEIVABLE_APPLICATIONS' -- reference30
);
WritetoLog(l_excep_level, 'PostNonDistApplications','Exception:PostNonDistApplications.INSERT:' );
UPDATE igi_ar_rec_applications
SET arc_posting_control_id = p_Post.PostingControlId,
arc_gl_posted_date = p_Post.GlPostedDate
WHERE rowid = RRa.IGIRaRowid;
| 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 |
*---------------------------------------------------------------------------*/
PROCEDURE PostDistributedApplications( p_Post IN PostingParametersType ) IS
CURSOR CRa IS
SELECT ra.ROWID ra_rowid,
igira.ROWID igira_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,
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,
trunc(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
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
WHERE igira.arc_posting_control_id = p_Post.UnpostedPostingControlId
AND igira.receivable_application_id = ra.receivable_application_id
AND trunc(ra.gl_date) >= p_Post.GlDateFrom
AND trunc(ra.gl_date) <= p_Post.GlDateTo
AND nvl(ra.postable,'Y') = 'Y'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.status||'' in ( 'APP','ACTIVITY')
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 igira.arc_posting_control_id;
select 'x'
from ar_payment_schedules
where payment_schedule_id = fp_ps_id
;
UPDATE igi_ar_rec_applications
SET arc_posting_control_id = p_Post.PostingControlId,
arc_gl_posted_date = p_Post.GlPostedDate
WHERE rowid = l_IGIRowid;
| 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
*---------------------------------------------------------------------------*/
PROCEDURE PostCashReceiptHistory( p_Post IN PostingParametersType ) IS
CURSOR CCrh IS
SELECT crh.ROWID CrhRowid,
igicrh.ROWID IGICrhRowid,
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,
trunc(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,
igi_ar_cash_receipt_hist igicrh,
ar_cash_receipts cr,
ar_distributions d
WHERE trunc(crh.gl_date) >= p_Post.GlDateFrom
AND trunc(crh.gl_date) <= p_Post.GlDateTo
AND igicrh.arc_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 crh.cash_receipt_history_id = igicrh.cash_receipt_history_id
AND d.source_table = 'CRH'
FOR UPDATE OF igicrh.arc_posting_control_id;
PROCEDURE InsertIntoGl( RCrh IN CCrh%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,
reference27,
reference28,
reference29,
reference30
)
VALUES
(
p_Post.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
'NEW', -- status
'A', -- actual flag
p_Post.PostingControlId, -- group_id,
p_Post.CashSetOfBooksId, -- set_of_books_id
p_Post.UserSource, -- user_je_source_name
RCrh.Category, -- user_je_category_name
RCrh.GlDate, -- accounting_date
RCrh.DocSequenceId, -- subledger_doc_sequence_id
RCrh.DocSequenceValue, -- subledger_doc_sequence_value
RCrh.UssglTransactionCode, -- ussgl_transaction_code
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
'AR '||p_Post.PostingControlId, -- reference1
DECODE
(
p_Post.SummaryFlag,
'Y', NULL,
p_Post.NlsPreReceipt||' '||RCrh.ReceiptNumber||' '||p_Post.NlsPostReceipt
), -- reference10
p_Post.PostingControlId, -- reference21
RCrh.CashReceiptId, -- reference22
RCrh.CashReceiptHistoryId, -- reference23
RCrh.ReceiptNumber, -- reference24
RCrh.PayFromCustomer, -- reference27
RCrh.ModifiedType, -- reference28
RCrh.ModifiedType||'_'||RCrh.SourceType, -- reference29
'AR_CASH_RECEIPT_HISTORY' -- reference30
);
WriteToLog ( l_state_level, 'InsertIntoGL','Insert into GL interface okay!');
WritetoLog( l_excep_level, 'InsertIntoGL','InsertIntoGl:' );
InsertIntoGl( RCrh );
UPDATE igi_ar_cash_receipt_hist
SET arc_posting_control_id = p_Post.PostingControlId,
arc_gl_posted_date = p_Post.GlPostedDate
WHERE ROWID = RCrh.IGICrhRowid;
SELECT mcd.ROWID McdRowid,
igimcd.ROWID IGIMcdRowid,
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,
trunc(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,
igi_ar_misc_cash_dists igimcd,
ar_cash_receipts cr
WHERE igimcd.arc_posting_control_id = p_Post.UnpostedPostingControlId
AND trunc(mcd.gl_date) >= p_Post.GlDateFrom
AND trunc(mcd.gl_date) <= 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 = igimcd.misc_cash_distribution_id
FOR UPDATE OF igimcd.arc_posting_control_id;
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,
reference28,
reference29,
reference30
)
VALUES
(
p_Post.CreatedBy, -- created_by
TRUNC( SYSDATE ), -- date_created
'NEW', -- status
'A', -- actual flag
p_Post.PostingControlId, -- group_id,
p_Post.CashSetOfBooksId, -- set_of_books_id
p_Post.UserSource, -- user_je_source_name
RMcd.category, -- user_je_category_name
RMcd.gl_date, -- accounting_date
RMcd.doc_sequence_id, -- subledger_doc_sequence_id
RMcd.doc_sequence_value, -- subledger_doc_sequence_value
RMcd.ussgl_transaction_code, -- ussgl_transaction_code
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
'AR '||p_Post.PostingControlId, -- reference1
DECODE
(
p_Post.SummaryFlag,
'Y', NULL,
p_Post.NlsPreMiscDist||' '||RMcd.ReceiptNumber||p_Post.NlsPostMiscDist
), -- reference10
p_Post.PostingControlId, -- reference21
RMcd.CashReceiptId, -- reference22
RMcd.MiscCashDistributionId, -- reference23
RMcd.ReceiptNumber, -- reference24
'MISC', -- reference28
'MISC_MISC', -- reference29
'AR_MISC_CASH_DISTRIBUTIONS' -- reference30
);
UPDATE igi_ar_misc_cash_dists
SET arc_posting_control_id = p_Post.PostingControlId,
arc_gl_posted_date = p_Post.GlPostedDate
WHERE ROWID = RMcd.IGIMcdRowid;
UPDATE igi_ar_cash_receipt_hist iacrh
SET arc_gl_posted_date = NULL,
arc_posting_control_id = p_Post.UnpostedPostingControlId
WHERE arc_posting_control_id = p_Post.PostingControlId
AND EXISTS
( select cash_receipt_id
from ar_cash_receipt_history acrh
where acrh.cash_receipt_id = p_BalanceId
and acrh.cash_receipt_history_id = iacrh.cash_receipt_history_id
);
UPDATE igi_ar_cash_receipt_hist iacrh
SET arc_rev_gl_posted_date = NULL,
arc_rev_post_control_id = p_Post.UnpostedPostingControlId
WHERE arc_rev_post_control_id = p_Post.PostingControlId
AND EXISTS
( select cash_receipt_id
from ar_cash_receipt_history acrh
where acrh.cash_receipt_id = p_BalanceId
and acrh.cash_receipt_history_id = iacrh.cash_receipt_history_id
);
UPDATE igi_ar_misc_cash_dists iamcd
SET arc_gl_posted_date = NULL,
arc_posting_control_id = p_Post.UnpostedPostingControlId
WHERE arc_posting_control_id = p_Post.PostingControlId
AND EXISTS
( select cash_receipt_id
from ar_misc_cash_distributions amcd
where amcd.cash_receipt_id = p_BalanceId
and amcd.misc_cash_distribution_id = amcd.misc_cash_distribution_id
);
UPDATE igi_ar_rec_applications igiapp
SET arc_gl_posted_date = NULL,
arc_posting_control_id = p_Post.UnpostedPostingControlId
WHERE arc_posting_control_id = p_Post.PostingControlId
AND EXISTS
( select 'x'
from ar_receivable_applications app
where app.receivable_application_id = igiapp.receivable_application_id
and decode(p_CategoryCode,
'CMAPP',customer_trx_id,
'TRADE', cash_receipt_id) = p_BalanceId
) ;
DELETE FROM igi_ar_cash_basis_dists
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.CashSetOfBooksId
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.CashSetOfBooksId
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 WritetoLog), 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
, p_balance_flag in out NOCOPY varchar2 ) 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.CashSetOfBooksId
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.CashSetOfBooksId
AND i.reference22 = p_BalanceId
AND i.reference28 = p_CategoryCode
ORDER BY i.reference30,
i.reference23;
SELECT meaning
FROM igi_lookups
WHERE lookup_type = 'IGIRCBER_OUT_OF_BAL_REPORT'
AND lookup_code = p_lookup_code
;
select igi_plsql_control_s.nextval , p_Post.PostingControlId
into l_plsql_id, l_posting_id
from sys.dual;
SELECT cbd.receivable_application_id
INTO l_ReceivableApplicationId
FROM igi_ar_cash_basis_dists 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 distinct 'x'
FROM gl_interface
WHERE set_of_books_id = fp_cash_sob_id
AND group_id = fp_posting_control_id
AND user_je_source_name = fp_user_source;
SELECT message_text
INTO l_message_text
FROM fnd_new_messages
WHERE application_id = 222
AND message_name = p_message_name;
SELECT sob.currency_code
, sp.set_of_books_id
, igisp.arc_cash_sob_id
, igisp.arc_unalloc_rev_ccid
, igisp.arc_je_source_name
INTO l_Post.FuncCurr
, l_Post.SetOfBooksId
, l_Post.CashSetOfBooksId
, l_Post.UnallocatedRevCcid
, l_je_source_name
FROM ar_system_parameters sp
, igi_ar_system_options igisp
, gl_sets_of_books sob
WHERE sob.set_of_books_id = sp.set_of_books_id;
SELECT user_je_source_name
INTO l_Post.UserSource
FROM gl_je_sources
WHERE je_source_name = nvl(l_je_source_name,'Receivables');
SELECT user_je_category_name
INTO l_Post.UserTrade
FROM gl_je_categories
WHERE je_category_name = 'Trade Receipts';
SELECT user_je_category_name
INTO l_Post.UserMisc
FROM gl_je_categories
WHERE je_category_name = 'Misc Receipts';
SELECT user_je_category_name
INTO l_Post.UserCcurr
FROM gl_je_categories
WHERE je_category_name = 'Cross Currency';
SELECT nvl(max(crh.cash_receipt_history_id), 999999999999998)+1
INTO l_Post.NxtCashReceiptHistoryId
FROM ar_cash_receipt_history crh
, igi_ar_cash_receipt_hist igicrh
WHERE crh.cash_receipt_history_id = igicrh.cash_receipt_history_id
AND igicrh.arc_posting_control_id = p_PostingControlId;
SELECT nvl(max(app.receivable_application_id), 999999999999998)+1
INTO l_Post.NxtReceivableApplicationId
FROM ar_receivable_applications app
, igi_ar_rec_applications igiapp
WHERE app.receivable_application_id = igiapp.receivable_application_id
AND igiapp.arc_posting_control_id = p_PostingControlId;
SELECT nvl(max(mcd.misc_cash_distribution_id), 999999999999998)+1
INTO l_Post.NxtMiscCashDistributionId
FROM ar_misc_cash_distributions mcd
, igi_ar_misc_cash_dists igimcd
WHERE mcd.misc_cash_distribution_id = igimcd.misc_cash_distribution_id
and igimcd.arc_posting_control_id = p_PostingControlId;
SELECT nvl(max(adj.adjustment_id), 999999999999998)+1
INTO l_Post.NxtAdjustmentId
FROM ar_adjustments adj
, igi_ar_adjustments igiadj
WHERE adj.adjustment_id = igiadj.adjustment_id
AND igiadj.arc_posting_control_id = p_PostingControlId;
SELECT nvl(max(cust_trx_line_gl_dist_id), 999999999999998)+1
INTO l_Post.NxtCustTrxLineGlDistId
FROM ra_cust_trx_line_gl_dist
WHERE posting_control_id = p_PostingControlId;
SELECT GL_JOURNAL_IMPORT_S.nextval
, sp.ARC_cash_sob_id
, nvl(sp.arc_je_source_name, 'Receivables') arc_je_source_name
INTO l_cash_gl_interface_run_id
, l_cash_set_of_books_id
, l_je_source_name
FROM igi_ar_system_options sp
, ar_system_parameters asp
WHERE sp.set_of_books_id = asp.set_of_books_id
and nvl(sp.org_id,-99) = nvl(asp.org_id,-99)
;
SELECT arc_summary_flag, arc_run_gl_import_flag
INTO l_Arc_summary_flag, l_arc_run_gl_import
FROM igi_ar_posting_control
WHERE arc_posting_control_id = p_posting_control_id
AND rownum <= 1
;
INSERT INTO gl_interface_control
( je_source_name
, status
, interface_run_id
, group_id
, set_of_books_id)
VALUES ( l_je_source_name
, 'S'
, l_cash_gl_interface_run_id
, p_posting_control_id
, l_cash_set_of_books_id
);
update igi_ar_posting_control
set arc_gllezl_request_id = l_cash_gllezl_request_id
where arc_posting_control_id = p_posting_control_id
;