[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cash_receipt_history_id FROM ar_cash_receipt_history
WHERE posting_control_id = l_pst_ctrl_id
AND cash_receipt_history_id NOT IN
(SELECT cash_receipt_history_id FROM psa_misc_posting);
SELECT cr.cash_receipt_id FROM ar_cash_receipts cr, ar_cash_receipt_history crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.posting_control_id = l_pst_ctrl_id;
SELECT
mfd.gl_date gl_date,
cr.doc_sequence_id doc_seqid,
cr.doc_sequence_value doc_num,
ard.currency_code currency,
decode(to_number(l1.lookup_code),
1, mfd.cash_ccid, 2, ard2.code_combination_id)
ccid,
decode(to_number(l1.lookup_code), 1, ard.amount_cr, 2, ard.amount_dr) entered_dr,
decode(to_number(l1.lookup_code),1, ard.amount_dr, 2, ard.amount_cr) entered_cr,
decode(to_number(l1.lookup_code),1, ard.acctd_amount_cr, 2, ard.acctd_amount_dr) accounted_dr,
decode(to_number(l1.lookup_code),1, ard.acctd_amount_dr, 2, ard.acctd_amount_cr) accounted_cr,
l_batch_prefix || TO_CHAR(l_pst_ctrl_id) ref1,
DECODE(to_number(l1.lookup_code),1, ('MFAR Misc. Receipt ' || cr.receipt_number),
2,('Receipt ' || cr.receipt_number||'(MFAR)')) ref10,
TO_CHAR (mcd.posting_control_id) ref21,
TO_CHAR (cr.cash_receipt_id) ref22,
TO_CHAR (ard.line_id) ref23,
cr.receipt_number ref24,
TO_CHAR (mcd.misc_cash_distribution_id) ref25,
NULL ref26,
'c1' ref27,
'MISC' ref28,
'MISC_' || ard.source_type ref29,
'PSA_MF_MISC_DIST_ALL' ref30
FROM
psa_mf_misc_dist_all mfd,
psa_lookup_codes l1,
ar_misc_cash_distributions mcd,
ar_distributions ard,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_distributions ard2
WHERE
l1.lookup_type = 'PSA_CARTESIAN_JOIN'
AND l1.lookup_code IN ('1','2')
AND mfd.misc_cash_distribution_id = mcd.misc_cash_distribution_id
AND nvl(mfd.posting_control_id,-3)= -3
AND crh.status = mfd.reference1
AND mcd.posting_control_id = l_pst_ctrl_id
AND mcd.set_of_books_id = l_sob_id
AND mcd.cash_receipt_id = cr.cash_receipt_id
AND ard.source_table = 'MCD'
AND ard.source_id = mcd.misc_cash_distribution_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.posting_control_id = l_pst_ctrl_id
AND ((crh.first_posted_record_flag = 'Y') OR (crh.current_record_flag = 'Y' AND crh.status = 'REVERSED'))
AND crh.cash_receipt_history_id = ard2.source_id
AND ard2.source_table = 'CRH'
AND (ard2.amount_cr is null or ard2.amount_cr > 0);
SELECT
crh.gl_date gl_date,
cr.doc_sequence_id doc_seqid,
cr.doc_sequence_value doc_num,
cr.currency_code currency,
ard.code_combination_id ccid,
to_number(ard.amount_cr) entered_dr,
to_number(ard.amount_dr) entered_cr,
to_number(ard.acctd_amount_cr) accounted_dr,
to_number(ard.acctd_amount_dr) accounted_cr,
l_batch_prefix || TO_CHAR (l_pst_ctrl_id) ref1,
('Receipt ' || cr.receipt_number||'(MFAR)') ref10,
TO_CHAR (l_pst_ctrl_id) ref21,
DECODE(cr.type,
'CASH',TO_CHAR(cr.cash_receipt_id)||'C'||
TO_CHAR(crh.cash_receipt_history_id),
'MISC',TO_CHAR(cr.cash_receipt_id)) ref22,
TO_CHAR(ard.line_id) ref23,
cr.receipt_number ref24,
DECODE(cr.type,
'CASH',TO_CHAR(NULL),
'MISC',TO_CHAR(crh.cash_receipt_history_id)) ref25,
TO_CHAR(NULL) ref26,
'c2' ref27,
DECODE( cr.type,
'MISC', 'MISC',
'TRADE') ref28,
DECODE( cr.type,
'MISC', 'MISC_',
'TRADE_') || ard.source_type ref29,
'AR_CASH_RECEIPT_HISTORY' ref30
FROM
ar_cash_receipt_history crh,
psa_receivables_trx_all psa,
ar_distributions ard,
ar_cash_receipts cr
WHERE
crh.status <> 'REVERSED'
AND crh.posting_control_id = l_pst_ctrl_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.receivables_trx_id = psa.psa_receivables_trx_id
AND cr.set_of_books_id = l_sob_id
AND ard.source_table = 'CRH'
AND ard.source_id = crh.cash_receipt_history_id
AND nvl(crh.first_posted_record_flag, 'N') = 'N';
SELECT
mfd.gl_date gl_date,
cr.doc_sequence_id doc_seqid,
cr.doc_sequence_value doc_num,
cr.currency_code currency,
decode(to_number(l1.lookup_code), 1, mfd.cash_ccid) ccid,
-- 2, mfd.reversal_ccid) ccid, -- rgopalan
decode(crh.status, 'CLEARED', decode(to_number(l1.lookup_code),1, mcd.amount, null),
'REMITTED',decode(to_number(l1.lookup_code),2, mcd.amount, null)) entered_dr,
decode(crh.status, 'CLEARED', decode(to_number(l1.lookup_code),2, mcd.amount, null),
'REMITTED',decode(to_number(l1.lookup_code),1, mcd.amount, null)) entered_cr,
decode(crh.status, 'CLEARED', decode(to_number(l1.lookup_code),1, mcd.amount, null),
'REMITTED',decode(to_number(l1.lookup_code),2, mcd.amount, null)) accounted_dr,
decode(crh.status, 'CLEARED', decode(to_number(l1.lookup_code),2, mcd.amount, null),
'REMITTED',decode(to_number(l1.lookup_code),1, mcd.amount, null)) accounted_cr,
l_batch_prefix || TO_CHAR (l_pst_ctrl_id) ref1,
DECODE(l1.lookup_code,1, ('MFAR Cash ' || cr.receipt_number),
2,('MFAR Remittance ' || cr.receipt_number)) ref10,
TO_CHAR (l_pst_ctrl_id) ref21,
DECODE(cr.type,
'CASH',TO_CHAR(cr.cash_receipt_id)||'C'||
TO_CHAR(crh.cash_receipt_history_id),
'MISC',TO_CHAR(cr.cash_receipt_id)) ref22,
-- TO_CHAR(mfd.misc_cash_distribution_id) ref23,
nvl( get_misc_ard_id(mfd.misc_cash_distribution_id),
to_char(mfd.misc_cash_distribution_id) ) ref23,
cr.receipt_number ref24,
DECODE(cr.type,
'CASH',TO_CHAR(NULL),
'MISC',TO_CHAR(crh.cash_receipt_history_id)) ref25,
TO_CHAR(NULL) ref26,
'c3' ref27,
DECODE( cr.type,
'MISC', 'MISC',
'TRADE') ref28,
DECODE( cr.type,
'MISC', 'MISC_',
'TRADE_') ref29,
'PSA_MF_MISC_DIST_ALL' ref30
FROM
psa_mf_misc_dist_all mfd,
psa_lookup_codes l1,
ar_misc_cash_distributions mcd,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_cash_receipt_history crhold
WHERE
mfd.reference1 = 'CLEARED'
AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
AND l1.lookup_code IN (1,2)
AND mfd.misc_cash_distribution_id = mcd.misc_cash_distribution_id
AND mcd.set_of_books_id = l_sob_id
AND mcd.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.posting_control_id = l_pst_ctrl_id
AND crh.cash_receipt_history_id = crhold.reversal_cash_receipt_hist_id
AND nvl(crh.first_posted_record_flag, 'N') = 'N'
AND ((crh.STATUS <> 'REVERSED'));
' --> Inserting into GL INTERFACE foor - PSA_MF_MISC_DIST_ALL');
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,
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 (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
l_misc_cat_name,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30);
' --> Inserting into GL INTERFACE for - PSA_MF_REVERSE_OF_AR_CRH');
## For each misc_cash_distribution_id the record will be inserted.
*/
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,
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 (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
l_misc_cat_name,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30);
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,
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 (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
l_misc_cat_name,
K.gl_date,
K.doc_seqid,
K.doc_num,
K.currency,
K.ccid,
K.entered_dr,
K.entered_cr,
K.accounted_dr,
K.accounted_cr,
K.ref1,
K.ref10,
K.ref21,
K.ref22,
K.ref23,
K.ref24,
K.ref25,
K.ref26,
K.ref27,
K.ref28,
K.ref29,
K.ref30);
## Insert a record into psa_misc_posting to keep track of
## each reversing record of AR_CASH_RECEIPT_HISTORY, that we insert into GL_INTERFACE
*/
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,
' --> Inserting into psa_misc_posting ');
INSERT INTO psa_misc_posting (cash_receipt_history_id, posting_control_id)
VALUES (J.cash_receipt_history_id, l_pst_ctrl_id);
UPDATE psa_mf_misc_dist_all
SET posting_control_id = l_pst_ctrl_id
WHERE misc_cash_distribution_id IN
(SELECT misc_cash_distribution_id FROM ar_misc_cash_distributions
WHERE posting_control_id = l_pst_ctrl_id);
' --> (PSA_MF_MISC_DIST_ALL) Updated Posting control id for '
|| (SQL%ROWCOUNT));
DELETE FROM psa_mf_misc_dist_all
WHERE posting_control_id = l_pst_ctrl_id
AND misc_cash_distribution_id NOT IN
(SELECT misc_cash_distribution_id
FROM ar_misc_cash_distributions
WHERE posting_control_id = l_pst_ctrl_id);
' --> (PSA_MF_MISC_DIST_ALL) Deleted --> '
|| (SQL%ROWCOUNT));
-- Added UNION clauses to select transactions associated with receipts and adjustments
SELECT ctlgd.customer_trx_id customer_trx_id
FROM ra_cust_trx_line_gl_dist ctlgd
WHERE ctlgd.posting_control_id = l_pst_ctrl_id
AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (ctlgd.customer_trx_id, 'TRX', l_sob_id),
'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
UNION
SELECT customer_trx_id
FROM ar_adjustments adj
WHERE adj.posting_control_id = l_pst_ctrl_id
AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (adj.adjustment_id, 'ADJ', l_sob_id),
'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
AND arp_global.sysparam.accounting_method = 'ACCRUAL'
UNION
SELECT applied_customer_trx_id
FROM ar_receivable_applications ra
WHERE ra.status = 'APP'
AND ra.posting_control_id = l_pst_ctrl_id
AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (ra.receivable_application_id, 'RCT', l_sob_id),
'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
UNION
SELECT ra.applied_customer_trx_id
FROM ar_receivable_applications ra,
ar_cash_receipt_history crh,
ar_cash_receipt_history crho
WHERE crh.posting_control_id = l_pst_ctrl_id
AND crh.cash_receipt_history_id = crho.reversal_cash_receipt_hist_id
AND crh.cash_receipt_id = ra.cash_receipt_id
AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (ra.receivable_application_id, 'RCT', l_sob_id),
'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
AND ra.status = 'APP';
SELECT
DECODE (ctt.type, 'CM', l_cm_cat_name,
'DM', l_dm_cat_name,
'CB', l_cb_cat_name, l_inv_cat_name) category,
ctlgd.gl_date gl_date,
ct.doc_sequence_id doc_seqid,
ct.doc_sequence_value doc_num,
ct.invoice_currency_code currency,
DECODE (l1.lookup_code,
'1', psa_trx_dist.mf_receivables_ccid, /* DEBIT A/C FROM PSA_TRX_DIST */
'2', psa_mfar_utils.get_rec_ccid (null,p_customer_trx_id)) /* CREDIT A/C FROM CORE TRX_DIST */ ccid,
DECODE (l1.lookup_code,
'1', DECODE (SIGN (ctlgd.amount), -1, NULL, ctlgd.amount),
'2', DECODE (SIGN (ctlgd.amount), -1, -ctlgd.amount, NULL)) entered_dr,
DECODE (l1.lookup_code,
'1', DECODE (SIGN (ctlgd.amount), -1, -ctlgd.amount, NULL),
'2', DECODE (SIGN (ctlgd.amount), -1, NULL, ctlgd.amount)) entered_cr,
DECODE (l1.lookup_code,
'1', DECODE (SIGN (ctlgd.amount), -1, NULL, ctlgd.amount),
'2', DECODE (SIGN (ctlgd.amount), -1, -ctlgd.amount, NULL)) accounted_dr,
DECODE (l1.lookup_code,
'1', DECODE (SIGN (ctlgd.amount), -1, -ctlgd.amount, NULL),
'2', DECODE (SIGN (ctlgd.amount), -1, NULL, ctlgd.amount)) accounted_cr,
l_batch_prefix || TO_CHAR(l_pst_ctrl_id) ref1,
DECODE (l1.lookup_code,
'1', 'MFAR Receivable' ,
'2', 'MFAR Reversal of Receivable' ) || ' ' || l_pre_ct_line ||
' ' || 'Invoice ' || ct.trx_number || l_post_ct_line ref10,
TO_CHAR(l_pst_ctrl_id) ref21,
TO_CHAR(ct.customer_trx_id) ref22,
TO_CHAR(ctlgd.cust_trx_line_gl_dist_id) ref23,
ct.trx_number ref24,
hca.account_number ref25,
'CUSTOMER' ref26,
TO_CHAR(ct.bill_to_customer_id) ref27,
DECODE (ctt.type, 'CM', 'CM',
'DM', 'DM',
'CB', 'CB', 'INV') ref28,
DECODE (ctt.type, 'CM', 'CM_',
'DM', 'DM_',
'CB', 'CB_', 'INV_') || ctlgd.account_class ref29,
-- 'PSA_TRX_DIST' ref30
'RA_CUST_TRX_LINE_GL_DIST' ref30
FROM
ar_lookups l,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist ctlgd,
ra_cust_trx_types ctt,
hz_cust_accounts hca,
psa_lookup_codes l1,
psa_mf_trx_dist_all psa_trx_dist
WHERE
ctlgd.customer_trx_id = p_customer_trx_id
AND ctlgd.customer_trx_id = ct.customer_trx_id
AND l.lookup_type = 'AUTOGL_TYPE'
AND l.lookup_code = nvl(ctlgd.account_class,'REV')
AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
AND l1.lookup_code in
(1, decode(ctt.type, 'INV', decode(l_post_det_acct_flag, 'N', -1, 2),
'DM', decode(l_post_det_acct_flag, 'N', -1, 2), 2))
AND ct.bill_to_customer_id = hca.cust_account_id -- cust.customer_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctlgd.account_Class <> 'REC'
AND psa_trx_dist.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
AND nvl(ctlgd.amount,0) <> 0
AND ctlgd.posting_control_id = l_pst_ctrl_id
AND nvl(psa_trx_dist.posting_control_id, -3) = -3;
DELETE FROM GL_INTERFACE GI
WHERE GI.user_je_source_name = 'Receivables'
AND GI.set_of_books_id = l_sob_id
AND GI.group_id = l_pst_ctrl_id
AND GI.reference29 IN ('INV_REC', 'DM_REC')
AND GI.reference30 = 'RA_CUST_TRX_LINE_GL_DIST'
AND GI.reference22 = to_char(I.customer_trx_id);
' --> DELETE FROM GL_INTERFACE ' || SQL%ROWCOUNT);
## For each Cutomer trax id the record will be inserted.
*/
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,
' --> Customer trx id ==> ' || I.customer_trx_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,
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 (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
J.category,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30);
' --> Inserting into GL INTERFACE for ==> '
|| I.customer_trx_id );
UPDATE psa_mf_trx_dist_all ptda
SET ptda.posting_control_id = l_pst_ctrl_id
WHERE ptda.cust_trx_line_gl_dist_id IN
(SELECT cust_trx_line_gl_dist_id FROM ra_cust_trx_line_gl_dist rct
WHERE rct.posting_control_id = l_pst_ctrl_id);
' --> (PSA_MF_TRX_DIST_ALL) Updated Posting control id for '
|| SQL%ROWCOUNT);
DELETE FROM psa_mf_trx_dist_all
WHERE posting_control_id = l_pst_ctrl_id
AND cust_trx_line_gl_dist_id NOT IN
( SELECT cust_trx_line_gl_dist_id FROM ra_cust_trx_line_gl_dist rct
WHERE rct.posting_control_id = l_pst_ctrl_id);
' --> DELETE FROM psa_mf_trx_dist_all ==> ' || SQL%ROWCOUNT);
SELECT h1.cash_receipt_history_id, h1.status
FROM ar_cash_receipt_history h1,
ar_cash_receipt_history h2
WHERE h1.posting_control_id = l_pst_ctrl_id
AND h1.cash_receipt_history_id = h2.reversal_cash_receipt_hist_id
ORDER BY h1.cash_receipt_history_id ;
SELECT distinct ra.receivable_application_id receivable_application_id
FROM ar_receivable_applications ra
WHERE ra.status = 'APP'
AND ra.posting_control_id = l_pst_ctrl_id
AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check
(ra.receivable_application_id, 'RCT', l_sob_id) , 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE';
SELECT distinct ra.receivable_application_id receivable_application_id
FROM ar_receivable_applications ra, ar_cash_receipt_history crh, ar_cash_receipt_history crho
WHERE crh.posting_control_id = l_pst_ctrl_id
AND crh.cash_receipt_history_id = crho.reversal_cash_receipt_hist_id
AND crh.cash_receipt_id = ra.cash_receipt_id
AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check
(ra.receivable_application_id, 'RCT', p_set_of_books_id) , 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
AND ra.status = 'APP';
CURSOR mfar_rcpt_lines will insert thw following categories of records in gl_interface
If Payment Method has staus = 'CLEARED' ( No Remittance involved in Receipt processing)
1. MFAR Receivable Account
2. MFAR Reversal of Core Receivable Account
3. MFAR Cash Account
4. MFAR Reversal of Core Cash Account ( derived from Transaction Dist A/c)
(OR)
If Payment Method has staus = 'REMITTED' ( Remittance in Receipt processing - Receipt cleared through Cash Mgt.)
1. MFAR Receivable Account
2. MFAR Reversal of Core Receivable Account
3. MFAR Remittance Account
4. MFAR reversal of Core Remittance Account ( derived from Transaction Dist A/c)
In AR_CASH_RECEIPT_HISTORY_ALL, account_code_combination_id stores remittance account if status = 'REMITTED'
account_code_combination_id stores Cash account if status = 'CLEARED'
For MFAR Entries, the description should be appropriately changed based on Remittance requirement.
###############################################################################
*/
CURSOR Cur_mfar_rct_lines (p_receivable_application_id NUMBER)
IS
SELECT
DECODE(to_number(l1.lookup_code), '4', ra.ussgl_transaction_code,
'8', ra.ussgl_transaction_code,
'12', ra.ussgl_transaction_code, NULL) ussgl,
DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL, l_trade_cat_name, l_ccurr_cat_name),
'CM', l_cm_cat_name ) category,
ra.gl_date gl_date,
DECODE (ra.application_type, 'CASH', cr.doc_sequence_id,
'CM', ctcm.doc_sequence_id) doc_seqid,
DECODE (ra.application_type, 'CASH', cr.doc_sequence_value,
'CM', ctcm.doc_sequence_value) doc_num,
DECODE (ra.application_type, 'CASH', DECODE(ra.status, 'APP',
DECODE( SUBSTR(ard.source_type,1,5),
'EXCH_', DECODE (cr.currency_code, l_func_curr, ctinv.invoice_currency_code, cr.currency_code), ctinv.invoice_currency_code), cr.currency_code),
'CM', ctcm.invoice_currency_code) currency,
DECODE (to_NUMBER(l1.lookup_code), 1, psa_rct_dist.mf_cash_ccid,
2, DECODE(ra.application_type, 'CM', psa_mfar_utils.get_rec_ccid (ra.applied_customer_trx_id, ra.customer_trx_id), crh.account_code_combINation_id),
3, ra.code_combINation_id,
4, psa_trx_dist.mf_receivables_ccid,
5, psa_rct_dist.discount_ccid,
6, ra.earned_discount_ccid,
7, ra.code_combINation_id,
8, psa_trx_dist.mf_receivables_ccid,
9, psa_rct_dist.ue_discount_ccid,
10, ra.unearned_discount_ccid,
11, ra.code_combINation_id,
12, psa_trx_dist.mf_receivables_ccid) ccid,
DECODE (ra.application_type, 'CM', get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount),
get_entered_dr_rct (l1.lookup_code, psa_rct_dist.amount,
psa_rct_dist.discount_amount,
psa_rct_dist.ue_discount_amount)) entered_dr,
DECODE (ra.application_type, 'CM', get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount),
get_entered_cr_rct (l1.lookup_code, psa_rct_dist.amount,
psa_rct_dist.discount_amount,
psa_rct_dist.ue_discount_amount)) entered_cr,
DECODE (ra.application_type, 'CM', get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount),
get_entered_dr_rct (l1.lookup_code, psa_rct_dist.amount,
psa_rct_dist.discount_amount,
psa_rct_dist.ue_discount_amount)) accounted_dr,
DECODE (ra.application_type, 'CM', get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount),
get_entered_cr_rct (l1.lookup_code, psa_rct_dist.amount,
psa_rct_dist.discount_amount,
psa_rct_dist.ue_discount_amount)) accounted_cr,
DECODE(ard.source_type, 'EXCH_GAIN', TO_CHAR(ra.code_combINation_id),
'EXCH_LOSS', TO_CHAR(ra.code_combINation_id),
'CURR_ROUND', TO_CHAR(ra.code_combINation_id),
l_batch_prefix || TO_CHAR(l_pst_ctrl_id)) ref1,
SUBSTRB (DECODE (l1.lookup_code, '1', DECODE (ra.application_type, 'CM', 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || ' Receivable account for credit memo' || ctcm.trx_NUMBER || '.',
'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || DECODE(crh.status,'CLEARED', 'Cash Account for ', 'REMITTED', ' Remittance Account for ')),
'2', DECODE (ra.application_type, 'CM',
'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || ' Reversal of Receivable account for credit memo '|| ctcm.trx_NUMBER || '.',
'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || DECODE(crh.status, 'CLEARED', ' Reversal of Cash Account for ' , 'REMITTED', ' Reversal of Remittance Account for ')),
'3', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Reversal of AR for ',
'4', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Receivable Account for ',
'5', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct earn-disc): ',
'6', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core earn-disc):' ,
'7', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):' ,
'8', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' ,
'9', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct unearn-disc):',
'10', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core unearn-disc):',
'11', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):',
'12', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' )
|| DECODE (l_summary_flag, 'Y', NULL, DECODE(ra.application_type,
/* Cash Receipt application */
'CASH', DECODE (ard.source_type, 'REC', l_pre_tradeapp ||' '|| cr.receipt_NUMBER ||
DECODE (ra.status, 'ACC', l_app_onacc,
'UNAPP', l_app_unapp,
'UNID', l_app_unid,
'APP', l_app_applied, NULL),
'EDISC', l_pre_erdisc ||' '|| cr.receipt_NUMBER || l_app_applied,
'EDISC_NON_REC_TAX', l_pre_rec_erdisc_nrtax ||' '|| cr.receipt_NUMBER || l_app_applied,
'UNEDISC', l_pre_undisc ||' '|| cr.receipt_NUMBER || l_app_applied,
'UNEDISC_NON_REC_TAX', l_pre_rec_undisc_nrtax ||' '|| cr.receipt_NUMBER || l_app_applied,
'EXCH_GAIN', l_pre_rec_gain ||' '|| cr.receipt_NUMBER || l_app_applied,
'EXCH_LOSS', l_pre_rec_loss ||' '|| cr.receipt_NUMBER || l_app_applied,
'CURR_ROUND', l_pre_rec_curr_round ||' '|| cr.receipt_NUMBER || l_app_applied,
'TAX', l_pre_rec_tax ||' '|| cr.receipt_NUMBER || l_app_applied,
'DEFERRED_TAX', l_pre_rec_deftax ||' '|| cr.receipt_NUMBER || l_app_applied) ||
DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR',l_class_guar,
'INV', l_class_inv,NULL) ||
' ' || ctinv.trx_NUMBER || l_post_general)),1,240) ref10,
TO_CHAR(l_pst_ctrl_id) ref21,
DECODE (ra.application_type, 'CASH',TO_CHAR(cr.cash_receipt_id) || 'C' || TO_CHAR(ra.receivable_application_id),
'CM', TO_CHAR(ra.receivable_application_id)) ref22,
-- psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID ref23,
DECODE(ra.application_type, 'CASH', ard.line_id,
'CM', psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
-- nvl(ard.line_id, psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
DECODE (ra.application_type, 'CASH', cr.receipt_NUMBER,
'CM', ctcm.trx_NUMBER) ref24,
ctinv.trx_NUMBER ref25,
ctt.type ref26,
DECODE (ra.application_type, 'CASH', TO_CHAR(cr.pay_from_customer),
'CM', TO_CHAR(ctcm.bill_to_customer_id)) ref27,
DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL,'TRADE','CCURR'),
'CM', 'CM') ref28,
DECODE(ra.application_type, 'CASH', DECODE (ra.amount_applied_from, NULL,'TRADE_' ||ard.source_type, 'CCURR_' ||ard.source_type),
'CM', 'CM_'||ard.source_type) ref29,
DECODE(ra.application_type, 'CASH', 'PSA_RCT_DIST',
'CM', 'RA_CUST_TRX_LINE_GL_DIST') ref30
FROM
ar_receivable_applications ra,
ar_cash_receipts cr,
(SELECT * FROM ar_distributions
WHERE source_table = 'RA'
AND source_id = p_receivable_application_id
AND rownum = 1) ard,
ra_customer_trx ctcm,
ra_customer_trx ctinv,
ra_cust_trx_types ctt,
ar_cash_receipt_history crh,
psa_mf_rct_dist_all psa_rct_dist,
psa_mf_trx_dist_all psa_trx_dist,
psa_lookup_codes l1
WHERE
psa_rct_dist.receivable_application_id = p_receivable_application_id
AND psa_rct_dist.ue_discount_ccid IS NULL
AND psa_rct_dist.receivable_application_id = ra.receivable_application_id
AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
/* For MFAR we consider only thr APP rows */
AND 'APP' = DECODE(ra.application_type, 'CASH',ra.status, 'CM','APP')
AND ra.cash_receipt_id = cr.cash_receipt_id(+)
AND ra.customer_trx_id = ctcm.customer_trx_id(+)
AND ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
AND ctinv.cust_trx_type_id = ctt.cust_trx_type_id(+)
AND ra.cash_receipt_id = crh.cash_receipt_id(+)
AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
AND l1.lookup_code IN ('1','4','5','7','8','9','12',
decode(l_rct_post_det_flag, 'N', -1, 2),
decode(l_rct_post_det_flag, 'N', -1, 3),
decode(l_rct_post_det_flag, 'N', -1, 6),
decode(l_rct_post_det_flag, 'N', -1, 10),
decode(l_rct_post_det_flag, 'N', -1, 11))
AND DECODE (ceil(to_NUMBER(l1.lookup_code)/4), 1, nvl(psa_rct_dist.amount,0),
2, nvl(psa_rct_dist.discount_amount,0),
3, nvl(psa_rct_dist.ue_discount_amount,0), 0) <> 0
AND l1.lookup_code <= DECODE(ra.application_type, 'CM', 2, l1.lookup_code)
AND ra.posting_control_id = l_pst_ctrl_id
AND nvl(psa_rct_dist.posting_control_id, -3) = -3
/* For bug 3397563, NVL in case there is no crh record */
AND NVL(crh.status, 'CLEARED') IN ('CLEARED','REMITTED')
AND NVL(crh.first_posted_record_flag,'Y') = 'Y';
SELECT
DECODE(to_number(l1.lookup_code), '4', ra.ussgl_transaction_code,
'8', ra.ussgl_transaction_code,
'12', ra.ussgl_transaction_code, NULL) ussgl,
DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL, l_trade_cat_name, l_ccurr_cat_name),
'CM', l_cm_cat_name ) category,
ra.gl_date gl_date,
ctcm.doc_sequence_id doc_seqid,
ctcm.doc_sequence_value doc_num,
ctcm.invoice_currency_code currency,
DECODE (to_NUMBER(l1.lookup_code), 1, gld_inv.code_combination_id,
2, gld.code_combination_id) ccid,
get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount) entered_dr,
get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount) entered_cr,
get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount) accounted_dr,
get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount) accounted_cr,
DECODE(ard.source_type, 'EXCH_GAIN', TO_CHAR(ra.code_combINation_id),
'EXCH_LOSS', TO_CHAR(ra.code_combINation_id),
'CURR_ROUND', TO_CHAR(ra.code_combINation_id),
l_batch_prefix || TO_CHAR(l_pst_ctrl_id)) ref1,
SUBSTRB (DECODE (l1.lookup_code, '1', 'Revenue account for Credit Memo' || ctcm.trx_NUMBER || '.',
'2', 'MFAR Reversal of Revenue account for credit memo '|| ctcm.trx_NUMBER || '.'),1,240) ref10,
TO_CHAR(l_pst_ctrl_id) ref21,
TO_CHAR(ra.receivable_application_id) ref22,
-- psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID ref23,
-- nvl(ard.line_id, psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
DECODE(ra.application_type, 'CASH', ard.line_id,
'CM', psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
ctcm.trx_NUMBER ref24,
ctinv.trx_NUMBER ref25,
ctt.type ref26,
TO_CHAR(ctcm.bill_to_customer_id) ref27,
DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL,'TRADE','CCURR'),
'CM', 'CMAPP') ref28,
DECODE(ra.application_type, 'CASH', DECODE (ra.amount_applied_from, NULL,'TRADE_' ||ard.source_type, 'CCURR_' ||ard.source_type),
'CM', 'CMAPP_'||ard.source_type) ref29,
-- 'PSA_RCT_DIST' ref30
DECODE(ra.application_type, 'CASH', 'PSA_RCT_DIST',
'CM', 'RA_CUST_TRX_LINE_GL_DIST') ref30
FROM
ar_receivable_applications ra,
(SELECT * FROM ar_distributions
WHERE source_table = 'RA'
AND source_id = p_receivable_application_id
AND rownum = 1) ard,
ra_customer_trx ctcm,
ra_cust_trx_line_gl_dist gld,
ra_cust_trx_line_gl_dist gld_inv,
ra_customer_trx ctinv,
ra_cust_trx_types ctt,
ar_cash_receipt_history crh,
psa_mf_rct_dist_all psa_rct_dist,
psa_mf_trx_dist_all psa_trx_dist,
psa_lookup_codes l1
WHERE
psa_rct_dist.receivable_application_id = p_receivable_application_id
AND psa_rct_dist.receivable_application_id = ra.receivable_application_id
AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
AND psa_rct_dist.cust_trx_line_gl_dist_id = gld_inv.cust_trx_line_gl_dist_id
AND gld_inv.ACCOUNT_class = 'REV' /* For MFAR we consider only thr APP rows */
AND 'APP' = DECODE(ra.application_type, 'CASH',ra.status, 'CM','APP')
AND ra.customer_trx_id = ctcm.customer_trx_id(+)
/* Bug 3397563, check for On Account Credit Memo */
AND ctcm.previous_customer_trx_id IS NULL
AND ctcm.customer_trx_id = gld.customer_trx_id
AND gld.account_class = 'REV'
AND ra.customer_trx_id = ctcm.customer_trx_id(+)
AND ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
AND ctinv.cust_trx_type_id = ctt.cust_trx_type_id(+)
AND ra.cash_receipt_id = crh.cash_receipt_id(+)
AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
AND l1.lookup_code IN ('1','2')
AND ra.posting_control_id = l_pst_ctrl_id
AND nvl(psa_rct_dist.posting_control_id, -3) = -3
AND crh.status(+) = 'CLEARED';
SELECT
DECODE(ra.amount_applied_from, NULL, l_trade_cat_name, l_ccurr_cat_name) category,
crhnew.gl_date gl_date,
cr.doc_sequence_id doc_seqid,
cr.doc_sequence_value doc_num,
crhnew.status newstatus,
crhold.status oldstatus,
DECODE(ra.status, 'APP', DECODE( SUBSTR(ard.source_type,1,5),
'EXCH_', DECODE (cr.currency_code, l_func_curr, ctinv.invoice_currency_code, cr.currency_code),
ctinv.invoice_currency_code), cr.currency_code) currency,
DECODE (to_NUMBER(l1.lookup_code), 1, psa_rct_dist.ue_discount_ccid, -- mfar remittance (CR)-- check remittance/cash
2, decode(crhnew.status,'REMITTED',crhnew.account_code_combination_id,crhold.account_code_combination_id), -- Core Remittance (DB)
3, decode(crhnew.status,'REMITTED',crhold.account_code_combination_id,crhnew.account_code_combination_id), -- Core Cash (CR)
4, decode(psa_rct_dist.attribute1,'CLEARED',psa_rct_dist.mf_cash_ccid)) -- MFAR Cash (DB)
ccid,
get_entered_dr_rct_clear(l1.lookup_code, psa_rct_dist.amount,crhnew.status,crhold.status) entered_dr,
get_entered_cr_rct_clear(l1.lookup_code, psa_rct_dist.amount,crhnew.status,crhold.status) entered_cr,
get_entered_dr_rct_clear(to_number(l1.lookup_code), psa_rct_dist.amount,crhnew.status,crhold.status) accounted_dr,
get_entered_cr_rct_clear(l1.lookup_code, psa_rct_dist.amount,crhnew.status,crhold.status) accounted_cr,
DECODE(ard.source_type, 'EXCH_GAIN', TO_CHAR(ra.code_combINation_id),
'EXCH_LOSS', TO_CHAR(ra.code_combINation_id),
'CURR_ROUND', TO_CHAR(ra.code_combINation_id),
l_batch_prefix || TO_CHAR(l_pst_ctrl_id)) ref1,
SUBSTRB (DECODE (l1.lookup_code, '1', 'CSH MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || 'Remittance Reversal for ',
'2', 'CSH MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || 'Reversal of Core Remittance for ',
'3', 'CSH MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Reversal of Core Cash A/c ',
'4', 'CSH MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Cash Account for ',
'5', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct earn-disc): ',
'6', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core earn-disc):' ,
'7', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):' ,
'8', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' ,
'9', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct unearn-disc):',
'10', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core unearn-disc):',
'11', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):',
'12', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' )
|| DECODE (l_summary_flag, 'Y', NULL, DECODE(ra.application_type,
/* Cash Receipt application */
'CASH', DECODE (ard.source_type, 'REC', l_pre_tradeapp ||' '|| cr.receipt_NUMBER ||
DECODE (ra.status, 'ACC', l_app_onacc,
'UNAPP', l_app_unapp,
'UNID', l_app_unid,
'APP', l_app_applied, NULL),
'EDISC', l_pre_erdisc ||' '|| cr.receipt_NUMBER || l_app_applied,
'EDISC_NON_REC_TAX', l_pre_rec_erdisc_nrtax ||' '|| cr.receipt_NUMBER || l_app_applied,
'UNEDISC', l_pre_undisc ||' '|| cr.receipt_NUMBER || l_app_applied,
'UNEDISC_NON_REC_TAX', l_pre_rec_undisc_nrtax ||' '|| cr.receipt_NUMBER || l_app_applied,
'EXCH_GAIN', l_pre_rec_gain ||' '|| cr.receipt_NUMBER || l_app_applied,
'EXCH_LOSS', l_pre_rec_loss ||' '|| cr.receipt_NUMBER || l_app_applied,
'CURR_ROUND', l_pre_rec_curr_round ||' '|| cr.receipt_NUMBER || l_app_applied,
'TAX', l_pre_rec_tax ||' '|| cr.receipt_NUMBER || l_app_applied,
'DEFERRED_TAX', l_pre_rec_deftax ||' '|| cr.receipt_NUMBER || l_app_applied) ||
DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR',l_class_guar,
'INV', l_class_inv,NULL) ||
' ' || ctinv.trx_NUMBER || l_post_general)),1,240)
ref10,
TO_CHAR(l_pst_ctrl_id) ref21,
TO_CHAR(cr.cash_receipt_id) || 'C' || TO_CHAR(ra.receivable_application_id) ref22,
-- psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID ref23,
nvl(ard.line_id, psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
cr.receipt_NUMBER ref24,
ctinv.trx_NUMBER ref25,
ctt.type ref26,
to_char(cr.pay_from_customer) ref27,
DECODE(ra.amount_applied_from, NULL,'TRADE','CCURR') ref28,
DECODE (ra.amount_applied_from, NULL,'TRADE_' ||ard.source_type, 'CCURR_' ||ard.source_type)
ref29,
'PSA_RCT_DIST' ref30
FROM
ar_receivable_applications ra,
ar_cash_receipts cr,
(SELECT * FROM ar_distributions
WHERE source_table = 'RA'
AND source_id = p_receivable_application_id
AND rownum = 1) ard,
ra_customer_trx ctinv,
ra_cust_trx_types ctt,
ar_cash_receipt_history crhnew,
ar_cash_receipt_history crhold,
psa_mf_rct_dist_all psa_rct_dist,
psa_mf_trx_dist_all psa_trx_dist,
psa_lookup_codes l1
WHERE
psa_rct_dist.receivable_application_id = p_receivable_application_id
AND psa_rct_dist.ue_discount_ccid IS NOT NULL
AND psa_rct_dist.attribute1 = 'CLEARED'
AND psa_rct_dist.receivable_application_id = ra.receivable_application_id
AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
/* For MFAR we consider only thr APP rows */
AND 'APP' = DECODE(ra.application_type, 'CASH',ra.status, 'CM','APP')
AND ra.cash_receipt_id = cr.cash_receipt_id(+)
AND ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
AND ctinv.cust_trx_type_id = ctt.cust_trx_type_id(+)
AND ra.cash_receipt_id = crhnew.cash_receipt_id --outer joinremoved
AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
AND l1.lookup_code IN ('1','2','3','4') --,'5','6','7','8','9','10','11','12')
AND crhnew.posting_control_id = l_pst_ctrl_id
AND crhnew.cash_receipt_history_id = p_crhid
AND crhold.reversal_cash_receipt_hist_id = crhnew.cash_receipt_history_id
AND nvl(crhnew.first_posted_record_flag, 'N') = 'N';
SELECT to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) cash_receipt_id,
sum(accounted_dr) sum_acctd_dr,
sum(accounted_cr) sum_acctd_cr
FROM gl_interface gl
WHERE gl.user_je_source_name = 'Receivables'
AND gl.set_of_books_id = c_sob_id
AND gl.group_id = c_group_id
AND substr(gl.reference29, 7) IN ('CASH', 'REC')
AND gl.reference10 NOT LIKE 'MFAR%'
AND to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) IN
(select cash_receipt_id from ar_receivable_applications where receivable_application_id = c_rcv_app_id)
GROUP BY to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1))
HAVING sum(accounted_dr) = sum(accounted_cr);
' --> Inserting into gl interface ');
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,
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,
ussgl_transaction_code)
VALUES (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
J.category,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30,
NULL);
' --> Inserting into GL INTERFACE for Receipts (Cash Cleared) '||
' - Receivable Application id ==> ' || I.receivable_application_id);
UPDATE psa_mf_rct_dist_all pda
SET pda.posting_control_id = l_pst_ctrl_id
WHERE pda.attribute1 = 'CLEARED'
AND pda.receivable_application_id IN
(SELECT receivable_application_id FROM ar_receivable_applications ara, ar_cash_receipt_history crh
WHERE ara.cash_receipt_id = crh.cash_receipt_id AND crh.status = 'CLEARED'
AND crh.posting_control_id = l_pst_ctrl_id) ;
' UPDATE psa_mf_rct_dist_all '|| SQL%ROWCOUNT);
DELETE FROM gl_interface gl
WHERE gl.user_je_source_name = 'Receivables'
AND gl.set_of_books_id = l_sob_id
AND gl.group_id = l_pst_ctrl_id
AND substr(gl.reference29, 7) IN ('CASH', 'REC')
AND gl.reference10 NOT LIKE 'MFAR%'
AND to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) = l_fv_balance_check.cash_receipt_id;
' --> DELETE FROM gl_interface -> ' || SQL%ROWCOUNT);
## For each receivable app id the record will be INserted.
*/
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,
' --> inside cursor Cur_mfar_rct_lines ');
' --> inserting into gl_interface ');
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,
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,
ussgl_transaction_code)
VALUES (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
J.category,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30,
J.ussgl);
' --> Inserting into GL INTERFACE for Receipts ' ||
' - Receivable Application id ==> ' || I.receivable_application_id );
## For each receivable app id the record will be INserted.
*/
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,
' --> Inside cursor Cur_mfar_rct_lines_cm ');
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,
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,
ussgl_transaction_code)
VALUES (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
J.category,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30,
J.ussgl);
' --> Inserting into GL INTERFACE for Receipts ' ||
' - Receivable Application id ==> ' || I.receivable_application_id );
UPDATE psa_mf_rct_dist_all pda
SET pda.posting_control_id = l_pst_ctrl_id
WHERE pda.receivable_application_id IN
(SELECT receivable_application_id FROM ar_receivable_applications ara
WHERE ara.posting_control_id = l_pst_ctrl_id);
' --> (PSA_MF_RCT_DIST_ALL) Posting control id updated for '
|| (SQL%ROWCOUNT) );
SELECT distinct adj.adjustment_id adjustment_id
FROM ar_adjustments adj
WHERE adj.posting_control_id = l_pst_ctrl_id
AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (adj.adjustment_id, 'ADJ', l_sob_id)
, 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE';
SELECT
adj.gl_date gl_date,
adj.doc_sequence_id doc_seqid,
adj.doc_sequence_value doc_num,
ct.invoice_currency_code currency,
DECODE(l.lookup_code, '1', psa_adj_dist.mf_adjustment_ccid,
'2', get_adj_ccid (p_adjustment_id),
'3', PSA_MFAR_UTILS.get_rec_ccid (null, adj.customer_trx_id),
'4', psa_trx_dist.mf_receivables_ccid) ccid,
get_entered_dr_adj (l.lookup_code, psa_adj_dist.amount) entered_dr,
get_entered_cr_adj (l.lookup_code, psa_adj_dist.amount) entered_cr,
get_entered_dr_adj (l.lookup_code, psa_adj_dist.amount) accounted_dr,
get_entered_cr_adj (l.lookup_code, psa_adj_dist.amount) accounted_cr,
adj.ussgl_transaction_code,
l_batch_prefix || TO_CHAR(l_pst_ctrl_id) ref1,
DECODE (l.lookup_code, '1', 'MFAR ',
'2', 'MFAR Reversal of ' ,
'3', 'MFAR Reversal of ' ,
'4', 'MFAR ' ) ||
DECODE( l_summary_flag,'Y',NULL,
DECODE( l.lookup_code,
'4', DECODE(sign(psa_adj_dist.amount), -1,
l_pre_adjcr_ar || DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR', l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general,
l_pre_adjdr_ar || DECODE(ctt.type, 'CB', l_class_cb, 'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR', l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general),
'3', DECODE(sign(psa_adj_dist.amount), -1,
l_pre_adjcr_ar || DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR', l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general,
l_pre_adjdr_ar || DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR', l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general),
'2', DECODE(sign(psa_adj_dist.amount), -1,
l_pre_adjdr_adj || DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR', l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general,
l_pre_adjcr_adj || DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR', l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general),
'1', DECODE(sign(psa_adj_dist.amount), -1,
l_pre_adjdr_adj || DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR', l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ct.trx_number|| l_post_general,
l_pre_adjcr_adj || DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR', l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ct.trx_number|| l_post_general))) ref10,
TO_CHAR(l_pst_ctrl_id) ref21,
TO_CHAR(psa_adj_dist.adjustment_id) ref22,
-- TO_CHAR(psa_adj_dist.cust_trx_line_gl_dist_id) ref23,
nvl(get_adj_ard_id(adj.adjustment_id),
to_char(psa_adj_dist.cust_trx_line_gl_dist_id) ) ref23,
ct.trx_number ref24,
adj.adjustment_number ref25,
ctt.type ref26,
ct.bill_to_customer_id ref27,
'ADJ' ref28,
DECODE(l.lookup_code, '1', 'ADJ_REC',
'2', 'ADJ_ADJ',
'3', 'ADJ_FINCHRG') ref29,
'PSA_ADJ_DIST' ref30
FROM ar_adjustments adj,
psa_mf_adj_dist_all psa_adj_dist,
psa_mf_trx_dist_all psa_trx_dist,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist ctlgd,
ra_cust_trx_types ctt,
psa_lookup_codes l
WHERE psa_adj_dist.adjustment_id = adj.adjustment_id
AND adj.adjustment_id = p_adjustment_id
AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_adj_dist.cust_trx_line_gl_dist_id
AND adj.customer_trx_id = ct.customer_trx_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND psa_adj_dist.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
AND l.lookup_type = 'PSA_CARTESIAN_JOIN'
-- AND l.lookup_code in ('1','2','3','4')
AND l.lookup_code in ('1','4')
-- AND nvl(psa_adj_dist.amount, 0) <> 0 -- Bug 3739491, commented this condition
AND adj.posting_control_id = l_pst_ctrl_id
AND nvl(psa_adj_dist.posting_control_id, -3) = -3;
DELETE FROM GL_INTERFACE GI
WHERE GI.user_je_source_name = 'Receivables'
AND GI.set_of_books_id = l_sob_id
AND GI.group_id = l_pst_ctrl_id
AND GI.reference28 = 'ADJ'
AND GI.reference29 IN ('ADJ_ADJ', 'ADJ_REC', 'ADJ_FINCHRG')
AND GI.reference10 NOT LIKE '%MFAR%'
AND GI.reference22 = to_char(I.adjustment_id);
## For each adjustments id the record will be inserted.
*/
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,
' --> adjustment id ==> ' || I.adjustment_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,
currency_code,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
ussgl_transaction_code,
reference1,
reference10,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30)
VALUES (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
l_adj_cat_name,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ussgl_transaction_code,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30);
' --> Inserting into GL INTERFACE for adjustment id ==> '
|| I.adjustment_id );
UPDATE psa_mf_adj_dist_all pada
SET pada.posting_control_id = l_pst_ctrl_id
WHERE pada.adjustment_id IN
(SELECT adjustment_id FROM ar_adjustments aa
WHERE aa.posting_control_id = l_pst_ctrl_id);
' --> (PSA_MF_ADJ_DIST_ALL) Posting control id updated for '
|| (SQL%ROWCOUNT));
SELECT chart_of_accounts_id FROM gl_sets_of_books
WHERE set_of_books_id = p_sob_id;
SELECT distinct reference22 FROM gl_interface
WHERE group_id = l_pst_ctrl_id
AND reference28 = 'CMAPP'
AND reference29 = 'CMAPP_REC'
AND reference30 = 'AR_RECEIVABLE_APPLICATIONS';
SELECT accounting_date gl_date,
subledger_doc_sequence_id doc_seqid,
subledger_doc_sequence_value doc_num,
currency_code currency,
code_combination_id ccid,
entered_dr entered_dr,
entered_cr entered_cr,
accounted_dr accounted_dr,
accounted_cr accounted_cr,
reference1 ref1,
'MFAR reversal for' || Substr(reference10,19) ref10,
reference21 ref21,
reference22 ref22,
reference23 ref23,
reference24 ref24,
reference25 ref25,
reference26 ref26,
reference27 ref27,
reference28 ref28,
reference29 ref29,
reference30 ref30
FROM gl_interface
WHERE group_id = l_pst_ctrl_id
AND reference22 = p_cust_trx_id
AND reference28 = 'CMAPP'
AND reference29 = 'CMAPP_REC'
AND reference30 = 'AR_RECEIVABLE_APPLICATIONS';
l_select VARCHAR2(3000);
l_select := ' SELECT segment' || l_acct_seg_num || ' Segment, SUM(accounted_dr) Debit, SUM(accounted_cr) Credit' ||
' FROM gl_interface ' ||
' WHERE reference22 = :1' ||
' AND reference30 = :2' ||
' GROUP BY segment' || l_acct_seg_num ||
' HAVING SUM(nvl(accounted_dr,0)) <> SUM(nvl(accounted_cr,0))' ;
psa_utils.debug_other_string(g_state_level,l_full_path,' l_select --> ' || l_select);
EXECUTE IMMEDIATE l_select BULK COLLECT INTO gl_int_dets USING C_cust_trx_dets.reference22, 'AR_RECEIVABLE_APPLICATIONS';
psa_utils.debug_other_string(g_state_level,l_full_path,' Inserting into gl_interface');
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,
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 (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
l_user_cm_cat_name,
C_int_dets.gl_date,
C_int_dets.doc_seqid,
C_int_dets.doc_num,
C_int_dets.currency,
C_int_dets.ccid,
C_int_dets.entered_cr, -- reversal so interchanged DB and CR.
C_int_dets.entered_dr,
C_int_dets.accounted_cr,
C_int_dets.accounted_dr,
C_int_dets.ref1,
C_int_dets.ref10,
C_int_dets.ref21,
C_int_dets.ref22,
C_int_dets.ref23,
C_int_dets.ref24,
C_int_dets.ref25,
C_int_dets.ref26,
C_int_dets.ref27,
C_int_dets.ref28,
C_int_dets.ref29,
C_int_dets.ref30);
psa_utils.debug_other_string(g_state_level,l_full_path,' Inserting into gl_interface --> ' || SQL%ROWCOUNT);
SELECT Message_text FROM Fnd_new_messages
WHERE language_code = USERENV('LANG')
AND message_name = p_message_name;
SELECT user_je_category_name FROM gl_je_categories
WHERE je_category_name = p_category_name ;
SELECT user_je_source_name FROM gl_je_sources
WHERE je_source_name = 'Receivables';
SELECT currency_code from gl_sets_of_books
WHERE Set_of_books_id = l_sob_id;
select psa_mf_error_log_s.nextval into l_run_num from dual;
UPDATE gl_interface gi
SET (
gi.segment1 , gi.segment2 , gi.segment3 , gi.segment4 , gi.segment5 , gi.segment6 ,
gi.segment7 , gi.segment8 , gi.segment9 , gi.segment10, gi.segment11, gi.segment12,
gi.segment13, gi.segment14, gi.segment15, gi.segment16, gi.segment17, gi.segment18,
gi.segment19, gi.segment20, gi.segment21, gi.segment22, gi.segment23, gi.segment24,
gi.segment25, gi.segment26, gi.segment27, gi.segment28, gi.segment29, gi.segment30) =
(SELECT
cc.segment1 , cc.segment2 , cc.segment3 , cc.segment4 , cc.segment5 , cc.segment6 ,
cc.segment7 , cc.segment8 , cc.segment9 , cc.segment10, cc.segment11, cc.segment12,
cc.segment13, cc.segment14, cc.segment15, cc.segment16, cc.segment17, cc.segment18,
cc.segment19, cc.segment20, cc.segment21, cc.segment22, cc.segment23, cc.segment24,
cc.segment25, cc.segment26, cc.segment27, cc.segment28, cc.segment29, cc.segment30
FROM gl_code_combinations cc
WHERE cc.code_combination_id = gi.code_combination_id)
WHERE gi.group_id = l_pst_ctrl_id
AND reference10 like '%MFAR%';
SELECT ard.code_combination_id adj_ccid
FROM ar_adjustments_all adj,
ar_distributions_all ard
WHERE adj.adjustment_id = c_adjustment_id
AND adj.adjustment_id = ard.source_id
AND ard.source_table = 'ADJ'
AND ard.source_type IN ('ADJ', 'FINCHRG');
SELECT ard.line_id adj_ard_id
FROM ar_adjustments_all adj,
ar_distributions_all ard
WHERE adj.adjustment_id = c_adjustment_id
AND adj.adjustment_id = ard.source_id
AND ard.source_table = 'ADJ'
AND ard.source_type IN ('ADJ', 'FINCHRG');
SELECT ard.line_id misc_ard_id
FROM ar_misc_cash_distributions_all mcd,
ar_distributions_all ard
WHERE mcd.misc_cash_distribution_id = c_misc_cash_dist_id
AND mcd.misc_cash_distribution_id = ard.source_id
AND ard.source_table = 'MCD';
CURSOR c_crh_parent IS SELECT status FROM ar_cash_receipt_history WHERE
cash_receipt_history_id = p_rev_crh_id;
SELECT distinct ra.receivable_application_id receivable_application_id
FROM ar_receivable_applications ra
WHERE ra.status = 'APP'
AND ra.posting_control_id = l_pst_ctrl_id
AND DECODE(PSA_MFAR_VAL_PKG.ar_mfar_validate_check
(ra.receivable_application_id, 'RCT', l_sob_id) , 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE';
SELECT
DECODE(to_number(l1.lookup_code), '4', ra.ussgl_transaction_code,
'8', ra.ussgl_transaction_code,
'12', ra.ussgl_transaction_code, NULL) ussgl,
DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL, l_trade_cat_name, l_ccurr_cat_name),
'CM', l_user_cm_cat_name ) category,
ra.gl_date gl_date,
DECODE (ra.application_type, 'CASH', cr.doc_sequence_id,
'CM', ctcm.doc_sequence_id) doc_seqid,
DECODE (ra.application_type, 'CASH', cr.doc_sequence_value,
'CM', ctcm.doc_sequence_value) doc_num,
cr.currency_code currency,
DECODE (to_NUMBER(l1.lookup_code), 1, psa_rct_dist.mf_cash_ccid,
2, DECODE(ra.application_type, 'CM', psa_mfar_utils.get_rec_ccid (ra.applied_customer_trx_id, ra.customer_trx_id), crh.account_code_combINation_id),
3, ar_trx_dist.code_combINation_id,
4, ar_trx_dist.code_combINation_id,
5, psa_rct_dist.discount_ccid,
6, ra.earned_discount_ccid,
7, ra.code_combINation_id,
8, psa_trx_dist.mf_receivables_ccid,
9, psa_rct_dist.ue_discount_ccid,
10, ra.unearned_discount_ccid,
11, ra.code_combINation_id,
12, psa_trx_dist.mf_receivables_ccid) ccid,
DECODE (ra.application_type, 'CM', get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount),
get_entered_dr_rct (l1.lookup_code, psa_rct_dist.amount,
psa_rct_dist.discount_amount,
psa_rct_dist.ue_discount_amount)) entered_dr,
DECODE (ra.application_type, 'CM', get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount),
get_entered_cr_rct (l1.lookup_code, psa_rct_dist.amount,
psa_rct_dist.discount_amount,
psa_rct_dist.ue_discount_amount)) entered_cr,
DECODE (ra.application_type, 'CM', get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount),
get_entered_dr_rct (l1.lookup_code, psa_rct_dist.amount,
psa_rct_dist.discount_amount,
psa_rct_dist.ue_discount_amount)) accounted_dr,
DECODE (ra.application_type, 'CM', get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount),
get_entered_cr_rct (l1.lookup_code, psa_rct_dist.amount,
psa_rct_dist.discount_amount,
psa_rct_dist.ue_discount_amount)) accounted_cr,
'AR ' || TO_CHAR(l_pst_ctrl_id) ref1,
SUBSTRB (DECODE (l1.lookup_code, '1', DECODE (ra.application_type, 'CM', 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || ' Receivable account for credit memo' || ctcm.trx_NUMBER || '.',
'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || DECODE(crh.status,'CLEARED', ' Cash Account for ', 'REMITTED', ' Remittance Account for ')),
'2', DECODE (ra.application_type, 'CM',
'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || ' Reversal of Receivable account for credit memo '|| ctcm.trx_NUMBER || '.',
'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || DECODE(crh.status, 'CLEARED', ' Reversal of Cash Account for ' , 'REMITTED', ' Reversal of Remittance Account for ')),
'3', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Reversal of Revenue Account for ',
'4', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Receipt ',
'5', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct earn-disc): ',
'6', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core earn-disc):' ,
'7', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):' ,
'8', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' ,
'9', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct unearn-disc):',
'10', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core unearn-disc):',
'11', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):',
'12', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' )
|| DECODE (l_summary_flag, 'Y', NULL, DECODE(ra.application_type,
/* Cash Receipt application */
'CASH', ' '|| cr.receipt_NUMBER || l_app_applied ||
DECODE(ctt.type, 'CB', l_class_cb,
'CM', l_class_cm,
'DEP', l_class_dep,
'DM', l_class_dm,
'GUAR',l_class_guar,
'INV', l_class_inv,NULL) || ' ' || ctinv.trx_NUMBER || l_post_general)),1,240) ref10,
TO_CHAR(l_pst_ctrl_id) ref21,
DECODE (ra.application_type, 'CASH',TO_CHAR(cr.cash_receipt_id), -- || 'C' || TO_CHAR(ra.receivable_application_id),
'CM', TO_CHAR(ra.receivable_application_id)) ref22,
psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID ref23,
DECODE (ra.application_type, 'CASH', cr.receipt_NUMBER,
'CM', ctcm.trx_NUMBER) ref24,
ctinv.trx_NUMBER ref25,
ctt.type ref26,
DECODE (ra.application_type, 'CASH', TO_CHAR(cr.pay_from_customer),
'CM', TO_CHAR(ctcm.bill_to_customer_id)) ref27,
DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL,'TRADE','CCURR'),
'CM', 'CMAPP') ref28,
DECODE(ra.application_type, 'CASH', DECODE (ra.amount_applied_from, NULL,'TRADE_' ||ar_trx_dist.account_class, 'CCURR_' ||ar_trx_dist.account_class),
'CM', 'CMAPP_'||ar_trx_dist.account_class) ref29,
DECODE(ra.application_type, 'CASH', 'PSA_RCT_DIST',
'CM', 'RA_CUST_TRX_LINE_GL_DIST') ref30
FROM
ar_receivable_applications ra,
ar_cash_receipts cr,
ra_customer_trx ctcm,
ra_customer_trx ctinv,
ra_cust_trx_types ctt,
ar_cash_receipt_history crh,
psa_mf_rct_dist_all psa_rct_dist,
psa_mf_trx_dist_all psa_trx_dist,
ra_cust_trx_line_gl_dist ar_trx_dist,
psa_lookup_codes l1
WHERE
psa_rct_dist.receivable_application_id = p_receivable_application_id
AND psa_rct_dist.ue_discount_ccid IS NULL
AND psa_rct_dist.receivable_application_id = ra.receivable_application_id
AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
AND ar_trx_dist.cust_trx_line_gl_dist_id = psa_trx_dist.cust_trx_line_gl_dist_id
AND ar_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
/* For MFAR we consider only thr APP rows */
AND ra.status = 'APP'
AND ra.cash_receipt_id = cr.cash_receipt_id(+)
AND ra.customer_trx_id = ctcm.customer_trx_id(+)
AND ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
AND ctinv.cust_trx_type_id = ctt.cust_trx_type_id(+)
AND ra.cash_receipt_id = crh.cash_receipt_id(+)
AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
AND l1.lookup_code IN ('1','4','5','7','8','9','12',
decode(l_rct_post_det_flag, 'N', -1, 2),
-- decode(l_rct_post_det_flag, 'N', -1, 3),
decode(l_rct_post_det_flag, 'N', -1, 6),
decode(l_rct_post_det_flag, 'N', -1, 10),
decode(l_rct_post_det_flag, 'N', -1, 11))
AND DECODE (ceil(to_NUMBER(l1.lookup_code)/4), 1, nvl(psa_rct_dist.amount,0),
2, nvl(psa_rct_dist.discount_amount,0),
3, nvl(psa_rct_dist.ue_discount_amount,0), 0) <> 0
AND l1.lookup_code <= DECODE(ra.application_type, 'CM', 2, l1.lookup_code)
AND ra.posting_control_id = l_pst_ctrl_id
AND nvl(psa_rct_dist.posting_control_id, -3) = -3
AND crh.status IN ('CLEARED','REMITTED')
AND crh.first_posted_record_flag = 'Y';
SELECT to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) cash_receipt_id,
sum(accounted_dr) sum_acctd_dr,
sum(accounted_cr) sum_acctd_cr
FROM gl_interface gl
WHERE gl.user_je_source_name = 'Receivables'
AND gl.set_of_books_id = c_sob_id
AND gl.group_id = c_group_id
AND substr(gl.reference29, 7) IN ('CASH', 'REC')
AND gl.reference10 NOT LIKE 'MFAR%'
AND to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) IN
(select cash_receipt_id from ar_receivable_applications where receivable_application_id = c_rcv_app_id)
GROUP BY to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1))
HAVING sum(accounted_dr) = sum(accounted_cr);
DELETE FROM gl_interface gl
WHERE gl.user_je_source_name = 'Receivables'
AND gl.set_of_books_id = l_sob_id
AND gl.group_id = l_pst_ctrl_id
AND substr(gl.reference29, 7) IN ('CASH', 'REC')
AND gl.reference10 NOT LIKE 'MFAR%'
AND to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) = l_fv_balance_check.cash_receipt_id;
DELETE FROM gl_interface gl
WHERE gl.user_je_source_name = 'Receivables'
AND gl.set_of_books_id = l_sob_id
AND gl.group_id = l_pst_ctrl_id
AND gl.reference29 = 'TRADE_APP_INV_GL_LINE'
AND gl.reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
AND gl.reference10 NOT LIKE '%MFAR%'
AND to_number(gl.reference22) =
(select cash_receipt_id
from ar_receivable_applications
where receivable_application_id = I.receivable_application_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,
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,
ussgl_transaction_code)
VALUES
(l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
J.category,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30,
J.ussgl);
UPDATE psa_mf_rct_dist_all pda
SET pda.posting_control_id = l_pst_ctrl_id
WHERE pda.receivable_application_id IN
(SELECT receivable_application_id
FROM ar_receivable_applications ara
WHERE ara.posting_control_id = l_pst_ctrl_id);
SELECT cash_receipt_history_id FROM ar_cash_receipt_history_all
WHERE posting_control_id = l_pst_ctrl_id
AND cash_receipt_history_id NOT IN
(SELECT cash_receipt_history_id FROM psa_misc_posting);
SELECT cr.cash_receipt_id FROM ar_cash_receipts_all cr, ar_cash_receipt_history_all crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
-- AND crh.status = 'CLEARED'
AND fnd_date.date_to_canonical (crh.gl_date) BETWEEN fnd_date.date_to_canonical (TO_DATE (l_gl_start_date ||' 00:00:00','YYYY/MM/DD HH24:MI:SS'))
AND fnd_date.date_to_canonical (TO_DATE (l_post_through_date ||' 00:00:00','YYYY/MM/DD HH24:MI:SS'));
SELECT
mfd.gl_date gl_date,
cr.doc_sequence_id doc_seqid,
cr.doc_sequence_value doc_num,
ard.currency_code currency,
decode(to_number(l1.lookup_code),
1, mfd.cash_ccid, 2, ard.code_combination_id)
ccid,
decode(to_number(l1.lookup_code), 1, mcd.amount, 2, Null) entered_dr,
decode(to_number(l1.lookup_code), 1, Null, 2, mcd.amount) entered_cr,
decode(to_number(l1.lookup_code), 1, mcd.acctd_amount, 2, Null) accounted_dr,
decode(to_number(l1.lookup_code), 1, Null, 2, mcd.acctd_amount) accounted_cr,
l_batch_prefix || TO_CHAR(l_pst_ctrl_id) ref1,
DECODE(to_number(l1.lookup_code),1, ('MFAR Misc. Receipt ' || cr.receipt_number),
2,('Receipt ' || cr.receipt_number||'(MFAR)')) ref10,
TO_CHAR (mcd.posting_control_id) ref21,
TO_CHAR (cr.cash_receipt_id) ref22,
TO_CHAR (ard.line_id) ref23,
cr.receipt_number ref24,
TO_CHAR (mcd.misc_cash_distribution_id) ref25,
NULL ref26,
'c1' ref27,
'MISC' ref28,
'MISC_' || ard.source_type ref29,
'PSA_MF_MISC_DIST_ALL' ref30
FROM
psa_mf_misc_dist_all mfd,
psa_lookup_codes l1,
ar_misc_cash_distributions_all mcd,
ar_distributions_all ard,
ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh
WHERE
l1.lookup_type = 'PSA_CARTESIAN_JOIN'
AND l1.lookup_code IN ('1','2')
AND mfd.misc_cash_distribution_id = mcd.misc_cash_distribution_id
AND fnd_date.date_to_canonical (mfd.gl_date) BETWEEN fnd_date.date_to_canonical (TO_DATE (l_gl_start_date ||' 00:00:00','YYYY/MM/DD HH24:MI:SS'))
AND fnd_date.date_to_canonical (TO_DATE (l_post_through_date ||' 00:00:00','YYYY/MM/DD HH24:MI:SS'))
AND nvl(mfd.posting_control_id,-3) = -3
AND crh.status = mfd.reference1
AND mcd.posting_control_id = l_pst_ctrl_id
AND mcd.set_of_books_id = l_sob_id
AND mcd.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.posting_control_id = l_pst_ctrl_id
AND ((crh.first_posted_record_flag = 'Y') OR (crh.current_record_flag = 'Y' AND crh.status = 'REVERSED'))
AND ard.source_table = 'CRH'
AND ard.source_id = crh.cash_receipt_history_id
AND (ard.amount_cr is null or ard.amount_cr > 0);
## Insert into GL_INTERFACE Select from psa_mf_misc_dist_all
*/
FOR J IN Cur_MFAR_mrct_lines
LOOP
/*
## For each misc_cash_distribution_id the record will be inserted.
*/
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,
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 (l_user_id,
trunc(sysdate),
l_status,
l_actual_flag,
l_pst_ctrl_id,
l_sob_id,
l_source,
l_misc_cat_name,
J.gl_date,
J.doc_seqid,
J.doc_num,
J.currency,
J.ccid,
J.entered_dr,
J.entered_cr,
J.accounted_dr,
J.accounted_cr,
J.ref1,
J.ref10,
J.ref21,
J.ref22,
J.ref23,
J.ref24,
J.ref25,
J.ref26,
J.ref27,
J.ref28,
J.ref29,
J.ref30);
## Insert a record into psa_misc_posting to keep track of
## each reversing record of AR_CASH_RECEIPT_HISTORY, that we insert into GL_INTERFACE
*/
FOR J IN c_crh_post
LOOP
INSERT INTO psa_misc_posting (cash_receipt_history_id, posting_control_id)
VALUES (J.cash_receipt_history_id, l_pst_ctrl_id);
UPDATE psa_mf_misc_dist_all
SET posting_control_id = l_pst_ctrl_id
WHERE misc_cash_distribution_id IN
(SELECT misc_cash_distribution_id FROM ar_misc_cash_distributions_all
WHERE posting_control_id = l_pst_ctrl_id);