[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Dummy constants for use in update and lock operations |
+--------------------------------------------------------*/
AR_TEXT_DUMMY CONSTANT VARCHAR2(20) := '~~!@#$*&^';
,SELECT_FLAG VARCHAR2(1)
,LEVEL_FLAG VARCHAR2(1)
,FROM_TO_FLAG VARCHAR2(1)
,EVENT_TYPE_CODE VARCHAR2(30)
,EVENT_CLASS_CODE VARCHAR2(30)
,ENTITY_CODE VARCHAR2(30)
,THIRD_PARTY_ID NUMBER(15)
,THIRD_PARTY_SITE_ID NUMBER(15)
,THIRD_PARTY_TYPE VARCHAR2(30)
,SOURCE_TYPE VARCHAR2(30)
,CM_APP_LINE_AMT NUMBER
,CM_APP_LINE_ACCTD_AMT NUMBER
,AMOUNT_APPLIED NUMBER
,ACCTD_AMOUNT_APPLIED_FROM NUMBER
,FROM_DIST_LINE_ID NUMBER(15)
,LINE_NUMBER NUMBER(15)
);
,SELECT_FLAG VARCHAR2(1)
,LEVEL_FLAG VARCHAR2(1)
,FROM_TO_FLAG VARCHAR2(1)
,CRH_STATUS VARCHAR2(30)
,APP_CRH_STATUS VARCHAR2(30)
--
,EVENT_TYPE_CODE VARCHAR2(30)
,EVENT_CLASS_CODE VARCHAR2(30)
,ENTITY_CODE VARCHAR2(30)
--
,THIRD_PARTY_ID NUMBER(15)
,THIRD_PARTY_SITE_ID NUMBER(15)
,THIRD_PARTY_TYPE VARCHAR2(30)
,SOURCE_TYPE VARCHAR2(30)
--
,RECP_AMOUNT NUMBER
,RECP_ACCTD_AMOUNT NUMBER
,CRH_AMOUNT NUMBER
,CRH_ACCTD_AMOUNT NUMBER
,CRH_RECORD_ID NUMBER(15)
,ALLOC_AMOUNT NUMBER
,ALLOC_ACCTD_AMOUNT NUMBER
,MAX_LINE_ID NUMBER(15)
,MFAR_UPG_TRX VARCHAR2(1)
,LINE_NUMBER NUMBER(15)
);
,SELECT_FLAG VARCHAR2(1)
,LEVEL_FLAG VARCHAR2(1)
,FROM_TO_FLAG VARCHAR2(1)
,FROM_AMOUNT NUMBER
,AMOUNT NUMBER
,FROM_ACCTD_AMOUNT NUMBER
,EVENT_TYPE_CODE VARCHAR2(30)
,EVENT_CLASS_CODE VARCHAR2(30)
,ENTITY_CODE VARCHAR2(30)
,MFAR_ADDITIONAL_ENTRY VARCHAR2(30)
,REF_MF_DIST_FLAG VARCHAR2(1)
);
PROCEDURE mfar_insert_crh_extract(p_crh_mfar_extract_record IN crh_mfar_extract_record_type);
PROCEDURE mfar_cm_app_insert_extract(p_ar_cm_from_rec IN ar_cm_from_tab, p_ar_cm_to_rec IN OUT NOCOPY ar_cm_to_tab);
PROCEDURE mfar_cmapp_trx_update(p_cmapp_curr_round IN cmapp_curr_round, p_call VARCHAR2);
SELECT CASE WHEN (NVL(a.amount_cr,0) - NVL(a.amount_dr,0)) < 0 THEN
ABS(b.from_amount) ELSE NULL END
,CASE WHEN (NVL(a.amount_cr,0) - NVL(a.amount_dr,0)) > 0 THEN
ABS(b.from_amount) ELSE NULL END
,CASE WHEN (NVL(a.acctd_amount_cr,0) - NVL(a.acctd_amount_dr,0)) < 0 THEN
ABS(b.from_acctd_amount) ELSE NULL END
,CASE WHEN (NVL(a.acctd_amount_cr,0) - NVL(a.acctd_amount_dr,0)) > 0 THEN
ABS(b.from_acctd_amount) ELSE NULL END
,b.activity_bucket
,b.line_id
FROM ar_line_app_detail_gt b,
ar_distributions_all a
WHERE b.line_id = a.line_id;
UPDATE ar_distributions_all a
SET a.from_amount_dr = l_from_amount_dr(i),
a.from_amount_cr = l_from_amount_cr(i),
a.from_acctd_amount_dr = l_from_acctd_amount_dr(i),
a.from_acctd_amount_cr = l_from_acctd_amount_cr(i),
a.activity_bucket = l_activity_bucket(i)
WHERE a.line_id = l_line_id(i);
SELECT taxable_amount, --amount_applied_from
taxable_acctd_amount, --acctd_amount_applied_from
base_currency, --sob_currency
det_id, --cash_receipt_id
group_id, --customer_trx_id CM
gt_id, --receivable_application_id used also as gt_id
to_currency --invoice currency
FROM ar_line_app_detail_gt
GROUP BY
(taxable_amount, --amount_applied_from
taxable_acctd_amount, --acctd_amount_applied_from
base_currency, --sob_currency
det_id, --cash_receipt_id
group_id, --customer_trx_id CM
gt_id, --receivable_application_id used also as gt_id
to_currency); --invoice currency
INSERT INTO ar_line_app_detail_gt
(gt_id, --receivable_application_id
source_data_key1, --application_type CASH CM
det_id, --cash_receipt_id CASH
group_id, --customer_trx_id CM
ref_customer_trx_id, --applied_customer_trx_id INV
line_id, --line_id
amount,
acctd_amount,
TO_CURRENCY, --CURRENCY_CODE
TAXABLE_AMOUNT, --from_total_applied
TAXABLE_ACCTD_AMOUNT, --from_total_accted_applied
base_currency, --sob_currency
activity_bucket,
from_amount,
from_acctd_amount)
SELECT /*+LEADING(eve) USE_NL(eve,app)*/
app.RECEIVABLE_APPLICATION_ID,
app.APPLICATION_TYPE ,
app.CASH_RECEIPT_ID ,
app.CUSTOMER_TRX_ID ,
app.APPLIED_CUSTOMER_TRX_ID ,
dist.LINE_ID ,
NVL(dist.AMOUNT_CR,0)-NVL(dist.AMOUNT_DR,0),
NVL(dist.ACCTD_AMOUNT_CR,0)-NVL(dist.ACCTD_AMOUNT_DR,0),
dist.CURRENCY_CODE ,
NVL(app.AMOUNT_APPLIED_FROM ,app.AMOUNT_APPLIED),
app.ACCTD_AMOUNT_APPLIED_FROM,
sob.currency_code ,
DECODE(dist.source_type,'EDISC' ,'ED_LINE' ,
'UNEDISC','UNED_LINE',
'REC' ,'APP_LINE' , NULL),
NVL(dist.AMOUNT_CR,0)-NVL(dist.AMOUNT_DR,0),
NVL(dist.ACCTD_AMOUNT_CR,0)-NVL(dist.ACCTD_AMOUNT_DR,0)
FROM xla_events_gt eve,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_ledgers sob
WHERE eve.application_id = 222
AND eve.entity_code IN ('RECEIPTS','TRANSACTIONS')
AND eve.event_id = app.event_id
AND app.status IN ('APP','ACTIVITY')
AND app.posting_control_id = -3
AND NVL(app.postable,'Y') ='Y'
AND NVL(app.confirmed_flag,'Y')='Y'
AND app.upgrade_method IS NULL
AND app.receivable_application_id = dist.source_id
AND dist.source_table = 'RA'
-- This is not required the app status should suffice
-- AND dist.source_type IN ('REC','EDISC','UNEDISC','DEFERRED_TAX','TAX','ACTIVITY','SHORT_TERM_DEBT')
AND app.set_of_books_id = sob.ledger_id;
arp_det_dist_pkg.update_from_gt
(p_from_amt => l_amount_applied_from,
p_from_acctd_amt => l_acctd_amount_applied_from,
p_ae_sys_rec => l_ae_sys_rec,
p_app_rec => l_app_rec,
p_gt_id => l_receivable_application_id,
p_inv_currency => l_to_currency);
UPDATE ar_receivable_applications_all
SET upgrade_method = '11I_R12_POST'
WHERE receivable_application_id IN
(SELECT gt_id
FROM ar_line_app_detail_gt
GROUP BY gt_id);
SELECT /*+LEADING(gt) USE_NL(gt,app)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.source_type,
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
NVL(crh.exchange_rate_type,cr.exchange_rate_type), -- EXCHANGE_RATE_TYPE
NVL(crh.EXCHANGE_RATE,cr.exchange_rate) , -- EXCHANGE_RATE
NVL(crh.EXCHANGE_DATE,cr.exchange_date) , -- EXCHANGE_DATE
--
NVL(dist.acctd_amount_cr,0)
- NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0)
- NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
app.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_ID
'APP', -- POSTING_ENTITY
app.amount_applied,
app.amount_applied_from,
app.acctd_amount_applied_from,
cr.cash_receipt_id, -- CASH_RECEIPT_ID
NULL, -- CUSTOMER_TRX_ID
NULL, -- CUSTOMER_TRX_LINE_ID
NULL, -- CUST_TRX_LINE_GL_DIST_ID
NULL, -- CUST_TRX_LINE_SALESREP_ID
NULL, -- INVENTORY_ITEM_ID
NULL, -- SALES_TAX_ID
osp.master_organization_id, -- SO_ORGANIZATION_ID
NULL, -- TAX_EXEMPTION_ID
NULL, -- UOM_CODE
NULL, -- WAREHOUSE_ID
NULL, -- AGREEMENT_ID
cr.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
NULL, -- DRAWEE_BANK_ACCOUNT_ID
cr.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
cr.distribution_set_id, -- DISTRIBUTION_SET_ID
NULL, -- PAYMENT_SCHEDULE_ID
cr.receipt_method_id, -- RECEIPT_METHOD_ID
cr.receivables_trx_id, -- RECEIVABLES_TRX_ID
-- arp_xla_extract_main_pkg.ed_uned_trx('EDISC',app.org_id), -- ED_ADJ_RECEIVABLES_TRX_ID
-- arp_xla_extract_main_pkg.ed_uned_trx('UNEDISC',app.org_id), -- UNED_RECEIVABLES_TRX_ID
-- ED and UNED activity id should only be available on the to doc in application
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
cr.set_of_books_id, -- SET_OF_BOOKS_ID
NULL, -- SALESREP_ID
cr.customer_site_use_id, -- BILL_SITE_USE_ID
NULL, -- DRAWEE_SITE_USE_ID
cr.customer_site_use_id, -- PAYING_SITE_USE_ID -- synch with PAYING_CUSTOMER_ID
NULL, -- SOLD_SITE_USE_ID
NULL, -- SHIP_SITE_USE_ID
cr.customer_site_use_id, -- RECEIPT_CUSTOMER_SITE_USE_ID
NULL, -- BILL_CUST_ROLE_ID
NULL, -- DRAWEE_CUST_ROLE_ID
NULL, -- SHIP_CUST_ROLE_ID
NULL, -- SOLD_CUST_ROLE_ID
NULL, -- BILL_CUSTOMER_ID
NULL, -- DRAWEE_CUSTOMER_ID
cr.pay_from_customer, -- PAYING_CUSTOMER_ID
NULL, -- SOLD_CUSTOMER_ID
NULL, -- SHIP_CUSTOMER_ID
NULL, -- REMIT_ADDRESS_ID
cr.SELECTED_REMITTANCE_BATCH_ID, -- RECEIPT_BATCH_ID
app.receivable_application_id, -- RECEIVABLE_APPLICATION_ID
cr.customer_bank_branch_id, -- CUSTOMER_BANK_BRANCH_ID
cr.issuer_bank_branch_id, -- ISSUER_BANK_BRANCH_ID
NULL, -- BATCH_SOURCE_ID
NULL, -- BATCH_ID
NULL, -- TERM_ID
'Y', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'F', -- FROM_TO_FLAG
--BUG#5201086
-- NVL(dist.from_amount_cr,0)
-- -NVL(dist.from_amount_dr,0), -- FROM_AMOUNT,
CASE WHEN (app.upgrade_method IS NULL AND app.status ='APP') THEN
CASE WHEN (dist.from_amount_dr IS NOT NULL OR dist.from_amount_cr IS NOT NULL) THEN
NVL(dist.from_amount_cr,0)-NVL(dist.from_amount_dr,0)
ELSE
CASE WHEN (dist.source_type NOT IN ('REC','EDISC','UNEDISC')) THEN
NULL
ELSE
CASE WHEN (app.earned_discount_taken IS NOT NULL AND
app.earned_discount_taken = NVL(dist.amount_dr,0)-NVL(dist.amount_cr,0) AND
app.acctd_earned_discount_taken = NVL(dist.acctd_amount_dr,0)-NVL(dist.acctd_amount_cr,0)
AND dist.source_type = 'REC') THEN
NULL
ELSE
CASE WHEN (trx.invoice_currency_code = cr.currency_code) THEN
NVL(dist.amount_cr,0)-NVL(dist.amount_dr,0)
ELSE
CASE WHEN (app.amount_applied <> 0 AND app.amount_applied_from <> 0) THEN
NVL(app.amount_applied_from / app.amount_applied * dist.amount_cr,0)-
NVL(app.amount_applied_from / app.amount_applied * dist.amount_dr,0)
ELSE NULL END
END
END
END
END
ELSE
NVL(dist.from_amount_cr,0)
-NVL(dist.from_amount_dr,0)
END, -- FROM_AMOUNT
NVL(dist.amount_cr,0)
-NVL(dist.amount_dr,0), -- AMOUNT
--BUG#5201086
-- NVL(dist.from_acctd_amount_cr,0)
-- -NVL(dist.from_acctd_amount_dr,0), -- FROM_ACCTD_AMOUNT
CASE WHEN (app.upgrade_method IS NULL AND app.status ='APP') THEN
CASE WHEN (dist.from_acctd_amount_dr IS NOT NULL OR dist.from_acctd_amount_cr IS NOT NULL) THEN
NVL(dist.from_acctd_amount_cr,0)-NVL(dist.from_acctd_amount_dr,0)
ELSE
CASE WHEN (dist.source_type NOT IN ('REC','EDISC','UNEDISC')) THEN
NULL
ELSE
CASE WHEN (app.earned_discount_taken IS NOT NULL AND
app.earned_discount_taken = NVL(dist.amount_dr,0)-NVL(dist.amount_cr,0) AND
app.acctd_earned_discount_taken = NVL(dist.acctd_amount_dr,0)-NVL(dist.acctd_amount_cr,0)
AND dist.source_type = 'REC') THEN
NULL
ELSE
CASE WHEN (trx.invoice_currency_code = sob.currency_code AND
cr.currency_code = sob.currency_code ) THEN
NVL(dist.acctd_amount_cr,0)-NVL(dist.acctd_amount_dr,0)
ELSE
CASE WHEN (app.acctd_amount_applied_to <> 0 AND app.acctd_amount_applied_from <> 0) THEN
NVL(app.acctd_amount_applied_from / app.acctd_amount_applied_to * dist.acctd_amount_cr,0)-
NVL(app.acctd_amount_applied_from / app.acctd_amount_applied_to * dist.acctd_amount_dr,0)
ELSE NULL END
END
END
END
END
ELSE
NVL(dist.from_acctd_amount_cr,0)
-NVL(dist.from_acctd_amount_dr,0)
END, -- FROM_ACCTD_AMOUNT
--{BUG#4356088
gt.event_type_code
,gt.event_class_code
,gt.entity_code
,'N' --MFAR_ADDITIONAL_ENTRY
,nvl(dist.ref_mf_dist_flag,'N') --REF_MF_DIST_FLAG
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ar_cash_receipts_all cr,
--BUG#5201086
ar_cash_receipt_history_all crh,
ra_customer_trx_all trx
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ) --,'RECP_REVERSE') uptake XLA transaction reversal
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND dist.source_table = 'RA' -- Don't need this join due to ar_app_dist_upg_v
AND dist.source_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND DECODE(app.acctd_amount_applied_to,0,DECODE(app.acctd_amount_applied_from,0,'N','Y'),'N') = 'Y'
--
-- BUG#5366837
-- R12_11ICASH_POST is reserved for Upgraded 11i Cash basis not posted applications
-- We are not passing Cash basis at From Line level
-- the data for Cash Basis accounting upgraded will be at the To line level only
--
-- AND NVL(app.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST')
--
-- Need to incorporate PSA upgrade
--
AND DECODE(app.upgrade_method,
'R12_11ICASH_POST','N',
'11I_MFAR_UPG' ,DECODE(dist.source_table_secondary,'UPMFRAMIAR','Y','N'),
'Y') = 'Y'
AND app.org_id = osp.org_id(+)
AND app.cash_receipt_id = cr.cash_receipt_id
AND app.cash_receipt_history_id = crh.cash_receipt_history_id(+)
AND app.applied_customer_trx_id = trx.customer_trx_id(+)
AND dist.source_type IN ('REC'
,'OTHER ACC','ACC','BANK_CHARGES','ACTIVITY','FACTOR','REMITTANCE',
'TAX','DEFERRED_TAX','UNEDISC','EDISC','CURR_ROUND','SHORT_TERM_DEBT',
'EXCH_LOSS','EXCH_GAIN','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX');
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,CRH_STATUS
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--MFAR
,MFAR_ADDITIONAL_ENTRY
)
-- FROM document type Cash Receipt
SELECT /*+LEADING(gt) USE_NL(gt,app)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
NVL(crh.exchange_rate_type,cr.exchange_rate_type), -- EXCHANGE_RATE_TYPE
NVL(crh.EXCHANGE_RATE,cr.exchange_rate) , -- EXCHANGE_RATE
NVL(crh.EXCHANGE_DATE,cr.exchange_date) , -- EXCHANGE_DATE
--
NVL(dist.acctd_amount_cr,0)
- NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0)
- NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
app.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_ID
'APP', -- POSTING_ENTITY
cr.cash_receipt_id, -- CASH_RECEIPT_ID
NULL, -- CUSTOMER_TRX_ID
NULL, -- CUSTOMER_TRX_LINE_ID
NULL, -- CUST_TRX_LINE_GL_DIST_ID
NULL, -- CUST_TRX_LINE_SALESREP_ID
NULL, -- INVENTORY_ITEM_ID
NULL, -- SALES_TAX_ID
osp.master_organization_id, -- SO_ORGANIZATION_ID
NULL, -- TAX_EXEMPTION_ID
NULL, -- UOM_CODE
NULL, -- WAREHOUSE_ID
NULL, -- AGREEMENT_ID
cr.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
NULL, -- DRAWEE_BANK_ACCOUNT_ID
cr.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
cr.distribution_set_id, -- DISTRIBUTION_SET_ID
NULL, -- PAYMENT_SCHEDULE_ID
cr.receipt_method_id, -- RECEIPT_METHOD_ID
cr.receivables_trx_id, -- RECEIVABLES_TRX_ID
-- arp_xla_extract_main_pkg.ed_uned_trx('EDISC',app.org_id), -- ED_ADJ_RECEIVABLES_TRX_ID
-- arp_xla_extract_main_pkg.ed_uned_trx('UNEDISC',app.org_id), -- UNED_RECEIVABLES_TRX_ID
-- ED and UNED activity id should only be available on the to doc in application
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
cr.set_of_books_id, -- SET_OF_BOOKS_ID
NULL, -- SALESREP_ID
cr.customer_site_use_id, -- BILL_SITE_USE_ID
NULL, -- DRAWEE_SITE_USE_ID
cr.customer_site_use_id, -- PAYING_SITE_USE_ID -- synch with PAYING_CUSTOMER_ID
NULL, -- SOLD_SITE_USE_ID
NULL, -- SHIP_SITE_USE_ID
cr.customer_site_use_id, -- RECEIPT_CUSTOMER_SITE_USE_ID
NULL, -- BILL_CUST_ROLE_ID
NULL, -- DRAWEE_CUST_ROLE_ID
NULL, -- SHIP_CUST_ROLE_ID
NULL, -- SOLD_CUST_ROLE_ID
NULL, -- BILL_CUSTOMER_ID
NULL, -- DRAWEE_CUSTOMER_ID
cr.pay_from_customer, -- PAYING_CUSTOMER_ID
NULL, -- SOLD_CUSTOMER_ID
NULL, -- SHIP_CUSTOMER_ID
NULL, -- REMIT_ADDRESS_ID
cr.SELECTED_REMITTANCE_BATCH_ID, -- RECEIPT_BATCH_ID
app.receivable_application_id, -- RECEIVABLE_APPLICATION_ID
cr.customer_bank_branch_id, -- CUSTOMER_BANK_BRANCH_ID
cr.issuer_bank_branch_id, -- ISSUER_BANK_BRANCH_ID
NULL, -- BATCH_SOURCE_ID
NULL, -- BATCH_ID
NULL, -- TERM_ID
'Y', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'F', -- FROM_TO_FLAG
decode(app.status, 'APP', NULL,
'UNAPP', NULL,
'UNID', NULL, app.status), -- CRH_STATUS
--BUG#5201086
-- NVL(dist.from_amount_cr,0)
-- -NVL(dist.from_amount_dr,0), -- FROM_AMOUNT,
CASE WHEN (app.upgrade_method IS NULL AND app.status ='APP') THEN
CASE WHEN (dist.from_amount_dr IS NOT NULL OR dist.from_amount_cr IS NOT NULL) THEN
NVL(dist.from_amount_cr,0)-NVL(dist.from_amount_dr,0)
ELSE
CASE WHEN (dist.source_type NOT IN ('REC','EDISC','UNEDISC')) THEN
NULL
ELSE
CASE WHEN (app.earned_discount_taken IS NOT NULL AND
app.earned_discount_taken = NVL(dist.amount_dr,0)-NVL(dist.amount_cr,0) AND
app.acctd_earned_discount_taken = NVL(dist.acctd_amount_dr,0)-NVL(dist.acctd_amount_cr,0)
AND dist.source_type = 'REC') THEN
NULL
ELSE
CASE WHEN (trx.invoice_currency_code = cr.currency_code) THEN
NVL(dist.amount_cr,0)-NVL(dist.amount_dr,0)
ELSE
CASE WHEN (app.amount_applied <> 0 AND app.amount_applied_from <> 0) THEN
NVL(app.amount_applied_from / app.amount_applied * dist.amount_cr,0)-
NVL(app.amount_applied_from / app.amount_applied * dist.amount_dr,0)
ELSE NULL END
END
END
END
END
ELSE
NVL(dist.from_amount_cr,0)
-NVL(dist.from_amount_dr,0)
END, -- FROM_AMOUNT
NVL(dist.amount_cr,0)
-NVL(dist.amount_dr,0), -- AMOUNT
--BUG#5201086
-- NVL(dist.from_acctd_amount_cr,0)
-- -NVL(dist.from_acctd_amount_dr,0), -- FROM_ACCTD_AMOUNT
CASE WHEN (app.upgrade_method IS NULL AND app.status ='APP') THEN
CASE WHEN (dist.from_acctd_amount_dr IS NOT NULL OR dist.from_acctd_amount_cr IS NOT NULL) THEN
NVL(dist.from_acctd_amount_cr,0)-NVL(dist.from_acctd_amount_dr,0)
ELSE
CASE WHEN (dist.source_type NOT IN ('REC','EDISC','UNEDISC')) THEN
NULL
ELSE
CASE WHEN (app.earned_discount_taken IS NOT NULL AND
app.earned_discount_taken = NVL(dist.amount_dr,0)-NVL(dist.amount_cr,0) AND
app.acctd_earned_discount_taken = NVL(dist.acctd_amount_dr,0)-NVL(dist.acctd_amount_cr,0)
AND dist.source_type = 'REC') THEN
NULL
ELSE
CASE WHEN (trx.invoice_currency_code = sob.currency_code AND
cr.currency_code = sob.currency_code ) THEN
NVL(dist.acctd_amount_cr,0)-NVL(dist.acctd_amount_dr,0)
ELSE
CASE WHEN (app.acctd_amount_applied_to <> 0 AND app.acctd_amount_applied_from <> 0) THEN
NVL(app.acctd_amount_applied_from / app.acctd_amount_applied_to * dist.acctd_amount_cr,0)-
NVL(app.acctd_amount_applied_from / app.acctd_amount_applied_to * dist.acctd_amount_dr,0)
ELSE NULL END
END
END
END
END
ELSE
NVL(dist.from_acctd_amount_cr,0)
-NVL(dist.from_acctd_amount_dr,0)
END, -- FROM_ACCTD_AMOUNT
--{BUG#4356088
gt.event_type_code
,gt.event_class_code
,gt.entity_code
,'N' --MFAR_ADDITIONAL_ENTRY
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ar_cash_receipts_all cr,
--BUG#5201086
ar_cash_receipt_history_all crh,
ra_customer_trx_all trx
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ) --,'RECP_REVERSE') uptake XLA transaction reversal
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND dist.source_table = 'RA' -- Don't need this join due to ar_app_dist_upg_v
AND dist.source_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND DECODE(app.acctd_amount_applied_to,0,DECODE(app.acctd_amount_applied_from,0,'N','Y'),'N') = 'N'
--
-- BUG#5366837
-- R12_11ICASH_POST is reserved for Upgraded 11i Cash basis not posted applications
-- We are not passing Cash basis at From Line level
-- the data for Cash Basis accounting upgraded will be at the To line level only
--
-- AND NVL(app.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST')
--
-- Need to incorporate PSA upgrade
--
AND DECODE(app.upgrade_method,
'R12_11ICASH_POST','N',
'11I_MFAR_UPG' ,DECODE(dist.source_table_secondary,'UPMFRAMIAR','Y','N'),
'Y') = 'Y'
AND app.org_id = osp.org_id(+)
AND app.cash_receipt_id = cr.cash_receipt_id
AND app.cash_receipt_history_id = crh.cash_receipt_history_id(+)
AND app.applied_customer_trx_id = trx.customer_trx_id(+)
AND dist.source_type IN ('REC'
,'OTHER ACC','ACC','BANK_CHARGES','ACTIVITY','FACTOR','REMITTANCE',
'TAX','DEFERRED_TAX','UNEDISC','EDISC','CURR_ROUND','SHORT_TERM_DEBT',
'EXCH_LOSS','EXCH_GAIN','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX');
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
,event_type_code
,event_class_code
,entity_code
,additional_char1
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt,app)*/
gt.event_id -- EVENT_ID
,dist.cash_basis_distribution_id -- LINE_NUMBER
,'' -- LANGUAGE
,sob.set_of_books_id -- LEDGER_ID
,dist.receivable_application_id_cash -- SOURCE_ID
,'RA' -- SOURCE_TABLE
,dist.cash_basis_distribution_id -- LINE_ID
,NULL -- TAX_CODE_ID
,NULL -- LOCATION_SEGMENT_ID
,sob.currency_code -- BASE_CURRENCY
,NVL(crh.exchange_rate_type,cr.exchange_rate_type) -- EXCHANGE_RATE_TYPE
,NVL(crh.EXCHANGE_RATE,cr.exchange_rate) -- EXCHANGE_RATE
,NVL(crh.EXCHANGE_DATE,cr.exchange_date) -- EXCHANGE_DATE
,dist.acctd_amount -- ACCTD_AMOUNT
,0 -- TAXABLE_ACCTD_AMOUNT
,app.org_id -- ORG_ID
,app.receivable_application_id -- HEADER_ID
,'APP' -- POSTING_ENTITY
,cr.cash_receipt_id -- CASH_RECEIPT_ID
,NULL -- CUSTOMER_TRX_ID
,NULL -- CUSTOMER_TRX_LINE_ID
,NULL -- CUST_TRX_LINE_GL_DIST_ID
,NULL -- CUST_TRX_LINE_SALESREP_ID
,NULL -- INVENTORY_ITEM_ID
,NULL -- SALES_TAX_ID
,osp.master_organization_id -- SO_ORGANIZATION_ID
,NULL -- TAX_EXEMPTION_ID
,NULL -- UOM_CODE
,NULL -- WAREHOUSE_ID
,NULL -- AGREEMENT_ID
,cr.customer_bank_account_id -- CUSTOMER_BANK_ACCT_ID
,NULL -- DRAWEE_BANK_ACCOUNT_ID
,cr.remit_bank_acct_use_id -- REMITTANCE_BANK_ACCT_ID
,cr.distribution_set_id -- DISTRIBUTION_SET_ID
,NULL -- PAYMENT_SCHEDULE_ID
,cr.receipt_method_id -- RECEIPT_METHOD_ID
,cr.receivables_trx_id -- RECEIVABLES_TRX_ID
,NULL -- ED_ADJ_RECEIVABLES_TRX_ID
,NULL -- UNED_RECEIVABLES_TRX_ID
,cr.set_of_books_id -- SET_OF_BOOKS_ID
,NULL -- SALESREP_ID
,cr.customer_site_use_id -- BILL_SITE_USE_ID
,NULL -- DRAWEE_SITE_USE_ID
,cr.customer_site_use_id -- PAYING_SITE_USE_ID -- synch with PAYING_CUSTOMER_ID
,NULL -- SOLD_SITE_USE_ID
,NULL -- SHIP_SITE_USE_ID
,cr.customer_site_use_id -- RECEIPT_CUSTOMER_SITE_USE_ID
,NULL -- BILL_CUST_ROLE_ID
,NULL -- DRAWEE_CUST_ROLE_ID
,NULL -- SHIP_CUST_ROLE_ID
,NULL -- SOLD_CUST_ROLE_ID
,NULL -- BILL_CUSTOMER_ID
,NULL -- DRAWEE_CUSTOMER_ID
,cr.pay_from_customer -- PAYING_CUSTOMER_ID
,NULL -- SOLD_CUSTOMER_ID
,NULL -- SHIP_CUSTOMER_ID
,NULL -- REMIT_ADDRESS_ID
,cr.SELECTED_REMITTANCE_BATCH_ID -- RECEIPT_BATCH_ID
,app.receivable_application_id -- RECEIVABLE_APPLICATION_ID
,cr.customer_bank_branch_id -- CUSTOMER_BANK_BRANCH_ID
,cr.issuer_bank_branch_id -- ISSUER_BANK_BRANCH_ID
,NULL -- BATCH_SOURCE_ID
,NULL -- BATCH_ID
,NULL -- TERM_ID
,'Y' -- SELECT_FLAG
,'L' -- LEVEL_FLAG
,'F' -- FROM_TO_FLAG
,dist.from_amount -- FROM_AMOUNT
,dist.amount -- AMOUNT
,dist.from_acctd_amount -- FROM_ACCTD_AMOUNT
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,app.upgrade_method
,'N' --MFAR_ADDITIONAL_ENTRY
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_cash_basis_dists_all dist,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ra_customer_trx_all trx
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST') -- Uptake XLA trx reversal ,'RECP_REVERSE')
AND gt.application_id = 222
AND gt.event_id = app.event_id
AND dist.receivable_application_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND app.upgrade_method = 'R12_11ICASH_POST'
AND app.org_id = osp.org_id(+)
AND app.cash_receipt_id = cr.cash_receipt_id
AND app.cash_receipt_history_id = crh.cash_receipt_history_id
AND app.applied_customer_trx_id = trx.customer_trx_id;
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
,event_type_code
,event_class_code
,entity_code
,tax_line_id
,additional_char1
,MFAR_ADDITIONAL_ENTRY
,source_type
,CM_APP_TO_TRX_LINE_ID
,BAL_SEG_VALUE
)
-- FROM document type CM
SELECT /*+LEADING(gt) USE_NL(gt,app)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
/* bug7311808 -vavenugo*/
NVL(DIST.CURRENCY_CONVERSION_TYPE,trxf.exchange_rate_type), -- EXCHANGE_RATE_TYPE
NVL(DIST.CURRENCY_CONVERSION_RATE,trxf.exchange_rate), -- EXCHANGE_RATE
NVL(DIST.CURRENCY_CONVERSION_DATE, nvl(trxf.exchange_date, DECODE(dist.REF_PREV_CUST_TRX_LINE_ID, null, trxf.trx_date,
nvl((select trxt.trx_date from ra_customer_trx_all trxt where trxt.customer_trx_id = trxf.previous_customer_trx_id),trxf.trx_date)))) , -- EXCHANGE_DATE
/* End bug7311808 -vavenugo */ /* if REF_PREV_CUST_TRX_LINE_ID is NULL, use CM exchange_date/trx_date. Otherwise use INV exchange_date/trx_date*/
NVL(dist.acctd_amount_cr,0) -
NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0) -
NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
app.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_TABLE_ID
'APP', -- POSTING_ENTITY
NULL, -- CASH_RECEIPT_ID
trxf.customer_trx_id, -- CUSTOMER_TRX_ID
tlf.customer_trx_line_id, -- CUSTOMER_TRX_LINE_ID
gldf.cust_trx_line_gl_dist_id, -- CUST_TRX_LINE_GL_DIST_ID
gldf.cust_trx_line_salesrep_id, -- CUST_TRX_LINE_SALESREP_ID
tlf.inventory_item_id, -- INVENTORY_ITEM_ID
tlf.sales_tax_id, -- SALES_TAX_ID
osp.master_organization_id, -- SO_ORGANIZATION_ID
tlf.tax_exemption_id, -- TAX_EXEMPTION_ID
tlf.uom_code, -- UOM_CODE
tlf.warehouse_id, -- WAREHOUSE_ID
trxf.agreement_id, -- AGREEMENT_ID
trxf.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
trxf.drawee_bank_account_id, -- DRAWEE_BANK_ACCOUNT_ID
trxf.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
NULL, -- DISTRIBUTION_SET_ID
psch.payment_schedule_id, -- PAYMENT_SCHEDULE_ID
trxf.receipt_method_id, -- RECEIPT_METHOD_ID
NULL, -- RECEIVABLES_TRX_ID
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
trxf.set_of_books_id, -- SET_OF_BOOKS_ID
trxf.primary_salesrep_id, -- SALESREP_ID
trxf.bill_to_site_use_id, -- BILL_SITE_USE_ID
trxf.drawee_site_use_id, -- DRAWEE_SITE_USE_ID
trxf.paying_site_use_id, -- PAYING_SITE_USE_ID
trxf.sold_to_site_use_id, -- SOLD_SITE_USE_ID
trxf.ship_to_site_use_id, -- SHIP_SITE_USE_ID
NULL, -- RECEIPT_CUSTOMER_SITE_USE_ID
trxf.bill_to_contact_id, -- BILL_CUST_ROLE_ID
trxf.drawee_contact_id, -- DRAWEE_CUST_ROLE_ID
trxf.ship_to_contact_id, -- SHIP_CUST_ROLE_ID
trxf.sold_to_contact_id, -- SOLD_CUST_ROLE_ID
trxf.bill_to_customer_id, -- BILL_CUSTOMER_ID
trxf.drawee_id, -- DRAWEE_CUSTOMER_ID
trxf.paying_customer_id, -- PAYING_CUSTOMER_ID
trxf.sold_to_customer_id, -- SOLD_CUSTOMER_ID
trxf.ship_to_customer_id, -- SHIP_CUSTOMER_ID
trxf.remit_to_address_id, -- REMIT_ADDRESS_ID
NULL, -- RECEIPT_BATCH_ID
NULL, -- RECEIVABLE_APPLICATION_ID
NULL, -- CUSTOMER_BANK_BRANCH_ID
NULL, -- ISSUER_BANK_BRANCH_ID
trxf.batch_source_id, -- BATCH_SOURCE_ID
trxf.batch_id, -- BATCH_ID
trxf.term_id, -- TERM_ID
'Y', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'F', -- FROM_TO_FLAG
CASE WHEN (dist.from_amount_cr IS NULL AND dist.from_amount_dr IS NULL) THEN
NVL(dist.amount_cr,0) - NVL(dist.amount_dr,0)
ELSE
NVL(dist.from_amount_cr,0) - NVL(dist.from_amount_dr,0)
END, -- FROM_AMOUNT
NVL(dist.amount_cr,0) - NVL(dist.amount_dr,0), -- AMOUNT
CASE WHEN (dist.from_acctd_amount_cr IS NULL AND dist.from_acctd_amount_dr IS NULL) THEN
NVL(dist.acctd_amount_cr,0) - NVL(dist.acctd_amount_dr,0)
ELSE
NVL(dist.from_acctd_amount_cr,0) - NVL(dist.from_acctd_amount_dr,0)
END, -- FROM_ACCTD_MOUNT
gt.event_type_code,
gt.event_class_code,
gt.entity_code,
tlf.tax_line_id, --tax_line_id
app.upgrade_method
,'N' --MFAR_ADDITIONAL_ENTRY
, dist.source_type
, dist.REF_PREV_CUST_TRX_LINE_ID -- 9860123
, the_segment_value(sob.chart_of_accounts_id, 'GL_BALANCING', dist.ref_dist_ccid)
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ra_customer_trx_all trxf,
ra_customer_trx_lines_all tlf,
ra_cust_trx_line_gl_dist_all gldf,
ar_payment_schedules_all psch
WHERE gt.event_type_code IN ('CM_CREATE','CM_UPDATE' ) --BUG#3419926
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND dist.source_table = 'RA'
AND dist.source_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND app.org_id = osp.org_id(+)
AND app.customer_trx_id = trxf.customer_trx_id
--
-- R12_11ICASH_POST is reserved for Upgraded 11i Cash basis not posted applications
-- We are not passing Cash basis at From Line level
-- the data for Cash Basis accounting upgraded will be at the To line level only
--
-- AND NVL(app.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST')
-- Need to incorporate PSA upgrade
AND DECODE(app.upgrade_method,
'R12_11ICASH_POST','N',
'11I_MFAR_UPG' ,DECODE(dist.source_table_secondary,'UPMFRAMIAR','Y','N'),
'Y') = 'Y'
-- AND trxf.customer_trx_id = tlf.customer_trx_id
-- AND trxf.customer_trx_id = gldf.customer_trx_id
AND dist.ref_customer_trx_line_id = tlf.customer_trx_line_id(+)
-- ?? application we only want line actually applied
AND dist.ref_cust_trx_line_gl_dist_id = gldf.cust_trx_line_gl_dist_id(+)
-- ?? application we only want line actually applied
AND trxf.customer_trx_id = psch.customer_trx_id
AND NVL(psch.terms_sequence_number,1) = 1
AND dist.source_type in ('REC','DEFERRED_TAX','TAX','CURR_ROUND') /* Bug 6119725 Start Changes */
AND (((dist.ref_cust_trx_line_gl_dist_id IS NOT NULL AND sign(dist.ref_cust_trx_line_gl_dist_id) = 1)
AND dist.ref_cust_trx_line_gl_dist_id NOT IN (SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all ctlgd
WHERE ctlgd.customer_trx_id = app.applied_customer_trx_id)) -- Restrict To rows of Invoice
OR (((dist.ref_cust_trx_line_gl_dist_id IS NULL OR sign(dist.ref_cust_trx_line_gl_dist_id) = -1)
AND ((sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0)))*-1 = sign(nvl(dist.amount_cr,0) * -1+nvl(dist.amount_dr,0)) AND dist.source_type = 'DEFERRED_TAX' )/* Bug 8269394 Changes */
OR ( sign((app.acctd_amount_applied_from+nvl(app.acctd_earned_discount_taken,0)+nvl(app.acctd_unearned_discount_taken,0)))*-1 = sign(nvl(dist.acctd_amount_dr,0) * -1+nvl(dist.acctd_amount_cr,0)) AND dist.source_type = 'CURR_ROUND')
OR
(( sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0)))*-1 = sign(nvl(dist.amount_dr,0) * -1+nvl(dist.amount_cr,0)) AND dist.source_type not in ('DEFERRED_TAX','CURR_ROUND'))
/* Bug 8269394 Changes */
AND (((sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0)))*-1) <> 0)
OR
((sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0)))*-1 = 0)
AND dist.amount_cr is not null)))))));
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
,event_type_code
,event_class_code
,entity_code
,tax_line_id
,additional_char1
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt,app)*/
gt.event_id -- EVENT_ID
,dist.cash_basis_distribution_id -- LINE_NUMBER
,'' -- LANGUAGE
,sob.set_of_books_id -- LEDGER_ID
,dist.source_id -- SOURCE_ID
,'RA' -- SOURCE_TABLE
,dist.cash_basis_distribution_id -- LINE_ID
,NULL -- TAX_CODE_ID
,NULL -- LOCATION_SEGMENT_ID
,sob.currency_code -- BASE_CURRENCY
,trxf.exchange_rate_type -- EXCHANGE_RATE_TYPE
,trxf.exchange_rate -- EXCHANGE_RATE
,trxf.exchange_date -- EXCHANGE_DATE
,dist.acctd_amount -- ACCTD_AMOUNT
,0 -- TAXABLE_ACCTD_AMOUNT
,app.org_id -- ORG_ID
,app.receivable_application_id -- HEADER_TABLE_ID
,'APP' -- POSTING_ENTITY
,NULL -- CASH_RECEIPT_ID
,trxf.customer_trx_id -- CUSTOMER_TRX_ID
,tlf.customer_trx_line_id -- CUSTOMER_TRX_LINE_ID
,gldf.cust_trx_line_gl_dist_id -- CUST_TRX_LINE_GL_DIST_ID
,gldf.cust_trx_line_salesrep_id -- CUST_TRX_LINE_SALESREP_ID
,tlf.inventory_item_id -- INVENTORY_ITEM_ID
,tlf.sales_tax_id -- SALES_TAX_ID
,osp.master_organization_id -- SO_ORGANIZATION_ID
,tlf.tax_exemption_id -- TAX_EXEMPTION_ID
,tlf.uom_code -- UOM_CODE
,tlf.warehouse_id -- WAREHOUSE_ID
,trxf.agreement_id -- AGREEMENT_ID
,trxf.customer_bank_account_id -- CUSTOMER_BANK_ACCT_ID
,trxf.drawee_bank_account_id -- DRAWEE_BANK_ACCOUNT_ID
,trxf.remit_bank_acct_use_id -- REMITTANCE_BANK_ACCT_ID
,NULL -- DISTRIBUTION_SET_ID
,psch.payment_schedule_id -- PAYMENT_SCHEDULE_ID
,trxf.receipt_method_id -- RECEIPT_METHOD_ID
,NULL -- RECEIVABLES_TRX_ID
,NULL -- ED_ADJ_RECEIVABLES_TRX_ID
,NULL -- UNED_RECEIVABLES_TRX_ID
,trxf.set_of_books_id -- SET_OF_BOOKS_ID
,trxf.primary_salesrep_id -- SALESREP_ID
,trxf.bill_to_site_use_id -- BILL_SITE_USE_ID
,trxf.drawee_site_use_id -- DRAWEE_SITE_USE_ID
,trxf.paying_site_use_id -- PAYING_SITE_USE_ID
,trxf.sold_to_site_use_id -- SOLD_SITE_USE_ID
,trxf.ship_to_site_use_id -- SHIP_SITE_USE_ID
,NULL -- RECEIPT_CUSTOMER_SITE_USE_ID
,trxf.bill_to_contact_id -- BILL_CUST_ROLE_ID
,trxf.drawee_contact_id -- DRAWEE_CUST_ROLE_ID
,trxf.ship_to_contact_id -- SHIP_CUST_ROLE_ID
,trxf.sold_to_contact_id -- SOLD_CUST_ROLE_ID
,trxf.bill_to_customer_id -- BILL_CUSTOMER_ID
,trxf.drawee_id -- DRAWEE_CUSTOMER_ID
,trxf.paying_customer_id -- PAYING_CUSTOMER_ID
,trxf.sold_to_customer_id -- SOLD_CUSTOMER_ID
,trxf.ship_to_customer_id -- SHIP_CUSTOMER_ID
,trxf.remit_to_address_id -- REMIT_ADDRESS_ID
,NULL -- RECEIPT_BATCH_ID
,NULL -- RECEIVABLE_APPLICATION_ID
,NULL -- CUSTOMER_BANK_BRANCH_ID
,NULL -- ISSUER_BANK_BRANCH_ID
,trxf.batch_source_id -- BATCH_SOURCE_ID
,trxf.batch_id -- BATCH_ID
,trxf.term_id -- TERM_ID
,'Y' -- SELECT_FLAG
,'L' -- LEVEL_FLAG
,'F' -- FROM_TO_FLAG
,dist.from_amount -- FROM_AMOUNT
,dist.amount -- AMOUNT
,dist.from_acctd_amount -- FROM_ACCTD_MOUNT
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,tlf.tax_line_id --tax_line_id
,app.upgrade_method
,'N' --MFAR_ADDITIONAL_ENTRY
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_cash_basis_dists_all dist,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ra_customer_trx_all trxf,
ra_customer_trx_lines_all tlf,
ra_cust_trx_line_gl_dist_all gldf,
ar_payment_schedules_all psch
WHERE gt.event_type_code IN ('CM_CREATE','CM_UPDATE' )
AND gt.application_id = 222
AND gt.event_id = app.event_id
AND dist.receivable_application_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND app.org_id = osp.org_id(+)
AND app.customer_trx_id = trxf.customer_trx_id
AND app.upgrade_method = 'R12_11ICASH_POST'
AND trxf.customer_trx_id = tlf.customer_trx_id
AND trxf.customer_trx_id = gldf.customer_trx_id
AND dist.ref_customer_trx_line_id = tlf.customer_trx_line_id(+)
AND dist.ref_cust_trx_line_gl_dist_id = gldf.cust_trx_line_gl_dist_id(+)
AND trxf.customer_trx_id = psch.customer_trx_id
AND NVL(psch.terms_sequence_number,1) = 1;
INSERT INTO AR_XLA_LINES_EXTRACT(
EVENT_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,PAIRED_CCID
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+ LEADING(gt,trx,ctlgd) USE_NL(gt,trx,ctlgd)*/
gt.event_id -- EVENT_ID
,'' -- SOURCE_ID
,'' -- SOURCE_TABLE
,'' -- LINE_ID
,'' -- TAX_CODE_ID
,'' -- LOCATION_SEGMENT_ID
,sob.currency_code -- BASE_CURRENCY_CODE
,trx.exchange_rate_type -- EXCHANGE_RATE_TYPE
,trx.exchange_rate -- EXCHANGE_RATE
,DECODE(trx.previous_customer_trx_id, NULL, trx.exchange_date, (select trx_date from ra_customer_trx_all where customer_trx_id = trx.previous_customer_trx_id) ) -- EXCHANGE_DATE
,'' -- ACCTD_AMOUNT
,'' -- TAXABLE_ACCTD_AMOUNT
,trx.org_id -- ORG_ID
,'' -- HEADER_TABLE_ID
,'CTLGD' -- POSTING_ENTITY
,'' -- CASH_RECEIPT_ID
,trx.customer_trx_id -- CUSTOMER_TRX_ID
,'' -- CUSTOMER_TRX_LINE_ID
,'' -- CUST_TRX_LINE_GL_DIST_ID
,'' -- CUST_TRX_LINE_SALESREP_ID
,'' -- INVENTORY_ITEM_ID
,'' -- SALES_TAX_ID
,'' -- SO_ORGANIZATION_ID
,'' -- TAX_EXEMPTION_ID
,'' -- UOM_CODE
,'' -- WAREHOUSE_ID
,trx.agreement_id -- AGREEMENT_ID
,trx.customer_bank_account_id -- CUSTOMER_BANK_ACCT_ID
,trx.drawee_bank_account_id -- DRAWEE_BANK_ACCOUNT_ID
,trx.remit_bank_acct_use_id -- REMITTANCE_BANK_ACCT_ID
,'' -- DISTRIBUTION_SET_ID
,psch.payment_schedule_id -- PAYMENT_SCHEDULE_ID
,trx.receipt_method_id -- RECEIPT_METHOD_ID
,'' -- RECEIVABLES_TRX_ID
,'' -- ED_ADJ_RECEIVABLES_TRX_ID
,'' -- UNED_RECEIVABLES_TRX_ID
,trx.set_of_books_id -- SET_OF_BOOKS_ID
,trx.primary_salesrep_id -- SALESREP_ID
,trx.bill_to_site_use_id -- BILL_SITE_USE_ID
,trx.drawee_site_use_id -- DRAWEE_SITE_USE_ID
,trx.paying_site_use_id -- PAYING_SITE_USE_ID
,trx.sold_to_site_use_id -- SOLD_SITE_USE_ID
,trx.ship_to_site_use_id -- SHIP_SITE_USE_ID
,'' -- RECEIPT_CUSTOMER_SITE_USE_ID
,trx.bill_to_contact_id -- BILL_CUST_ROLE_ID
,trx.drawee_contact_id -- DRAWEE_CUST_ROLE_ID
,trx.ship_to_contact_id -- SHIP_CUST_ROLE_ID
,trx.sold_to_contact_id -- SOLD_CUST_ROLE_ID
,trx.bill_to_customer_id -- BILL_CUSTOMER_ID
,trx.drawee_id -- DRAWEE_CUSTOMER_ID
,trx.paying_customer_id -- PAYING_CUSTOMER_ID
,trx.sold_to_customer_id -- SOLD_CUSTOMER_ID
,trx.ship_to_customer_id -- SHIP_CUSTOMER_ID
,trx.remit_to_address_id -- REMIT_ADDRESS_ID
,'' -- RECEIPT_BATCH_ID
,'' -- RECEIVABLE_APPLICATION_ID
,'' -- CUSTOMER_BANK_BRANCH_ID
,'' -- ISSUER_BANK_BRANCH_ID
,trx.batch_source_id -- BATCH_SOURCE_ID
,trx.batch_id -- BATCH_ID
,trx.term_id -- TERM_ID
,'Y' -- SELECT_FLAG
,'H' -- LEVEL_FLAG
,'' -- FROM_TO_FLAG
,ctlgd.code_combination_id -- paired_ccid
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,'N' --MFAR_ADDITIONAL_ENTRY
FROM ra_customer_trx_all trx,
gl_sets_of_books sob,
xla_events_gt gt,
ar_payment_schedules_all psch,
ra_cust_trx_line_gl_dist_all ctlgd
WHERE gt.event_type_code IN ('INV_CREATE' , 'INV_UPDATE' ,
'CM_CREATE' , 'CM_UPDATE' ,
'DM_CREATE' , 'DM_UPDATE' ,
'DEP_CREATE' , 'DEP_UPDATE' ,
'GUAR_CREATE' , 'GUAR_UPDATE' ,
'CB_CREATE' ) --BUG#3419926
AND gt.application_id = p_application_id
AND trx.customer_trx_id = gt.source_id_int_1
AND trx.set_of_books_id = sob.set_of_books_id
AND trx.customer_trx_id = ctlgd.customer_trx_id
AND trx.complete_flag = 'Y'
AND ctlgd.account_class = 'REC'
AND ctlgd.account_set_flag = 'N'
AND trx.customer_trx_id = psch.customer_trx_id(+)
AND NVL(psch.terms_sequence_number,1) = 1;
| data for header extract are already inserted |
| in the actract line table. The code in header |
| insertion is therefore not usefull. Unless it |
| a denormalised approach for header data for |
| performance reason. This needs to be evaluated.|
| For now, I commented the header table insertion|
| we might end up with removing the header table.|
+------------------------------------------------*/
/* to be removed at the end of the project*/
-- Load_header_data_ctlgd_h ;
INSERT INTO AR_XLA_LINES_EXTRACT(
EVENT_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,paired_ccid
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt, adj)*/
gt.event_id, -- EVENT_ID
'', -- SOURCE_ID
'', -- SOURCE_TABLE
'', -- LINE_ID
'', -- TAX_CODE_ID
'', -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY_CODE
trxf.exchange_rate_type, -- EXCHANGE_RATE_TYPE
trxf.exchange_rate, -- EXCHANGE_RATE
trxf.exchange_date, -- EXCHANGE_DATE
'', -- ACCTD_AMOUNT
'', -- TAXABLE_ACCTD_AMOUNT
adj.org_id, -- ORG_ID
adj.adjustment_id, -- HEADER_TABLE_ID
'ADJ', -- POSTING_ENTITY
'', -- CASH_RECEIPT_ID
adj.customer_trx_id, -- CUSTOMER_TRX_ID
'', -- CUSTOMER_TRX_LINE_ID
'', -- CUST_TRX_LINE_GL_DIST_ID
trxf.primary_salesrep_id, -- SALESREP_ID
'', -- INVENTORY_ITEM_ID
'', -- SALES_TAX_ID
'', -- SO_ORGANIZATION_ID
'', -- TAX_EXEMPTION_ID
'', -- UOM_CODE
'', -- WAREHOUSE_ID
trxf.agreement_id, -- AGREEMENT_ID
trxf.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
'', -- DRAWEE_BANK_ACCOUNT_ID
trxf.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
adj.distribution_set_id, -- DISTRIBUTION_SET_ID
adj.payment_schedule_id, -- PAYMENT_SCHEDULE_ID
trxf.receipt_method_id, -- RECEIPT_METHOD_ID
adj.receivables_trx_id, -- RECEIVABLES_TRX_ID
'', -- ED_ADJ_RECEIVABLES_TRX_ID
'', -- UNED_RECEIVABLES_TRX_ID
adj.set_of_books_id, -- SET_OF_BOOKS_ID
trxf.primary_salesrep_id, -- SALESREP_ID
trxf.bill_to_site_use_id, -- BILL_SITE_USE_ID
trxf.drawee_site_use_id, -- DRAWEE_SITE_USE_ID
trxf.paying_site_use_id, -- PAYING_SITE_USE_ID
trxf.sold_to_site_use_id, -- SOLD_SITE_USE_ID
trxf.ship_to_site_use_id, -- SHIP_SITE_USE_ID
'', -- RECEIPT_CUSTOMER_SITE_USE_ID
trxf.bill_to_contact_id, -- BILL_CUST_ROLE_ID
'', -- DRAWEE_CUST_ROLE_ID
trxf.ship_to_contact_id, -- SHIP_CUST_ROLE_ID
trxf.sold_to_contact_id, -- SOLD_CUST_ROLE_ID
trxf.bill_to_customer_id, -- BILL_CUSTOMER_ID
trxf.drawee_id, -- DRAWEE_CUSTOMER_ID
trxf.paying_customer_id, -- PAYING_CUSTOMER_ID
trxf.sold_to_customer_id, -- SOLD_CUSTOMER_ID
trxf.ship_to_customer_id, -- SHIP_CUSTOMER_ID
trxf.remit_to_address_id, -- REMIT_ADDRESS_ID
'', -- RECEIPT_BATCH_ID
'', -- RECEIVABLE_APPLICATION_ID
'', -- CUSTOMER_BANK_BRANCH_ID
'', -- ISSUER_BANK_BRANCH_ID
trxf.batch_source_id, -- BATCH_SOURCE_ID
trxf.batch_id, -- BATCH_ID
trxf.term_id, -- TERM_ID
'Y', -- select_flag
'H', -- level_flag
'', -- FROM_TO_FLAG
ctlgd.code_combination_id -- paired_ccid
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,'N' --MFAR_ADDITIONAL_ENTRY
FROM ar_adjustments_all adj,
gl_sets_of_books sob,
xla_events_gt gt,
ra_customer_trx_all trxf,
ra_cust_trx_line_gl_dist_all ctlgd
WHERE gt.event_type_code = 'ADJ_CREATE'
AND gt.application_id = p_application_id
AND adj.adjustment_id = gt.source_id_int_1
AND adj.set_of_books_id = sob.set_of_books_id
AND adj.customer_trx_id = trxf.customer_trx_id(+)
AND trxf.customer_trx_id = ctlgd.customer_trx_id
AND ctlgd.account_set_flag = 'N'
AND ctlgd.account_class = 'REC'
UNION ALL
SELECT /*+LEADING(gt) USE_NL(gt, adj)*/
gt.event_id, -- EVENT_ID
'', -- SOURCE_ID
'', -- SOURCE_TABLE
'', -- LINE_ID
'', -- TAX_CODE_ID
'', -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY_CODE
trxf.exchange_rate_type, -- EXCHANGE_RATE_TYPE
trxf.exchange_rate, -- EXCHANGE_RATE
trxf.exchange_date, -- EXCHANGE_DATE
'', -- ACCTD_AMOUNT
'', -- TAXABLE_ACCTD_AMOUNT
adj.org_id, -- ORG_ID
adj.adjustment_id, -- HEADER_TABLE_ID
'ADJ', -- POSTING_ENTITY
'', -- CASH_RECEIPT_ID
adj.customer_trx_id, -- CUSTOMER_TRX_ID
'', -- CUSTOMER_TRX_LINE_ID
'', -- CUST_TRX_LINE_GL_DIST_ID
trxf.primary_salesrep_id, -- SALESREP_ID
'', -- INVENTORY_ITEM_ID
'', -- SALES_TAX_ID
'', -- SO_ORGANIZATION_ID
'', -- TAX_EXEMPTION_ID
'', -- UOM_CODE
'', -- WAREHOUSE_ID
trxf.agreement_id, -- AGREEMENT_ID
trxf.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
'', -- DRAWEE_BANK_ACCOUNT_ID
trxf.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
adj.distribution_set_id, -- DISTRIBUTION_SET_ID
adj.payment_schedule_id, -- PAYMENT_SCHEDULE_ID
trxf.receipt_method_id, -- RECEIPT_METHOD_ID
adj.receivables_trx_id, -- RECEIVABLES_TRX_ID
'', -- ED_ADJ_RECEIVABLES_TRX_ID
'', -- UNED_RECEIVABLES_TRX_ID
adj.set_of_books_id, -- SET_OF_BOOKS_ID
trxf.primary_salesrep_id, -- SALESREP_ID
trxf.bill_to_site_use_id, -- BILL_SITE_USE_ID
trxf.drawee_site_use_id, -- DRAWEE_SITE_USE_ID
trxf.paying_site_use_id, -- PAYING_SITE_USE_ID
trxf.sold_to_site_use_id, -- SOLD_SITE_USE_ID
trxf.ship_to_site_use_id, -- SHIP_SITE_USE_ID
'', -- RECEIPT_CUSTOMER_SITE_USE_ID
trxf.bill_to_contact_id, -- BILL_CUST_ROLE_ID
'', -- DRAWEE_CUST_ROLE_ID
trxf.ship_to_contact_id, -- SHIP_CUST_ROLE_ID
trxf.sold_to_contact_id, -- SOLD_CUST_ROLE_ID
trxf.bill_to_customer_id, -- BILL_CUSTOMER_ID
trxf.drawee_id, -- DRAWEE_CUSTOMER_ID
trxf.paying_customer_id, -- PAYING_CUSTOMER_ID
trxf.sold_to_customer_id, -- SOLD_CUSTOMER_ID
trxf.ship_to_customer_id, -- SHIP_CUSTOMER_ID
trxf.remit_to_address_id, -- REMIT_ADDRESS_ID
'', -- RECEIPT_BATCH_ID
'', -- RECEIVABLE_APPLICATION_ID
'', -- CUSTOMER_BANK_BRANCH_ID
'', -- ISSUER_BANK_BRANCH_ID
trxf.batch_source_id, -- BATCH_SOURCE_ID
trxf.batch_id, -- BATCH_ID
trxf.term_id, -- TERM_ID
'Y', -- select_flag
'H', -- level_flag
'', -- FROM_TO_FLAG
ard.code_combination_id -- paired_ccid
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,'N' --MFAR_ADDITIONAL_ENTRY
FROM ar_adjustments_all adj,
gl_sets_of_books sob,
xla_events_gt gt,
ra_customer_trx_all trxf,
ar_transaction_history_all trh,
ar_distributions_all ard
WHERE gt.event_type_code = 'ADJ_CREATE'
AND gt.application_id = p_application_id
AND adj.adjustment_id = gt.source_id_int_1
AND adj.set_of_books_id = sob.set_of_books_id
AND adj.customer_trx_id = trxf.customer_trx_id(+)
AND trxf.customer_trx_id = trh.customer_trx_id
AND trh.current_accounted_flag = 'Y'
AND trh.postable_flag = 'Y'
AND ard.source_table = 'TH'
AND ard.source_id = trh.transaction_history_id
AND ard.source_type = 'REC'
AND ard.ref_customer_trx_line_id is null
AND ard.ref_cust_trx_line_gl_dist_id is null;
INSERT INTO AR_XLA_LINES_EXTRACT(
EVENT_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,CRH_STATUS
,CRH_PRV_STATUS
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--}
,reversal_code --Reversal at header should return 'Y' for RECP_REVERSAL
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt,cr)*/
gt.event_id, --EVENT_ID
'', --SOURCE_ID
'', --SOURCE_TABLE
'', --LINE_ID
'', --TAX_CODE_ID
'', --LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY_CODE
cr.exchange_rate_type,-- EXCHANGE_RATE_TYPE
cr.exchange_rate, -- EXCHANGE_RATE
cr.exchange_date, -- EXCHANGE_DATE
'', --ACCTD_AMOUNT
'', --TAXABLE_ACCTD_AMOUNT
cr.org_id, --ORG_ID
cr.cash_receipt_id, --HEADER_TABLE_ID
'CR', --POSTING_ENTITY
cr.cash_receipt_id, --CASH_RECEIPT_ID
'', --CUSTOMER_TRX_ID
'', --CUSTOMER_TRX_LINE_ID
'', --CUST_TRX_LINE_GL_DIST_ID
'', --CUST_TRX_LINE_SALESREP_ID
'', --INVENTORY_ITEM_ID
'', --SALES_TAX_ID
'', --SO_ORGANIZATION_ID
'', --TAX_EXEMPTION_ID
'', --UOM_CODE
'', --WAREHOUSE_ID
'', --AGREEMENT_ID
cr.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
'', -- DRAWEE_BANK_ACCOUNT_ID
cr.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
cr.distribution_set_id, -- DISTRIBUTION_SET_ID
'', -- PAYMENT_SCHEDULE_ID
cr.receipt_method_id, -- RECEIPT_METHOD_ID
cr.receivables_trx_id, -- RECEIVABLES_TRX_ID
'', -- ED_ADJ_RECEIVABLES_TRX_ID
'', -- UNED_RECEIVABLES_TRX_ID
cr.set_of_books_id, -- SET_OF_BOOKS_ID
'', -- SALESREP_ID
cr.customer_site_use_id, -- BILL_SITE_USE_ID
'', -- DRAWEE_SITE_USE_ID
cr.customer_site_use_id, -- PAYING_SITE_USE_ID -- HYU
'', -- SOLD_SITE_USE_ID
'', -- SHIP_SITE_USE_ID
cr.customer_site_use_id, -- RECEIPT_CUSTOMER_SITE_USE_ID
'', -- BILL_CUST_ROLE_ID
'', -- DRAWEE_CUST_ROLE_ID
'', -- SHIP_CUST_ROLE_ID
'', -- SOLD_CUST_ROLE_ID
'', -- BILL_CUSTOMER_ID
'', -- DRAWEE_CUSTOMER_ID
cr.pay_from_customer, -- PAYING_CUSTOMER_ID
'', -- SOLD_CUSTOMER_ID
'', -- SHIP_CUSTOMER_ID
'', -- REMIT_ADDRESS_ID
cr.SELECTED_REMITTANCE_BATCH_ID, -- RECEIPT_BATCH_ID
'', -- RECEIVABLE_APPLICATION_ID
cr.customer_bank_branch_id, -- CUSTOMER_BANK_BRANCH_ID
cr.issuer_bank_branch_id, -- ISSUER_BANK_BRANCH_ID
'', -- BATCH_SOURCE_ID
'', -- BATCH_ID
'', -- TERM_ID
'Y', -- SELECT_FLAG
'H', -- LEVEL_FLAG
'', -- FROM_TO_FLAG
--{BUG5332302
'', -- CRH_STATUS
'' -- CRH_PRV_STATUS
--}
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
--}
,DECODE(gt.event_type_code,'RECP_REVERSE','Y',
'MISC_RECP_REVERSE','Y','N') --reversal_code
,'N' --MFAR_ADDITIONAL_ENTRY
FROM xla_events_gt gt,
gl_sets_of_books sob,
ar_cash_receipts_all cr
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,--Header level view reversal stay
'MISC_RECP_CREATE' ,'MISC_RECP_UPDATE' ,
'MISC_RECP_RATE_ADJUST','MISC_RECP_REVERSE' )--BUG#3419926
AND gt.application_id = p_application_id
AND gt.source_id_int_1 = cr.cash_receipt_id
AND cr.set_of_books_id = sob.set_of_books_id;
INSERT INTO AR_XLA_LINES_EXTRACT(
EVENT_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,paired_ccid
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt,trx)*/
gt.event_id, --EVENT_ID
'', --SOURCE_ID
'', --SOURCE_TABLE
'', --LINE_ID
'', --TAX_CODE_ID
'', --LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY_CODE
trx.exchange_rate_type, -- EXCHANGE_RATE_TYPE
trx.exchange_rate, -- EXCHANGE_RATE
trx.exchange_date, -- EXCHANGE_DATE
'', --ACCTD_AMOUNT
'', --TAXABLE_ACCTD_AMOUNT
trx.org_id, -- ORG_ID
trx.customer_trx_id, --HEADER_TABLE_ID
'TH', --POSTING_ENTITY
'', -- CASH_RECEIPT_ID
trx.customer_trx_id, -- CUSTOMER_TRX_ID
'', --CUSTOMER_TRX_LINE_ID
'', --CUST_TRX_LINE_GL_DIST_ID
'', --CUST_TRX_LINE_SALESREP_ID
'', --INVENTORY_ITEM_ID
'', --SALES_TAX_ID
'', --SO_ORGANIZATION_ID
'', --TAX_EXEMPTION_ID
'', --UOM_CODE
'', --WAREHOUSE_ID
trx.agreement_id, -- AGREEMENT_ID
trx.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
trx.drawee_bank_account_id, -- DRAWEE_BANK_ACCOUNT_ID
'', -- DISTRIBUTION_SET_ID
'', -- PAYMENT_SCHEDULE_ID
trx.receipt_method_id, -- RECEIPT_METHOD_ID
trx.remit_bank_acct_use_id,-- REMITTANCE_BANK_ACCT_ID
'', -- RECEIVABLES_TRX_ID
'', -- ED_ADJ_RECEIVABLES_TRX_ID
'', -- UNED_RECEIVABLES_TRX_ID
trx.set_of_books_id, -- SET_OF_BOOKS_ID
trx.primary_salesrep_id, -- SALESREP_ID
trx.bill_to_site_use_id, -- BILL_SITE_USE_ID
trx.drawee_site_use_id, -- DRAWEE_SITE_USE_ID
trx.paying_site_use_id, -- PAYING_SITE_USE_ID
trx.sold_to_site_use_id, -- SOLD_SITE_USE_ID
trx.ship_to_site_use_id, -- SHIP_SITE_USE_ID
'', -- RECEIPT_CUSTOMER_SITE_USE_ID
trx.bill_to_contact_id, -- BILL_CUST_ROLE_ID
trx.drawee_contact_id, -- DRAWEE_CUST_ROLE_ID
trx.ship_to_contact_id, -- SHIP_CUST_ROLE_ID
trx.sold_to_contact_id, -- SOLD_CUST_ROLE_ID
trx.bill_to_customer_id, -- BILL_CUSTOMER_ID
trx.drawee_id, -- DRAWEE_CUSTOMER_ID
trx.paying_customer_id, -- PAYING_CUSTOMER_ID
trx.sold_to_customer_id, -- SOLD_CUSTOMER_ID
trx.ship_to_customer_id, -- SHIP_CUSTOMER_ID
trx.remit_to_address_id, -- REMIT_ADDRESS_ID
'', -- RECEIPT_BATCH_ID
'', -- RECEIVABLE_APPLICATION_ID
'', -- CUSTOMER_BANK_BRANCH_ID
'', -- ISSUER_BANK_BRANCH_ID
trx.batch_source_id, -- BATCH_SOURCE_ID
trx.batch_id, -- BATCH_ID
trx.term_id, -- TERM_ID
'Y', -- SELECT_FLAG
'H', -- LEVEL_FLAG
'', -- FROM_TO_FLAG
'' --BUG#5204032 ard.code_combination_id -- paired_ccid
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,'N' --MFAR_ADDITIONAL_ENTRY
--}
FROM xla_events_gt gt,
ra_customer_trx_all trx,
gl_sets_of_books sob
WHERE gt.event_type_code IN ('BILL_CREATE' ,'BILL_UPDATE' ,'BILL_REVERSE')
AND gt.application_id = p_application_id
AND gt.source_id_int_1 = trx.customer_trx_id
AND trx.set_of_books_id = sob.set_of_books_id;
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,PAIRED_CCID
,PAIRE_DIST_ID
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--BUG#4645389
,tax_line_id
--}
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt,gld)*/
gt.event_id, -- EVENT_ID
-1 * gld.cust_trx_line_gl_dist_id, --LINE_NUMBER
-- As in the case application are extracted along with the
-- transaction distributions the line number should be
-- unique without a event. For lines extracted from application
-- the line number is set by using the ard.line_id
-- and transaction by using the ctlgd.cust_trx_line_gl_dist_id
-- to avoid the same id to be extracted with in the same event
-- ids coming from ctlgd will be negative
'', --LANGUAGE
sob.set_of_books_id, --LEDGER_ID
'', -- SOURCE_ID
'', -- SOURCE_TABLE
'', -- LINE_ID
li.vat_tax_id, -- TAX_CODE_ID
li.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
hd.exchange_rate_type, -- EXCHANGE_RATE_TYPE
hd.exchange_rate, -- EXCHANGE_RATE
DECODE(hd.previous_customer_trx_id, NULL, hd.exchange_date, (select trx_date from ra_customer_trx_all where customer_trx_id = hd.previous_customer_trx_id) ), -- EXCHANGE_DATE
gld.acctd_amount, -- ACCTD_AMOUNT
'', -- TAXABLE_ACCTD_AMOUNT
gld.org_id, -- ORG_ID
gld.customer_trx_id, -- HEADER_ID
'CTLGD', -- POSTING_ENTITY
'', -- CASH_RECEIPT_ID
hd.customer_trx_id, -- CUSTOMER_TRX_ID
li.customer_trx_line_id, -- CUSTOMER_TRX_LINE_ID
gld.cust_trx_line_gl_dist_id, -- CUST_TRX_LINE_GL_DIST_ID
gld.cust_trx_line_salesrep_id, -- CUST_TRX_LINE_SALESREP_ID
li.inventory_item_id, -- INVENTORY_ITEM_ID
li.sales_tax_id, -- SALES_TAX_ID
osp.master_organization_id, -- SO_ORGANIZATION_ID
li.tax_exemption_id, -- TAX_EXEMPTION_ID
li.uom_code, -- UOM_CODE
li.warehouse_id, -- WAREHOUSE_ID
'', -- AGREEMENT_ID
'', -- CUSTOMER_BANK_ACCT_ID
'', -- DRAWEE_BANK_ACCOUNT_ID
'', -- REMITTANCE_BANK_ACCT_ID
'', -- DISTRIBUTION_SET_ID
'', -- PAYMENT_SCHEDULE_ID
'', -- RECEIPT_METHOD_ID
'', -- RECEIVABLES_TRX_ID
'', -- ED_ADJ_RECEIVABLES_TRX_ID
'', -- UNED_RECEIVABLES_TRX_ID
'', -- SET_OF_BOOKS_ID
'', -- SALESREP_ID
'', -- BILL_SITE_USE_ID
'', -- DRAWEE_SITE_USE_ID
'', -- PAYING_SITE_USE_ID
'', -- SOLD_SITE_USE_ID
'', -- SHIP_SITE_USE_ID
'', -- RECEIPT_CUSTOMER_SITE_USE_ID
'', -- BILL_CUST_ROLE_ID
'', -- DRAWEE_CUST_ROLE_ID
'', -- SHIP_CUST_ROLE_ID
'', -- SOLD_CUST_ROLE_ID
'', -- BILL_CUSTOMER_ID
'', -- DRAWEE_CUSTOMER_ID
'', -- PAYING_CUSTOMER_ID
'', -- SOLD_CUSTOMER_ID
'', -- SHIP_CUSTOMER_ID
'', -- REMIT_ADDRESS_ID
'', -- RECEIPT_BATCH_ID
'', -- RECEIVABLE_APPLICATION_ID
'', -- CUSTOMER_BANK_BRANCH_ID
'', -- ISSUER_BANK_BRANCH_ID
'', -- BATCH_SOURCE_ID
'', -- BATCH_ID
'', -- TERM_ID
'N', -- SELECT_FLAG -- This flag set to Y is probably ok
-- but as it is only used for
-- single document with application
-- driving by ctlgd, no shared views
-- should access to it
-- if we set this flag to Y still
-- it should not be a problem as the
-- line number will filter it out for
-- to be a source for a specific line
'L', -- LEVEL_FLAG
'' , -- FROM_TO_FLAG
NULL, -- PAIRED_CCID
NULL -- PAIRE_DIST_ID
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
--BUG#4645389
,li.tax_line_id --tax_line_id
,'N' --MFAR_ADDITIONAL_ENTRY
--}
FROM xla_events_gt gt,
ra_cust_trx_line_gl_dist_all gld,
ra_customer_trx_lines_all li,
ra_customer_trx_all hd,
gl_sets_of_books sob,
oe_system_parameters_all osp
WHERE gt.event_type_code IN ('INV_CREATE' , 'INV_UPDATE' ,
'CM_CREATE' , 'CM_UPDATE' ,
'DM_CREATE' , 'DM_UPDATE' ,
'DEP_CREATE' , 'DEP_UPDATE' ,
'GUAR_CREATE' , 'GUAR_UPDATE' ,
'CB_CREATE' ) --BUG#3419926
AND gt.application_id = p_application_id
AND gld.event_id = gt.event_id
AND hd.customer_trx_id = gt.source_id_int_1 --BUG#5517976
AND gld.customer_trx_line_id = li.customer_trx_line_id(+)
AND gld.customer_trx_id = hd.customer_trx_id
AND gld.set_of_books_id = sob.set_of_books_id
AND gld.org_id = osp.org_id(+)
AND gld.account_set_flag = 'N';
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,AMOUNT
,PAIRED_CCID
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--BUG#4645389
,tax_line_id
,MFAR_ADDITIONAL_ENTRY
,ADDITIONAL_CHAR2
--}
)
SELECT /*+LEADING(gt) USE_NL(gt,adj)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
trxt.exchange_rate_type, -- EXCHANGE_RATE_TYPE
trxt.exchange_rate, -- EXCHANGE_RATE
trxt.exchange_date, -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0)-
NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0) -
NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
adj.org_id, -- ORG_ID
adj.adjustment_id, -- HEADER_ID
'ADJ', -- POSTING_ENTITY
adj.associated_cash_receipt_id, -- CASH_RECEIPT_ID
adj.customer_trx_id, -- CUSTOMER_TRX_ID
tlt.customer_trx_line_id, -- CUSTOMER_TRX_LINE_ID
gldt.cust_trx_line_gl_dist_id, -- CUST_TRX_LINE_GL_DIST_ID
gldt.cust_trx_line_salesrep_id, -- CUST_TRX_LINE_SALESREP_ID
tlt.inventory_item_id, -- INVENTORY_ITEM_ID
tlt.sales_tax_id, -- SALES_TAX_ID
osp.master_organization_id, -- SO_ORGANIZATION_ID
tlt.tax_exemption_id, -- TAX_EXEMPTION_ID
tlt.uom_code, -- UOM_CODE
tlt.warehouse_id, -- WAREHOUSE_ID
trxt.agreement_id , -- AGREEMENT_ID
'', -- CUSTOMER_BANK_ACCT_ID
'', -- DRAWEE_BANK_ACCOUNT_ID
'', -- REMITTANCE_BANK_ACCT_ID
'', -- DISTRIBUTION_SET_ID
'', -- PAYMENT_SCHEDULE_ID
'', -- RECEIPT_METHOD_ID
'', -- RECEIVABLES_TRX_ID
'', -- ED_ADJ_RECEIVABLES_TRX_ID
'', -- UNED_RECEIVABLES_TRX_ID
'', -- SET_OF_BOOKS_ID
'', -- SALESREP_ID
'', -- BILL_SITE_USE_ID
'', -- DRAWEE_SITE_USE_ID
'', -- PAYING_SITE_USE_ID
'', -- SOLD_SITE_USE_ID
'', -- SHIP_SITE_USE_ID
'', -- RECEIPT_CUSTOMER_SITE_USE_ID
'', -- BILL_CUST_ROLE_ID
'', -- DRAWEE_CUST_ROLE_ID
'', -- SHIP_CUST_ROLE_ID
'', -- SOLD_CUST_ROLE_ID
'', -- BILL_CUSTOMER_ID
'', -- DRAWEE_CUSTOMER_ID
'', -- PAYING_CUSTOMER_ID
'', -- SOLD_CUSTOMER_ID
'', -- SHIP_CUSTOMER_ID
'', -- REMIT_ADDRESS_ID
'', -- RECEIPT_BATCH_ID
'', -- RECEIVABLE_APPLICATION_ID
'', -- CUSTOMER_BANK_BRANCH_ID
'', -- ISSUER_BANK_BRANCH_ID
'', -- BATCH_SOURCE_ID
'', -- BATCH_ID
'', -- TERM_ID
'N', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'', -- FROM_TO_FLAG
NVL(dist.amount_cr,0)
-NVL(dist.amount_dr,0), -- AMOUNT
NULL -- PAIRED_CCID
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
--BUG#4645389
,tlt.tax_line_id --tax_line_id
,'N' --MFAR_ADDITIONAL_ENTRY
,decode(trxt.upgrade_method,'R12_11ICASH','N','')
--}
FROM xla_events_gt gt,
ar_distributions_all dist,
ar_adjustments_all adj,
gl_sets_of_books sob,
ra_customer_trx_all trxt,
ra_customer_trx_lines_all tlt,
ra_cust_trx_line_gl_dist_all gldt,
oe_system_parameters_all osp
WHERE gt.event_type_code = 'ADJ_CREATE'
AND gt.application_id = p_application_id
AND adj.event_id = gt.event_id
AND adj.customer_trx_id = trxt.customer_trx_id
AND dist.source_table = 'ADJ'
AND dist.source_id = adj.adjustment_id
--{Pass adj distribution for the REC
-- AND dist.source_type <> 'REC'
--}
-- Need to add PSA upgrade impact
AND DECODE(adj.upgrade_method,
'11IMFAR',DECODE(dist.source_table_secondary,'UPMFAJMIAR','Y','N'),
'Y') = 'Y'
AND dist.ref_customer_trx_line_id = tlt.customer_trx_line_id(+)
AND dist.ref_cust_trx_line_gl_dist_id = gldt.cust_trx_line_gl_dist_id(+)
AND adj.set_of_books_id = sob.set_of_books_id
AND adj.org_id = osp.org_id(+);
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--BUG#4645389
,tax_line_id
--BUG#5366837
,additional_char1
,MFAR_ADDITIONAL_ENTRY
,SOURCE_TYPE
,CM_APP_TO_TRX_LINE_ID
,BAL_SEG_VALUE
,ADDITIONAL_CHAR2
)
SELECT /*+LEADING(gt) cardinality(gt,1) USE_NL(gt, app)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
trxt.exchange_rate_type, -- EXCHANGE_RATE_TYPE
trxt.exchange_rate, -- EXCHANGE_RATE
-- bug 7535858 Default Exch Date as Trx Date for Base Currency Line for ALC Calculation
decode(trxt.invoice_currency_code,sob.currency_code,
trxt.trx_date, trxt.exchange_date), -- EXCHANGE_DATE
-- trxt.exchange_date, -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0) -
NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0) -
NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
app.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_TABLE_ID
'APP', -- POSTING_ENTITY
NULL, -- CASH_RECEIPT_ID
trxt.customer_trx_id, -- CUSTOMER_TRX_ID
tlt.customer_trx_line_id, -- CUSTOMER_TRX_LINE_ID
gldt.cust_trx_line_gl_dist_id, -- CUST_TRX_LINE_GL_DIST_ID
gldt.cust_trx_line_salesrep_id, -- CUST_TRX_LINE_SALESREP_ID
tlt.inventory_item_id, -- INVENTORY_ITEM_ID
tlt.sales_tax_id, -- SALES_TAX_ID
osp.master_organization_id, -- SO_ORGANIZATION_ID
tlt.tax_exemption_id, -- TAX_EXEMPTION_ID
tlt.uom_code, -- UOM_CODE
tlt.warehouse_id, -- WAREHOUSE_ID
trxt.agreement_id, -- AGREEMENT_ID
trxt.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
trxt.drawee_bank_account_id, -- DRAWEE_BANK_ACCOUNT_ID
trxt.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
NULL, -- DISTRIBUTION_SET_ID
psch.payment_schedule_id, -- PAYMENT_SCHEDULE_ID
trxt.receipt_method_id, -- RECEIPT_METHOD_ID
NULL, -- RECEIVABLES_TRX_ID
arp_xla_extract_main_pkg.ed_uned_trx('EDISC',app.org_id), -- ED_ADJ_RECEIVABLES_TRX_ID
arp_xla_extract_main_pkg.ed_uned_trx('UNEDISC',app.org_id), -- UNED_RECEIVABLES_TRX_ID
trxt.set_of_books_id, -- SET_OF_BOOKS_ID
trxt.primary_salesrep_id, -- SALESREP_ID
trxt.bill_to_site_use_id, -- BILL_SITE_USE_ID
trxt.drawee_site_use_id, -- DRAWEE_SITE_USE_ID
trxt.paying_site_use_id, -- PAYING_SITE_USE_ID
trxt.sold_to_site_use_id, -- SOLD_SITE_USE_ID
trxt.ship_to_site_use_id, -- SHIP_SITE_USE_ID
NULL, -- RECEIPT_CUSTOMER_SITE_USE_ID
trxt.bill_to_contact_id, -- BILL_CUST_ROLE_ID
trxt.drawee_contact_id, -- DRAWEE_CUST_ROLE_ID
trxt.ship_to_contact_id, -- SHIP_CUST_ROLE_ID
trxt.sold_to_contact_id, -- SOLD_CUST_ROLE_ID
trxt.bill_to_customer_id, -- BILL_CUSTOMER_ID
trxt.drawee_id, -- DRAWEE_CUSTOMER_ID
trxt.paying_customer_id, -- PAYING_CUSTOMER_ID
trxt.sold_to_customer_id, -- SOLD_CUSTOMER_ID
trxt.ship_to_customer_id, -- SHIP_CUSTOMER_ID
trxt.remit_to_address_id, -- REMIT_ADDRESS_ID
NULL, -- RECEIPT_BATCH_ID
NULL, -- RECEIVABLE_APPLICATION_ID
NULL, -- CUSTOMER_BANK_BRANCH_ID
NULL, -- ISSUER_BANK_BRANCH_ID
trxt.batch_source_id, -- BATCH_SOURCE_ID
trxt.batch_id, -- BATCH_ID
trxt.term_id, -- TERM_ID
'Y', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'T', -- FROM_TO_FLAG
-- NVL(dist.from_amount_cr,0)
-- -NVL(dist.from_amount_dr,0), -- FROM_AMOUNT,
CASE WHEN (app.upgrade_method IS NULL AND app.status ='APP') THEN
CASE WHEN (dist.from_amount_dr IS NOT NULL OR dist.from_amount_cr IS NOT NULL) THEN
NVL(dist.from_amount_cr,0)-NVL(dist.from_amount_dr,0)
ELSE
CASE WHEN (dist.source_type NOT IN ('REC','EDISC','UNEDISC')) THEN
NULL
ELSE
CASE WHEN (app.earned_discount_taken IS NOT NULL AND
app.earned_discount_taken = NVL(dist.amount_dr,0)-NVL(dist.amount_cr,0) AND
app.acctd_earned_discount_taken = NVL(dist.acctd_amount_dr,0)-NVL(dist.acctd_amount_cr,0)
AND dist.source_type = 'REC') THEN
NULL
ELSE
CASE WHEN (trxt.invoice_currency_code = cr.currency_code) THEN
NVL(dist.amount_cr,0)-NVL(dist.amount_dr,0)
ELSE
CASE WHEN (app.amount_applied <> 0 AND app.amount_applied_from <> 0) THEN
NVL(app.amount_applied_from / app.amount_applied * dist.amount_cr,0)-
NVL(app.amount_applied_from / app.amount_applied * dist.amount_dr,0)
ELSE NULL END
END
END
END
END
ELSE
NVL(dist.from_amount_cr,0)
-NVL(dist.from_amount_dr,0)
END, -- FROM_AMOUNT
NVL(dist.amount_cr,0) - NVL(dist.amount_dr,0), -- AMOUNT
-- NVL(dist.from_acctd_amount_cr,0)
-- -NVL(dist.from_acctd_amount_dr,0) -- FROM_ACCTD_AMOUNT
CASE WHEN (app.upgrade_method IS NULL AND app.status ='APP') THEN
CASE WHEN (dist.from_acctd_amount_dr IS NOT NULL OR dist.from_acctd_amount_cr IS NOT NULL) THEN
NVL(dist.from_acctd_amount_cr,0)-NVL(dist.from_acctd_amount_dr,0)
ELSE
CASE WHEN (dist.source_type NOT IN ('REC','EDISC','UNEDISC')) THEN
NULL
ELSE
CASE WHEN (app.earned_discount_taken IS NOT NULL AND
app.earned_discount_taken = NVL(dist.amount_dr,0)-NVL(dist.amount_cr,0) AND
app.acctd_earned_discount_taken = NVL(dist.acctd_amount_dr,0)-NVL(dist.acctd_amount_cr,0)
AND dist.source_type = 'REC') THEN
NULL
ELSE
CASE WHEN (trxt.invoice_currency_code = sob.currency_code AND
cr.currency_code = sob.currency_code ) THEN
NVL(dist.acctd_amount_cr,0)-NVL(dist.acctd_amount_dr,0)
ELSE
CASE WHEN (app.acctd_amount_applied_to <> 0 AND app.acctd_amount_applied_from <> 0) THEN
NVL(app.acctd_amount_applied_from / app.acctd_amount_applied_to * dist.acctd_amount_cr,0)-
NVL(app.acctd_amount_applied_from / app.acctd_amount_applied_to * dist.acctd_amount_dr,0)
ELSE NULL END
END
END
END
END
ELSE
NVL(dist.from_acctd_amount_cr,0)
-NVL(dist.from_acctd_amount_dr,0)
END, -- FROM_ACCTD_AMOUNT
--{BUG#4356088
gt.event_type_code
,gt.event_class_code
,gt.entity_code
--BUG#4645389
,tlt.tax_line_id --tax_line_id
--BUG5366837
,app.upgrade_method
,'N' --MFAR_ADDITIONAL_ENTRY
, NULL
, to_number(NULL)
, NULL
,decode(trxt.upgrade_method,'R12_11ICASH','N','')
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ra_customer_trx_all trxt,
ra_customer_trx_lines_all tlt,
ra_cust_trx_line_gl_dist_all gldt,
ar_payment_schedules_all psch,
ar_cash_receipts_all cr
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ) -- Uptake XLA reversal, 'RECP_REVERSE')
-- Exclude 'RECP_REVERSE' for no extract at line level is reuired for Reversal
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND (app.upgrade_method IN ('R12_NLB','R12', 'R12_11IMFAR', 'R12_11ICASH','11I_R12_POST','R12_MERGE')
OR (app.upgrade_method IS NULL AND app.status = 'APP') --11i Accrual
--Need to add PSA upgrade impact
OR (DECODE(app.upgrade_method,
'11I_MFAR_UPG' ,DECODE(dist.source_table_secondary,'UPMFRAMIAR','Y','N'),
'N') = 'Y'))
AND app.set_of_books_id = sob.set_of_books_id
AND app.org_id = osp.org_id(+)
AND app.applied_customer_trx_id = trxt.customer_trx_id
--5201086
AND app.cash_receipt_id = cr.cash_receipt_id
AND dist.source_id = app.receivable_application_id
AND dist.source_table = 'RA'
AND dist.ref_customer_trx_line_id = tlt.customer_trx_line_id(+)
AND dist.ref_cust_trx_line_gl_dist_id = gldt.cust_trx_line_gl_dist_id(+)
AND trxt.customer_trx_id = psch.customer_trx_id
AND NVL(psch.terms_sequence_number,1) = 1
--{Pass also the UNAPP UNID ... everything
-- AND dist.source_type IN ('REC','EDISC','UNEDISC')
--}
--}
-- AND dist.activity_bucket IS NOT NULL
UNION ALL
SELECT /*+LEADING(gt) cardinality(gt,1) USE_NL(gt, app)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
trxt.exchange_rate_type, -- EXCHANGE_RATE_TYPE
trxt.exchange_rate, -- EXCHANGE_RATE
-- bug 9483060 Default Exch Date as Trx Date for Base Currency Line for ALC Calculation
decode(trxt.invoice_currency_code,sob.currency_code,
trxt.trx_date, trxt.exchange_date), -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0) -
NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0) -
NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
app.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_TABLE_ID
'APP', -- POSTING_ENTITY
NULL, -- CASH_RECEIPT_ID
trxt.customer_trx_id, -- CUSTOMER_TRX_ID
tlt.customer_trx_line_id, -- CUSTOMER_TRX_LINE_ID
gldt.cust_trx_line_gl_dist_id, -- CUST_TRX_LINE_GL_DIST_ID
gldt.cust_trx_line_salesrep_id, -- CUST_TRX_LINE_SALESREP_ID
tlt.inventory_item_id, -- INVENTORY_ITEM_ID
tlt.sales_tax_id, -- SALES_TAX_ID
osp.master_organization_id, -- SO_ORGANIZATION_ID
tlt.tax_exemption_id, -- TAX_EXEMPTION_ID
tlt.uom_code, -- UOM_CODE
tlt.warehouse_id, -- WAREHOUSE_ID
trxt.agreement_id, -- AGREEMENT_ID
trxt.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
trxt.drawee_bank_account_id, -- DRAWEE_BANK_ACCOUNT_ID
trxt.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
NULL, -- DISTRIBUTION_SET_ID
psch.payment_schedule_id, -- PAYMENT_SCHEDULE_ID
trxt.receipt_method_id, -- RECEIPT_METHOD_ID
NULL, -- RECEIVABLES_TRX_ID
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
trxt.set_of_books_id, -- SET_OF_BOOKS_ID
trxt.primary_salesrep_id, -- SALESREP_ID
trxt.bill_to_site_use_id, -- BILL_SITE_USE_ID
trxt.drawee_site_use_id, -- DRAWEE_SITE_USE_ID
trxt.paying_site_use_id, -- PAYING_SITE_USE_ID
trxt.sold_to_site_use_id, -- SOLD_SITE_USE_ID
trxt.ship_to_site_use_id, -- SHIP_SITE_USE_ID
NULL, -- RECEIPT_CUSTOMER_SITE_USE_ID
trxt.bill_to_contact_id, -- BILL_CUST_ROLE_ID
trxt.drawee_contact_id, -- DRAWEE_CUST_ROLE_ID
trxt.ship_to_contact_id, -- SHIP_CUST_ROLE_ID
trxt.sold_to_contact_id, -- SOLD_CUST_ROLE_ID
trxt.bill_to_customer_id, -- BILL_CUSTOMER_ID
trxt.drawee_id, -- DRAWEE_CUSTOMER_ID
trxt.paying_customer_id, -- PAYING_CUSTOMER_ID
trxt.sold_to_customer_id, -- SOLD_CUSTOMER_ID
trxt.ship_to_customer_id, -- SHIP_CUSTOMER_ID
trxt.remit_to_address_id, -- REMIT_ADDRESS_ID
NULL, -- RECEIPT_BATCH_ID
NULL, -- RECEIVABLE_APPLICATION_ID
NULL, -- CUSTOMER_BANK_BRANCH_ID
NULL, -- ISSUER_BANK_BRANCH_ID
trxt.batch_source_id, -- BATCH_SOURCE_ID
trxt.batch_id, -- BATCH_ID
trxt.term_id, -- TERM_ID
'Y', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'T', -- FROM_TO_FLAG
-- NVL(dist.from_amount_cr,0) -NVL(dist.from_amount_dr,0), -- FROM_AMOUNT,
CASE WHEN (dist.from_amount_cr IS NULL AND dist.from_amount_dr IS NULL) THEN
NVL(dist.amount_cr,0) - NVL(dist.amount_dr,0)
ELSE
NVL(dist.from_amount_cr,0) - NVL(dist.from_amount_dr,0)
END, -- FROM_AMOUNT
NVL(dist.amount_cr,0) -NVL(dist.amount_dr,0), -- AMOUNT
-- NVL(dist.from_acctd_amount_cr,0) -NVL(dist.from_acctd_amount_dr,0) -- FROM_ACCTD_AMOUNT
CASE WHEN (dist.from_acctd_amount_cr IS NULL AND dist.from_acctd_amount_dr IS NULL) THEN
NVL(dist.acctd_amount_cr,0) - NVL(dist.acctd_amount_dr,0)
ELSE
NVL(dist.from_acctd_amount_cr,0) - NVL(dist.from_acctd_amount_dr,0)
END, -- FROM_ACCTD_MOUNT
--{BUG#4356088
gt.event_type_code
,gt.event_class_code
,gt.entity_code
--BUG#4645389
,tlt.tax_line_id --tax_line_id
--BUG#5366837
,app.upgrade_method
,'N' --MFAR_ADDITIONAL_ENTRY
, dist.source_type
,decode(dist.REF_PREV_CUST_TRX_LINE_ID, NULL, to_number(NULL),dist.ref_customer_trx_line_id) -- 9860123
,the_segment_value(sob.chart_of_accounts_id, 'GL_BALANCING', dist.ref_dist_ccid)
,decode(trxt.upgrade_method,'R12_11ICASH','N','')
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ra_customer_trx_all trxt,
ra_customer_trx_lines_all tlt,
ra_cust_trx_line_gl_dist_all gldt,
ar_payment_schedules_all psch
WHERE gt.event_type_code IN ( 'CM_CREATE','CM_UPDATE' )
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND (app.upgrade_method IN ('R12_NLB','R12', 'R12_11IMFAR', 'R12_11ICASH','11I_R12_POST','R12_MERGE')
OR (app.upgrade_method IS NULL AND app.status = 'APP')
OR (DECODE(app.upgrade_method,
'11I_MFAR_UPG' ,DECODE(dist.source_table_secondary,'UPMFRAMIAR','Y','N'),
'N') = 'Y'))
AND dist.source_table = 'RA'
AND dist.source_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND app.org_id = osp.org_id(+)
AND app.applied_customer_trx_id = trxt.customer_trx_id
AND dist.ref_customer_trx_line_id = tlt.customer_trx_line_id(+)
AND dist.ref_cust_trx_line_gl_dist_id = gldt.cust_trx_line_gl_dist_id(+)
AND trxt.customer_trx_id = psch.customer_trx_id
AND NVL(psch.terms_sequence_number,1) = 1
-- {Pass every distributions REC EDISC UNEDISC UNAPP UNID
-- AND dist.source_type IN ('REC','EDISC','UNEDISC')
-- }
-- AND dist.activity_bucket IS NOT NULL
/* Bug 6119725 Begin Changes */
AND ((( dist.ref_cust_trx_line_gl_dist_id IS NOT NULL AND sign(dist.ref_cust_trx_line_gl_dist_id) = 1)
AND dist.ref_cust_trx_line_gl_dist_id IN (SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all ctlgd
WHERE ctlgd.customer_trx_id = app.applied_customer_trx_id)) -- Select only TO rows which belong to Invoice
OR (((dist.ref_cust_trx_line_gl_dist_id IS NULL OR sign(dist.ref_cust_trx_line_gl_dist_id) = -1)
AND ((sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0))) = sign(nvl(dist.amount_cr,0) * -1+nvl(dist.amount_dr,0)) AND dist.source_type = 'DEFERRED_TAX' )
OR (sign((app.acctd_amount_applied_to+nvl(app.acctd_earned_discount_taken,0)+nvl(app.acctd_unearned_discount_taken,0))) = sign(nvl(dist.acctd_amount_dr,0) * -1+nvl(dist.acctd_amount_cr,0)) AND dist.source_type = 'CURR_ROUND')
OR
( (sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0))) = sign(nvl(dist.amount_dr,0) * -1+nvl(dist.amount_cr,0)) AND dist.source_type not in ('DEFERRED_TAX', 'CURR_ROUND'))
AND (((sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0)))*-1) <> 0)
OR
((sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0)))*-1 = 0)
AND dist.amount_dr is not null)))))))
/* Bug 6119725 End Changes */
UNION
--HYUCMACT
SELECT /*+LEADING(gt) cardinality(gt,1) USE_NL(gt, app)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
trx.EXCHANGE_RATE_TYPE, -- EXCHANGE_RATE_TYPE
trx.EXCHANGE_RATE, -- EXCHANGE_RATE
trx.EXCHANGE_DATE, -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0) -
NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0) -
NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
app.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_TABLE_ID
'APP', -- POSTING_ENTITY
NULL, -- CASH_RECEIPT_ID
app.customer_trx_id, -- CUSTOMER_TRX_ID
NULL, -- CUSTOMER_TRX_LINE_ID
NULL, -- CUST_TRX_LINE_GL_DIST_ID
NULL, -- CUST_TRX_LINE_SALESREP_ID
NULL, -- INVENTORY_ITEM_ID
NULL, -- SALES_TAX_ID
NULL, -- SO_ORGANIZATION_ID
NULL, -- TAX_EXEMPTION_ID
NULL, -- UOM_CODE
NULL, -- WAREHOUSE_ID
NULL, -- AGREEMENT_ID
NULL, -- CUSTOMER_BANK_ACCT_ID
NULL, -- DRAWEE_BANK_ACCOUNT_ID
NULL, -- REMITTANCE_BANK_ACCT_ID
NULL, -- DISTRIBUTION_SET_ID
NULL, -- PAYMENT_SCHEDULE_ID
NULL, -- RECEIPT_METHOD_ID
app.receivables_trx_id, -- RECEIVABLES_TRX_ID
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
app.set_of_books_id, -- SET_OF_BOOKS_ID
NULL, -- SALESREP_ID
trx.BILL_TO_SITE_USE_ID, -- BILL_SITE_USE_ID
NULL, -- DRAWEE_SITE_USE_ID
NULL, -- PAYING_SITE_USE_ID
trx.SOLD_TO_SITE_USE_ID, -- SOLD_SITE_USE_ID
trx.SHIP_TO_SITE_USE_ID, -- SHIP_SITE_USE_ID
NULL, -- RECEIPT_CUSTOMER_SITE_USE_ID
NULL, -- BILL_CUST_ROLE_ID
NULL, -- DRAWEE_CUST_ROLE_ID
NULL, -- SHIP_CUST_ROLE_ID
NULL, -- SOLD_CUST_ROLE_ID
trx.BILL_TO_CUSTOMER_ID, -- BILL_CUSTOMER_ID
NULL, -- DRAWEE_CUSTOMER_ID
NULL, -- PAYING_CUSTOMER_ID
trx.SOLD_TO_CUSTOMER_ID, -- SOLD_CUSTOMER_ID
trx.SHIP_TO_CUSTOMER_ID, -- SHIP_CUSTOMER_ID
NULL, -- REMIT_ADDRESS_ID
NULL, -- RECEIPT_BATCH_ID
app.receivable_application_id, -- RECEIVABLE_APPLICATION_ID
NULL, -- CUSTOMER_BANK_BRANCH_ID
NULL, -- ISSUER_BANK_BRANCH_ID
NULL, -- BATCH_SOURCE_ID
NULL, -- BATCH_ID
NULL, -- TERM_ID
'Y', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'T', -- FROM_TO_FLAG
NVL(dist.amount_cr,0) - NVL(dist.amount_dr,0), -- FROM_AMOUNT
NVL(dist.amount_cr,0) -NVL(dist.amount_dr,0), -- AMOUNT
NVL(dist.acctd_amount_cr,0) - NVL(dist.acctd_amount_dr,0), -- FROM_ACCTD_MOUNT
gt.event_type_code
,gt.event_class_code
,gt.entity_code
,NULL --tax_line_id
--BUG#5366837
,app.upgrade_method
,'N' --MFAR_ADDITIONAL_ENTRY
, dist.source_type
, to_number(NULL)
, NULL
, NULL
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_sets_of_books sob,
ra_customer_trx_all trx
WHERE gt.event_type_code IN ( 'CM_CREATE','CM_UPDATE' )
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND dist.source_table = 'RA'
AND dist.source_id = app.receivable_application_id
AND app.status = 'ACTIVITY'
AND app.set_of_books_id = sob.set_of_books_id
AND trx.customer_trx_id = app.customer_trx_id
AND dist.source_type = 'ACTIVITY';
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
,event_type_code
,event_class_code
,entity_code
,tax_line_id
,ADDITIONAL_CHAR1
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt, app)*/
gt.event_id -- EVENT_ID
,acb.cash_basis_distribution_id -- LINE_NUMBER
,'' -- LANGUAGE
,sob.set_of_books_id -- LEDGER_ID
,app.receivable_application_id -- SOURCE_ID
,'RA' -- SOURCE_TABLE
,acb.cash_basis_distribution_id -- LINE_ID
,NULL -- TAX_CODE_ID
,NULL -- LOCATION_SEGMENT_ID
,sob.currency_code -- BASE_CURRENCY
,trxt.exchange_rate_type -- EXCHANGE_RATE_TYPE
,trxt.exchange_rate -- EXCHANGE_RATE
,trxt.exchange_date -- EXCHANGE_DATE
,acb.acctd_amount -- ACCTD_AMOUNT
,NULL -- TAXABLE_ACCTD_AMOUNT
,app.org_id -- ORG_ID
,app.receivable_application_id -- HEADER_TABLE_ID
,'APP' -- POSTING_ENTITY
,NULL -- CASH_RECEIPT_ID
,trxt.customer_trx_id -- CUSTOMER_TRX_ID
,tlt.customer_trx_line_id -- CUSTOMER_TRX_LINE_ID
,gldt.cust_trx_line_gl_dist_id -- CUST_TRX_LINE_GL_DIST_ID
,gldt.cust_trx_line_salesrep_id -- CUST_TRX_LINE_SALESREP_ID
,tlt.inventory_item_id -- INVENTORY_ITEM_ID
,tlt.sales_tax_id -- SALES_TAX_ID
,osp.master_organization_id -- SO_ORGANIZATION_ID
,tlt.tax_exemption_id -- TAX_EXEMPTION_ID
,tlt.uom_code -- UOM_CODE
,tlt.warehouse_id -- WAREHOUSE_ID
,trxt.agreement_id -- AGREEMENT_ID
,trxt.customer_bank_account_id -- CUSTOMER_BANK_ACCT_ID
,trxt.drawee_bank_account_id -- DRAWEE_BANK_ACCOUNT_ID
,trxt.remit_bank_acct_use_id -- REMITTANCE_BANK_ACCT_ID
,NULL -- DISTRIBUTION_SET_ID
,psch.payment_schedule_id -- PAYMENT_SCHEDULE_ID
,trxt.receipt_method_id -- RECEIPT_METHOD_ID
,NULL -- RECEIVABLES_TRX_ID
,NULL -- ED_ADJ_RECEIVABLES_TRX_ID
,NULL -- UNED_RECEIVABLES_TRX_ID
,trxt.set_of_books_id -- SET_OF_BOOKS_ID
,trxt.primary_salesrep_id -- SALESREP_ID
,trxt.bill_to_site_use_id -- BILL_SITE_USE_ID
,trxt.drawee_site_use_id -- DRAWEE_SITE_USE_ID
,trxt.paying_site_use_id -- PAYING_SITE_USE_ID
,trxt.sold_to_site_use_id -- SOLD_SITE_USE_ID
,trxt.ship_to_site_use_id -- SHIP_SITE_USE_ID
,NULL -- RECEIPT_CUSTOMER_SITE_USE_ID
,trxt.bill_to_contact_id -- BILL_CUST_ROLE_ID
,trxt.drawee_contact_id -- DRAWEE_CUST_ROLE_ID
,trxt.ship_to_contact_id -- SHIP_CUST_ROLE_ID
,trxt.sold_to_contact_id -- SOLD_CUST_ROLE_ID
,trxt.bill_to_customer_id -- BILL_CUSTOMER_ID
,trxt.drawee_id -- DRAWEE_CUSTOMER_ID
,trxt.paying_customer_id -- PAYING_CUSTOMER_ID
,trxt.sold_to_customer_id -- SOLD_CUSTOMER_ID
,trxt.ship_to_customer_id -- SHIP_CUSTOMER_ID
,trxt.remit_to_address_id -- REMIT_ADDRESS_ID
,NULL -- RECEIPT_BATCH_ID
,NULL -- RECEIVABLE_APPLICATION_ID
,NULL -- CUSTOMER_BANK_BRANCH_ID
,NULL -- ISSUER_BANK_BRANCH_ID
,trxt.batch_source_id -- BATCH_SOURCE_ID
,trxt.batch_id -- BATCH_ID
,trxt.term_id -- TERM_ID
,'Y' -- SELECT_FLAG
,'L' -- LEVEL_FLAG
,'T' -- FROM_TO_FLAG
,acb.from_amount -- FROM_AMOUNT
,acb.amount -- AMOUNT
,acb.from_acctd_amount -- FROM_ACCTD_AMOUNT
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,tlt.tax_line_id --tax_line_id
,app.upgrade_method
,'N' --MFAR_ADDITIONAL_ENTRY
FROM xla_events_gt gt,
ar_receivable_applications_all app,
AR_CASH_BASIS_DISTS_ALL acb,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ra_customer_trx_all trxt,
ra_customer_trx_lines_all tlt,
ra_cust_trx_line_gl_dist_all gldt,
ar_payment_schedules_all psch,
ar_system_parameters_all ars
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE',
'RECP_RATE_ADJUST' ) --Uptake XLA Reversal, 'RECP_REVERSE')
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND app.receivable_application_id = acb.receivable_application_id
AND app.upgrade_method = 'R12_11ICASH_POST'
AND acb.receivable_application_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND app.org_id = osp.org_id(+)
AND app.applied_customer_trx_id = trxt.customer_trx_id
AND acb.REF_CUSTOMER_TRX_LINE_ID = tlt.customer_trx_line_id(+)
AND acb.ref_cust_trx_line_gl_dist_id = gldt.cust_trx_line_gl_dist_id(+)
AND trxt.customer_trx_id = psch.customer_trx_id
AND NVL(psch.terms_sequence_number,1) = 1
AND ars.org_id = app.org_id
AND ars.ACCOUNTING_METHOD = 'CASH'
UNION ALL
SELECT /*+LEADING(gt) USE_NL(gt, app)*/
gt.event_id -- EVENT_ID
,acb.cash_basis_distribution_id -- LINE_NUMBER
,'' -- LANGUAGE
,sob.set_of_books_id -- LEDGER_ID
,app.receivable_application_id -- SOURCE_ID
,'RA' -- SOURCE_TABLE
,acb.cash_basis_distribution_id -- LINE_ID
,NULL -- TAX_CODE_ID
,NULL -- LOCATION_SEGMENT_ID
,sob.currency_code -- BASE_CURRENCY
,trxt.exchange_rate_type -- EXCHANGE_RATE_TYPE
,trxt.exchange_rate -- EXCHANGE_RATE
,trxt.exchange_date -- EXCHANGE_DATE
,acb.acctd_amount -- ACCTD_AMOUNT
,NULL -- TAXABLE_ACCTD_AMOUNT
,app.org_id -- ORG_ID
,app.receivable_application_id -- HEADER_TABLE_ID
,'APP' -- POSTING_ENTITY
,NULL -- CASH_RECEIPT_ID
,trxt.customer_trx_id -- CUSTOMER_TRX_ID
,tlt.customer_trx_line_id -- CUSTOMER_TRX_LINE_ID
,gldt.cust_trx_line_gl_dist_id -- CUST_TRX_LINE_GL_DIST_ID
,gldt.cust_trx_line_salesrep_id -- CUST_TRX_LINE_SALESREP_ID
,tlt.inventory_item_id -- INVENTORY_ITEM_ID
,tlt.sales_tax_id -- SALES_TAX_ID
,osp.master_organization_id -- SO_ORGANIZATION_ID
,tlt.tax_exemption_id -- TAX_EXEMPTION_ID
,tlt.uom_code -- UOM_CODE
,tlt.warehouse_id -- WAREHOUSE_ID
,trxt.agreement_id -- AGREEMENT_ID
,trxt.customer_bank_account_id -- CUSTOMER_BANK_ACCT_ID
,trxt.drawee_bank_account_id -- DRAWEE_BANK_ACCOUNT_ID
,trxt.remit_bank_acct_use_id -- REMITTANCE_BANK_ACCT_ID
,NULL -- DISTRIBUTION_SET_ID
,psch.payment_schedule_id -- PAYMENT_SCHEDULE_ID
,trxt.receipt_method_id -- RECEIPT_METHOD_ID
,NULL -- RECEIVABLES_TRX_ID
,NULL -- ED_ADJ_RECEIVABLES_TRX_ID
,NULL -- UNED_RECEIVABLES_TRX_ID
,trxt.set_of_books_id -- SET_OF_BOOKS_ID
,trxt.primary_salesrep_id -- SALESREP_ID
,trxt.bill_to_site_use_id -- BILL_SITE_USE_ID
,trxt.drawee_site_use_id -- DRAWEE_SITE_USE_ID
,trxt.paying_site_use_id -- PAYING_SITE_USE_ID
,trxt.sold_to_site_use_id -- SOLD_SITE_USE_ID
,trxt.ship_to_site_use_id -- SHIP_SITE_USE_ID
,NULL -- RECEIPT_CUSTOMER_SITE_USE_ID
,trxt.bill_to_contact_id -- BILL_CUST_ROLE_ID
,trxt.drawee_contact_id -- DRAWEE_CUST_ROLE_ID
,trxt.ship_to_contact_id -- SHIP_CUST_ROLE_ID
,trxt.sold_to_contact_id -- SOLD_CUST_ROLE_ID
,trxt.bill_to_customer_id -- BILL_CUSTOMER_ID
,trxt.drawee_id -- DRAWEE_CUSTOMER_ID
,trxt.paying_customer_id -- PAYING_CUSTOMER_ID
,trxt.sold_to_customer_id -- SOLD_CUSTOMER_ID
,trxt.ship_to_customer_id -- SHIP_CUSTOMER_ID
,trxt.remit_to_address_id -- REMIT_ADDRESS_ID
,NULL -- RECEIPT_BATCH_ID
,NULL -- RECEIVABLE_APPLICATION_ID
,NULL -- CUSTOMER_BANK_BRANCH_ID
,NULL -- ISSUER_BANK_BRANCH_ID
,trxt.batch_source_id -- BATCH_SOURCE_ID
,trxt.batch_id -- BATCH_ID
,trxt.term_id -- TERM_ID
,'Y' -- SELECT_FLAG
,'L' -- LEVEL_FLAG
,'T' -- FROM_TO_FLAG
,NULL -- FROM_AMOUNT,
,acb.amount -- AMOUNT
,acb.from_acctd_Amount -- FROM_ACCTD_AMOUNT
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,tlt.tax_line_id -- tax_line_id
,app.upgrade_method
,'N' --MFAR_ADDITIONAL_ENTRY
FROM xla_events_gt gt,
ar_receivable_applications_all app,
AR_CASH_BASIS_DISTS_ALL acb,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ra_customer_trx_all trxt,
ra_customer_trx_lines_all tlt,
ra_cust_trx_line_gl_dist_all gldt,
ar_payment_schedules_all psch,
ar_system_parameters_all ars
WHERE gt.event_type_code IN ( 'CM_CREATE' ,'CM_UPDATE' )
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND app.upgrade_method = 'R12_11ICASH_POST'
AND acb.receivable_application_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND app.org_id = osp.org_id(+)
AND app.applied_customer_trx_id = trxt.customer_trx_id
AND acb.ref_customer_trx_line_id = tlt.customer_trx_line_id(+)
AND acb.ref_cust_trx_line_gl_dist_id = gldt.cust_trx_line_gl_dist_id(+)
AND trxt.customer_trx_id = psch.customer_trx_id
AND NVL(psch.terms_sequence_number,1) = 1
AND ars.org_id = app.org_id
AND ars.ACCOUNTING_METHOD = 'CASH'
AND acb.ref_cust_trx_line_gl_dist_id NOT IN
(SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = gt.source_id_int_1); --Excluding the receivable distribution of the CM
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--}
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt, app)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
NULL, -- EXCHANGE_RATE_TYPE
NULL, -- EXCHANGE_RATE
NULL, -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0) -
NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0) -
NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
app.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_TABLE_ID
'APP', -- POSTING_ENTITY
NULL, -- CASH_RECEIPT_ID
NULL, -- CUSTOMER_TRX_ID
NULL, -- CUSTOMER_TRX_LINE_ID
NULL, -- CUST_TRX_LINE_GL_DIST_ID
NULL, -- CUST_TRX_LINE_SALESREP_ID
NULL, -- INVENTORY_ITEM_ID
NULL, -- SALES_TAX_ID
NULL, -- SO_ORGANIZATION_ID
NULL, -- TAX_EXEMPTION_ID
NULL, -- UOM_CODE
NULL, -- WAREHOUSE_ID
NULL, -- AGREEMENT_ID
NULL, -- CUSTOMER_BANK_ACCT_ID
NULL, -- DRAWEE_BANK_ACCOUNT_ID
NULL, -- REMITTANCE_BANK_ACCT_ID
NULL, -- DISTRIBUTION_SET_ID
NULL, -- PAYMENT_SCHEDULE_ID
NULL, -- RECEIPT_METHOD_ID
NULL, -- RECEIVABLES_TRX_ID
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
NULL, -- SET_OF_BOOKS_ID
NULL, -- SALESREP_ID
NULL, -- BILL_SITE_USE_ID
NULL, -- DRAWEE_SITE_USE_ID
NULL, -- PAYING_SITE_USE_ID
NULL, -- SOLD_SITE_USE_ID
NULL, -- SHIP_SITE_USE_ID
NULL, -- RECEIPT_CUSTOMER_SITE_USE_ID
NULL, -- BILL_CUST_ROLE_ID
NULL, -- DRAWEE_CUST_ROLE_ID
NULL, -- SHIP_CUST_ROLE_ID
NULL, -- SOLD_CUST_ROLE_ID
NULL, -- BILL_CUSTOMER_ID
NULL, -- DRAWEE_CUSTOMER_ID
NULL, -- PAYING_CUSTOMER_ID
NULL, -- SOLD_CUSTOMER_ID
NULL, -- SHIP_CUSTOMER_ID
NULL, -- REMIT_ADDRESS_ID
NULL, -- RECEIPT_BATCH_ID
NULL, -- RECEIVABLE_APPLICATION_ID
NULL, -- CUSTOMER_BANK_BRANCH_ID
NULL, -- ISSUER_BANK_BRANCH_ID
NULL, -- BATCH_SOURCE_ID
NULL, -- BATCH_ID
NULL, -- TERM_ID
'Y', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'T', -- FROM_TO_FLAG
NVL(dist.from_amount_cr,0)
-NVL(dist.from_amount_dr,0), -- FROM_AMOUNT,
NVL(dist.amount_cr,0)
-NVL(dist.amount_dr,0), -- AMOUNT
NVL(dist.from_acctd_amount_cr,0)
-NVL(dist.from_acctd_amount_dr,0) -- FROM_ACCTD_AMOUNT
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
--}
,'N' --MFAR_ADDITIONAL_ENTRY
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_distributions_all dist, --ar_distributions_all dist,
gl_sets_of_books sob
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ) --Uptake XLA reversal,'RECP_REVERSE')
-- Exclude 'RECP_REVERSE' for no extract at line level is reuired for Reversal
AND gt.application_id = p_application_id
AND gt.event_id = app.event_id
AND dist.source_table = 'RA'
AND dist.source_id = app.receivable_application_id
AND app.set_of_books_id = sob.set_of_books_id
AND dist.source_type = 'UNID';
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,CRH_STATUS
,CRH_PRV_STATUS
,AMOUNT
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--}
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt,crh)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
-- crh.exchange_rate_type, -- EXCHANGE_RATE_TYPE
-- crh.exchange_rate , -- EXCHANGE_RATE
-- crh.exchange_date , -- EXCHANGE_DATE
dist.CURRENCY_CONVERSION_TYPE, -- EXCHANGE_RATE_TYPE
dist.CURRENCY_CONVERSION_RATE , -- EXCHANGE_RATE
dist.CURRENCY_CONVERSION_DATE , -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0)
- NVL(dist.acctd_amount_dr,0) , -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0)
- NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
crh.org_id, -- ORG_ID
crh.cash_receipt_history_id, -- HEADER_TABLE_ID
'CRH', -- POSTING_ENTITY
crh.cash_receipt_id, -- CASH_RECEIPT_ID
NULL, -- CUSTOMER_TRX_ID
NULL, -- CUSTOMER_TRX_LINE_ID
NULL, -- CUST_TRX_LINE_GL_DIST_ID
NULL, -- CUST_TRX_LINE_SALESREP_ID
NULL, -- INVENTORY_ITEM_ID
NULL, -- SALES_TAX_ID
NULL, -- SO_ORGANIZATION_ID
NULL, -- TAX_EXEMPTION_ID
NULL, -- UOM_CODE
NULL, -- WAREHOUSE_ID
NULL, -- AGREEMENT_ID
NULL, -- CUSTOMER_BANK_ACCT_ID
NULL, -- DRAWEE_BANK_ACCOUNT_ID
NULL, -- REMITTANCE_BANK_ACCT_ID
NULL, -- DISTRIBUTION_SET_ID
NULL, -- PAYMENT_SCHEDULE_ID
NULL, -- RECEIPT_METHOD_ID
NULL, -- RECEIVABLES_TRX_ID
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
sob.set_of_books_id, -- SET_OF_BOOKS_ID
NULL, -- SALESREP_ID
NULL, -- BILL_SITE_USE_ID
NULL, -- DRAWEE_SITE_USE_ID
NULL, -- PAYING_SITE_USE_ID
NULL, -- SOLD_SITE_USE_ID
NULL, -- SHIP_SITE_USE_ID
NULL, -- RECEIPT_CUSTOMER_SITE_USE_ID
NULL, -- BILL_CUST_ROLE_ID
NULL, -- DRAWEE_CUST_ROLE_ID
NULL, -- SHIP_CUST_ROLE_ID
NULL, -- SOLD_CUST_ROLE_ID
NULL, -- BILL_CUSTOMER_ID
NULL, -- DRAWEE_CUSTOMER_ID
NULL, -- PAYING_CUSTOMER_ID
NULL, -- SOLD_CUSTOMER_ID
NULL, -- SHIP_CUSTOMER_ID
NULL, -- REMIT_ADDRESS_ID
NULL, -- RECEIPT_BATCH_ID
NULL, -- RECEIVABLE_APPLICATION_ID
NULL, -- CUSTOMER_BANK_BRANCH_ID
NULL, -- ISSUER_BANK_BRANCH_ID
NULL, -- BATCH_SOURCE_ID
NULL, -- BATCH_ID
NULL, -- TERM_ID
'N', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'' , -- FROM_TO_FLAG
crh.status, -- CRH_STATUS
pcrh.status, -- CRH_PRV_STATUS
NVL(dist.amount_cr,0)
- NVL(dist.amount_dr,0) -- AMOUNT
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,'N' --MFAR_ADDITIONAL_ENTRY
--}
FROM xla_events_gt gt,
ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ar_distributions_all dist,
gl_sets_of_books sob,
ar_cash_receipt_history_all pcrh
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'MISC_RECP_CREATE' ,'MISC_RECP_UPDATE' ,
--Uptake XLA Reversal 'RECP_REVERSE' ,'MISC_RECP_REVERSE' ,
--5201086
'RECP_RATE_ADJUST' ,'MISC_RECP_RATE_ADJUST')
AND gt.application_id = p_application_id
AND gt.event_id = crh.event_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND dist.source_table = 'CRH'
AND dist.source_id = crh.cash_receipt_history_id
AND cr.set_of_books_id = sob.set_of_books_id
AND crh.prv_stat_cash_receipt_hist_id = pcrh.cash_receipt_history_id(+);
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,CRH_STATUS
,CRH_PRV_STATUS
,AMOUNT
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--}
,MFAR_ADDITIONAL_ENTRY
,FROM_ACCTD_AMOUNT --Bug7255483 Added new column for acctd amount
)
SELECT /*+LEADING(gt) cardinality(gt,1) USE_NL(gt,cr)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
--5201086
crh.exchange_rate_type, -- EXCHANGE_RATE_TYPE
crh.exchange_rate , -- EXCHANGE_RATE
crh.exchange_date , -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0)
- NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0)
- NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
cr.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_TABLE_ID
--{Although the UNAPP and UNID are distributions created from RA the posting entity is CRH
-- but the source_table will be RA
'CRH', -- POSTING_ENTITY
--}
cr.cash_receipt_id, -- CASH_RECEIPT_ID
NULL, -- CUSTOMER_TRX_ID
NULL, -- CUSTOMER_TRX_LINE_ID
NULL, -- CUST_TRX_LINE_GL_DIST_ID
NULL, -- CUST_TRX_LINE_SALESREP_ID
NULL, -- INVENTORY_ITEM_ID
NULL, -- SALES_TAX_ID
NULL, -- SO_ORGANIZATION_ID
NULL, -- TAX_EXEMPTION_ID
NULL, -- UOM_CODE
NULL, -- WAREHOUSE_ID
NULL, -- AGREEMENT_ID
NULL, -- CUSTOMER_BANK_ACCT_ID
NULL, -- DRAWEE_BANK_ACCOUNT_ID
NULL, -- REMITTANCE_BANK_ACCT_ID
NULL, -- DISTRIBUTION_SET_ID
NULL, -- PAYMENT_SCHEDULE_ID
NULL, -- RECEIPT_METHOD_ID
app.receivables_trx_id, -- RECEIVABLES_TRX_ID
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
sob.set_of_books_id, -- SET_OF_BOOKS_ID
NULL, -- SALESREP_ID
NULL, -- BILL_SITE_USE_ID
NULL, -- DRAWEE_SITE_USE_ID
NULL, -- PAYING_SITE_USE_ID
NULL, -- SOLD_SITE_USE_ID
NULL, -- SHIP_SITE_USE_ID
NULL, -- RECEIPT_CUSTOMER_SITE_USE_ID
NULL, -- BILL_CUST_ROLE_ID
NULL, -- DRAWEE_CUST_ROLE_ID
NULL, -- SHIP_CUST_ROLE_ID
NULL, -- SOLD_CUST_ROLE_ID
NULL, -- BILL_CUSTOMER_ID
NULL, -- DRAWEE_CUSTOMER_ID
NULL, -- PAYING_CUSTOMER_ID
NULL, -- SOLD_CUSTOMER_ID
NULL, -- SHIP_CUSTOMER_ID
NULL, -- REMIT_ADDRESS_ID
NULL, -- RECEIPT_BATCH_ID
app.receivable_application_id, -- RECEIVABLE_APPLICATION_ID
NULL, -- CUSTOMER_BANK_BRANCH_ID
NULL, -- ISSUER_BANK_BRANCH_ID
NULL, -- BATCH_SOURCE_ID
NULL, -- BATCH_ID
NULL, -- TERM_ID
'N', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'' , -- FROM_TO_FLAG
app.status, -- CRH_STATUS
'', -- CRH_PRV_STATUS
NVL(dist.amount_cr,0)
- NVL(dist.amount_dr,0) -- AMOUNT
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,'N' --MFAR_ADDITIONAL_ENTRY
--}
--Bug7255483 Added value for new column in the view
,DECODE(NVL(app.receivables_trx_id,0), -16,
NVL(dist.from_acctd_amount_cr,0) - NVL(dist.from_acctd_amount_dr,0),to_number(NULL))
FROM xla_events_gt gt,
ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_sets_of_books sob
WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ) --Uptake XLA Reversal,'RECP_REVERSE' )
AND gt.application_id = p_application_id
AND cr.cash_receipt_id = gt.source_id_int_1
AND gt.event_id = app.event_id
AND app.status IN ('UNAPP','UNID',
--{BUG#4960533
'OTHER ACC',
'ACC','BANK_CHARGES','ACTIVITY','SHORT_TERM_DEBT')
--}
AND dist.source_table = 'RA'
AND dist.source_id = app.receivable_application_id
AND cr.set_of_books_id = sob.set_of_books_id
-- AND gt.event_id = crh.event_id(+)
AND app.event_id = crh.event_id(+)
AND app.cash_receipt_history_id = crh.cash_receipt_history_id (+)
AND crh.status(+) NOT IN ('REVERSED');
SELECT NULL
FROM gl_ledgers gl,
xla_acctg_method_rules mr,
xla_product_rules_tl pr
WHERE gl.ledger_id = p_ledger_id
AND mr.application_id = 222
AND mr.accounting_method_code = gl.SLA_ACCOUNTING_METHOD_CODE
AND mr.product_rule_code = pr.product_rule_code
AND mr.product_rule_code = 'MFAR_ACCRUAL_ACCOUNT'
AND pr.language = USERENV('LANG')
AND SYSDATE BETWEEN mr.start_date_active AND NVL(mr.end_date_active, SYSDATE);
DELETE FROM ar_crh_app_gt;
| the update procedure.
+----------------------------------------------------------------------------+
| History :
| 10-AUG-2010 Ram Manikandan Created due to bug#10047351
+---------------------------------------------------------------------------*/
PROCEDURE mfar_cmapp_fetch
IS
-- Cursor to fetch the foreign currency on-account CM applications with CURR_ROUND
CURSOR cmapp_curr_round_cur IS
SELECT ext1.event_id, ext1.source_id, ext1.amount, ext1.acctd_amount FROM ar_xla_lines_extract ext1
WHERE ext1.source_type='CURR_ROUND'
AND ext1.source_table = 'RA'
AND ext1.mfar_additional_entry ='N'
AND ext1.event_class_code = 'CREDIT_MEMO'
AND NOT EXISTS
(SELECT 1 FROM ar_xla_lines_extract ext2
WHERE ext2.event_id = ext1.event_id
AND ext2.source_id = ext1.source_id
AND ext2.source_table = 'RA'
AND ext2.source_type = 'REC'
AND ext2.mfar_additional_entry ='N'
AND ext2.event_class_code = 'CREDIT_MEMO'
AND ext2.cm_app_to_trx_line_id IS NOT NULL)
ORDER BY ext1.event_id, ext1.source_id;
SELECT DISTINCT ext1.event_id, ext1.source_id,0,0
FROM ar_xla_lines_extract ext1
WHERE ext1.exchange_rate IS NOT NULL
AND ext1.source_table = 'RA'
AND ext1.mfar_additional_entry ='N'
AND ext1.event_class_code = 'CREDIT_MEMO'
AND NOT EXISTS
(SELECT 1 FROM ar_xla_lines_extract ext2
WHERE ext2.event_id = ext1.event_id
AND ext2.source_id = ext1.source_id
AND ext2.source_table = 'RA'
AND ext2.source_type = 'REC'
AND ext2.mfar_additional_entry ='N'
AND ext2.event_class_code = 'CREDIT_MEMO'
AND ext2.cm_app_to_trx_line_id IS NOT NULL
UNION ALL
SELECT 1 FROM ar_xla_lines_extract ext3
WHERE ext3.event_id = ext1.event_id
AND ext3.source_id = ext1.source_id
AND ext3.source_type = 'CURR_ROUND'
AND ext3.source_table = 'RA'
AND ext3.mfar_additional_entry ='N'
AND ext3.event_class_code = 'CREDIT_MEMO')
ORDER BY ext1.event_id, ext1.source_id;
mfar_cmapp_trx_update(l_cmapp_curr_round_tab(i), 'CURR_ROUND');
mfar_cmapp_trx_update(l_cmapp_curr_round_tab(i), 'EXCHG_GAIN_LOSS');
| Procedure Name : mfar_cmapp_trx_update
| Description : Check if the CM and Invoice are of same funds and ratio.
| If p_call is CURR_ROUND, check if the difference in
| acctd_amounts is equal to CURR_ROUND else if p_call is
| EXCHG_GAIN_LOSS, check if the amounts are equal.
| If any of the above conditions fail, return from the
| procedure, else update the column cm_app_to_trx_line_id
| so that it behaves as a Regular CM from now on.
+----------------------------------------------------------------------------+
| History :
| 10-AUG-2010 Ram Manikandan Created due to bug#10047351
+---------------------------------------------------------------------------*/
PROCEDURE mfar_cmapp_trx_update(p_cmapp_curr_round IN cmapp_curr_round, p_call IN VARCHAR2) IS
CURSOR cmapp_rec_cur (p_source_id NUMBER, p_from_to_flag VARCHAR2) IS
SELECT bal_seg_value, sum(amount), sum(acctd_amount), min(customer_trx_line_id)
FROM ar_xla_lines_extract
WHERE source_id = p_source_id
AND source_table = 'RA'
AND posting_entity = 'APP'
AND event_class_code = 'CREDIT_MEMO'
AND source_type = 'REC'
AND mfar_additional_entry = 'N'
AND cm_app_to_trx_line_id IS NULL
AND from_to_flag = p_from_to_flag
GROUP BY bal_seg_value
ORDER BY bal_seg_value;
local_log(procedure_name => 'mfar_cmapp_trx_update',
p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_cmapp_trx_update ()+');
local_log(procedure_name => 'mfar_cmapp_trx_update',
p_msg_text => 'Source ID: '||p_cmapp_curr_round.source_id);
local_log(procedure_name => 'mfar_cmapp_trx_update',
p_msg_text => 'Local Round: '||local_round);
local_log(procedure_name => 'mfar_cmapp_trx_update',
p_msg_text => 'From count: '||l_cmapp_rec_record_tab_f.count);
local_log(procedure_name => 'mfar_cmapp_trx_update',
p_msg_text => 'To count: '||l_cmapp_rec_record_tab_t.count);
UPDATE ar_xla_lines_extract
SET cm_app_to_trx_line_id = l_cmapp_rec_record_tab_t(j).cust_trx_line_id
WHERE source_id = p_cmapp_curr_round.source_id
AND bal_seg_value = l_cmapp_rec_record_tab_t(j).bal_seg_value
AND source_type = 'REC'
AND event_class_code = 'CREDIT_MEMO';
local_log(procedure_name => 'mfar_cmapp_trx_update',
p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_cmapp_trx_update ()-');
local_log(procedure_name => 'mfar_cmapp_trx_update',
p_msg_text => 'EXCEPTION OTHERS in mfar_cmapp_trx_update '||
arp_global.CRLF || 'Error :'|| SQLERRM);
'Procedure :arp_xla_extract_main_pkg.mfar_cmapp_trx_update'|| arp_global.CRLF||
'Error :'||SQLERRM);
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,CRH_STATUS
,CRH_PRV_STATUS
,AMOUNT
,FROM_AMOUNT
,FROM_ACCTD_AMOUNT
,PREV_FUND_SEG_REPLACE
,APP_CRH_STATUS
,PAIRED_CCID
,PAIRE_DIST_ID
,REF_DIST_CCID
,REF_MF_DIST_FLAG
,ORIGIN_EXTRACT_TABLE
,EVENT_TYPE_CODE
,EVENT_CLASS_CODE
,ENTITY_CODE
,REVERSAL_CODE
,BUSINESS_FLOW_CODE
,TAX_LINE_ID
,ADDITIONAL_CHAR1
,ADDITIONAL_CHAR2
,ADDITIONAL_CHAR3
,ADDITIONAL_CHAR4
,ADDITIONAL_CHAR5
,CM_APP_TO_TRX_LINE_ID
,ADDITIONAL_ID2
,ADDITIONAL_ID3
,ADDITIONAL_ID4
,ADDITIONAL_ID5
,XLA_ENTITY_ID
,REF_CTLGD_CCID
,DIST_CCID
,FROM_EXCHANGE_RATE
,FROM_EXCHANGE_RATE_TYPE
,FROM_EXCHANGE_DATE
,FROM_CURRENCY_CODE
,TO_CURRENCY_CODE
,MFAR_ADDITIONAL_ENTRY
,THIRD_PARTY_ID
,THIRD_PARTY_SITE_ID
,THIRD_PARTY_TYPE
,SOURCE_TYPE )
SELECT l.EVENT_ID
,-1 * ar_mfar_extract_s.nextval
,l.LANGUAGE
,l.LEDGER_ID
,l.SOURCE_ID
,l.SOURCE_TABLE
,l.LINE_ID
,l.TAX_CODE_ID
,l.LOCATION_SEGMENT_ID
,l.BASE_CURRENCY_CODE
,l.EXCHANGE_RATE_TYPE
,l.EXCHANGE_RATE
,l.EXCHANGE_DATE
,curr.ACCTD_AMT
,l.TAXABLE_ACCTD_AMOUNT
,l.ORG_ID
,l.HEADER_TABLE_ID
,l.POSTING_ENTITY
,l.CASH_RECEIPT_ID
,l.CUSTOMER_TRX_ID
,l.CUSTOMER_TRX_LINE_ID
,l.CUST_TRX_LINE_GL_DIST_ID
,l.CUST_TRX_LINE_SALESREP_ID
,l.INVENTORY_ITEM_ID
,l.SALES_TAX_ID
,l.SO_ORGANIZATION_ID
,l.TAX_EXEMPTION_ID
,l.UOM_CODE
,l.WAREHOUSE_ID
,l.AGREEMENT_ID
,l.CUSTOMER_BANK_ACCT_ID
,l.DRAWEE_BANK_ACCOUNT_ID
,l.REMITTANCE_BANK_ACCT_ID
,l.DISTRIBUTION_SET_ID
,l.PAYMENT_SCHEDULE_ID
,l.RECEIPT_METHOD_ID
,l.RECEIVABLES_TRX_ID
,l.ED_ADJ_RECEIVABLES_TRX_ID
,l.UNED_RECEIVABLES_TRX_ID
,l.SET_OF_BOOKS_ID
,l.SALESREP_ID
,l.BILL_SITE_USE_ID
,l.DRAWEE_SITE_USE_ID
,l.PAYING_SITE_USE_ID
,l.SOLD_SITE_USE_ID
,l.SHIP_SITE_USE_ID
,l.RECEIPT_CUSTOMER_SITE_USE_ID
,l.BILL_CUST_ROLE_ID
,l.DRAWEE_CUST_ROLE_ID
,l.SHIP_CUST_ROLE_ID
,l.SOLD_CUST_ROLE_ID
,l.BILL_CUSTOMER_ID
,l.DRAWEE_CUSTOMER_ID
,l.PAYING_CUSTOMER_ID
,l.SOLD_CUSTOMER_ID
,l.SHIP_CUSTOMER_ID
,l.REMIT_ADDRESS_ID
,l.RECEIPT_BATCH_ID
,l.RECEIVABLE_APPLICATION_ID
,l.CUSTOMER_BANK_BRANCH_ID
,l.ISSUER_BANK_BRANCH_ID
,l.BATCH_SOURCE_ID
,l.BATCH_ID
,l.TERM_ID
,l.SELECT_FLAG
,l.LEVEL_FLAG
,l.FROM_TO_FLAG
,l.CRH_STATUS
,l.CRH_PRV_STATUS
,curr.AMOUNT
,curr.FROM_AMOUNT
,curr.FROM_ACCTD_AMT
,l.PREV_FUND_SEG_REPLACE
,l.APP_CRH_STATUS
,l.PAIRED_CCID
,l.PAIRE_DIST_ID
,l.REF_DIST_CCID
,l.REF_MF_DIST_FLAG
,l.ORIGIN_EXTRACT_TABLE
,l.EVENT_TYPE_CODE
,l.EVENT_CLASS_CODE
,l.ENTITY_CODE
,l.REVERSAL_CODE
,l.BUSINESS_FLOW_CODE
,l.TAX_LINE_ID
,l.ADDITIONAL_CHAR1
,l.ADDITIONAL_CHAR2
,l.ADDITIONAL_CHAR3
,l.ADDITIONAL_CHAR4
,l.ADDITIONAL_CHAR5
,l.CM_APP_TO_TRX_LINE_ID
,l.ADDITIONAL_ID2
,l.ADDITIONAL_ID3
,l.ADDITIONAL_ID4
,l.ADDITIONAL_ID5
,l.XLA_ENTITY_ID
,l.REF_CTLGD_CCID
,ard.code_combination_id
,l.FROM_EXCHANGE_RATE
,l.FROM_EXCHANGE_RATE_TYPE
,l.FROM_EXCHANGE_DATE
,l.FROM_CURRENCY_CODE
,l.TO_CURRENCY_CODE
,'Y'
,l.THIRD_PARTY_ID
,l.THIRD_PARTY_SITE_ID
,l.THIRD_PARTY_TYPE
,'CURR_ROUND'
FROM AR_XLA_LINES_EXTRACT l,
(SELECT
event_id,
source_id,
CM_APP_TO_TRX_LINE_ID,
sum(-1*ACCTD_AMOUNT) ACCTD_AMT,
sum(-1*FROM_ACCTD_AMOUNT) FROM_ACCTD_AMT,
sum(AMOUNT) AMOUNT,
sum(FROM_AMOUNT) FROM_AMOUNT
FROM ar_xla_lines_extract
WHERE POSTING_ENTITY = 'APP'
AND CUSTOMER_TRX_LINE_ID is not null
AND source_type = 'REC'
AND MFAR_ADDITIONAL_ENTRY = 'N'
GROUP BY event_id, source_id, CM_APP_TO_TRX_LINE_ID
HAVING sum(ACCTD_AMOUNT) <> 0 AND sum(AMOUNT) = 0
) curr,
AR_DISTRIBUTIONS_ALL ard
WHERE l.MFAR_ADDITIONAL_ENTRY = 'N'
AND l.FROM_TO_FLAG = 'T'
AND l.LEVEL_FLAG = 'L'
AND l.event_id = curr.event_id
AND l.source_id = curr.source_id
AND l.CUSTOMER_TRX_LINE_ID = curr.CM_APP_TO_TRX_LINE_ID
AND l.source_type = 'REC'
AND l.source_id = ard.source_id
AND ard.source_table = 'RA'
AND ard.source_type = 'CURR_ROUND';
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,CRH_STATUS
,CRH_PRV_STATUS
,AMOUNT
,FROM_AMOUNT
,FROM_ACCTD_AMOUNT
,PREV_FUND_SEG_REPLACE
,APP_CRH_STATUS
,PAIRED_CCID
,PAIRE_DIST_ID
,REF_DIST_CCID
,REF_MF_DIST_FLAG
,ORIGIN_EXTRACT_TABLE
,EVENT_TYPE_CODE
,EVENT_CLASS_CODE
,ENTITY_CODE
,REVERSAL_CODE
,BUSINESS_FLOW_CODE
,TAX_LINE_ID
,ADDITIONAL_CHAR1
,ADDITIONAL_CHAR2
,ADDITIONAL_CHAR3
,ADDITIONAL_CHAR4
,ADDITIONAL_CHAR5
,ADDITIONAL_ID1
,ADDITIONAL_ID2
,ADDITIONAL_ID3
,ADDITIONAL_ID4
,ADDITIONAL_ID5
,XLA_ENTITY_ID
,REF_CTLGD_CCID
,DIST_CCID
,FROM_EXCHANGE_RATE
,FROM_EXCHANGE_RATE_TYPE
,FROM_EXCHANGE_DATE
,FROM_CURRENCY_CODE
,TO_CURRENCY_CODE
,MFAR_ADDITIONAL_ENTRY
,THIRD_PARTY_ID
,THIRD_PARTY_SITE_ID
,THIRD_PARTY_TYPE
,SOURCE_TYPE )
SELECT l.EVENT_ID
,-1 * ar_mfar_extract_s.nextval
,l.LANGUAGE
,l.LEDGER_ID
,l.SOURCE_ID
,l.SOURCE_TABLE
,l.LINE_ID
,l.TAX_CODE_ID
,l.LOCATION_SEGMENT_ID
,l.BASE_CURRENCY_CODE
,l.EXCHANGE_RATE_TYPE
,l.EXCHANGE_RATE
,l.EXCHANGE_DATE
,curr.CURR_ROUND_ACCTD_AMT FROM_ACCTD_AMOUNT -- Currency Rounding Amount
,l.TAXABLE_ACCTD_AMOUNT
,l.ORG_ID
,l.HEADER_TABLE_ID
,l.POSTING_ENTITY
,l.CASH_RECEIPT_ID
,l.CUSTOMER_TRX_ID
,l.CUSTOMER_TRX_LINE_ID
,l.CUST_TRX_LINE_GL_DIST_ID
,l.CUST_TRX_LINE_SALESREP_ID
,l.INVENTORY_ITEM_ID
,l.SALES_TAX_ID
,l.SO_ORGANIZATION_ID
,l.TAX_EXEMPTION_ID
,l.UOM_CODE
,l.WAREHOUSE_ID
,l.AGREEMENT_ID
,l.CUSTOMER_BANK_ACCT_ID
,l.DRAWEE_BANK_ACCOUNT_ID
,l.REMITTANCE_BANK_ACCT_ID
,l.DISTRIBUTION_SET_ID
,l.PAYMENT_SCHEDULE_ID
,l.RECEIPT_METHOD_ID
,l.RECEIVABLES_TRX_ID
,l.ED_ADJ_RECEIVABLES_TRX_ID
,l.UNED_RECEIVABLES_TRX_ID
,l.SET_OF_BOOKS_ID
,l.SALESREP_ID
,l.BILL_SITE_USE_ID
,l.DRAWEE_SITE_USE_ID
,l.PAYING_SITE_USE_ID
,l.SOLD_SITE_USE_ID
,l.SHIP_SITE_USE_ID
,l.RECEIPT_CUSTOMER_SITE_USE_ID
,l.BILL_CUST_ROLE_ID
,l.DRAWEE_CUST_ROLE_ID
,l.SHIP_CUST_ROLE_ID
,l.SOLD_CUST_ROLE_ID
,l.BILL_CUSTOMER_ID
,l.DRAWEE_CUSTOMER_ID
,l.PAYING_CUSTOMER_ID
,l.SOLD_CUSTOMER_ID
,l.SHIP_CUSTOMER_ID
,l.REMIT_ADDRESS_ID
,l.RECEIPT_BATCH_ID
,l.RECEIVABLE_APPLICATION_ID
,l.CUSTOMER_BANK_BRANCH_ID
,l.ISSUER_BANK_BRANCH_ID
,l.BATCH_SOURCE_ID
,l.BATCH_ID
,l.TERM_ID
,l.SELECT_FLAG
,l.LEVEL_FLAG
,l.FROM_TO_FLAG
,l.CRH_STATUS
,l.CRH_PRV_STATUS
,(NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0)) AMOUNT
,(NVL(ard.from_amount_cr,0) - NVL(ard.from_amount_dr,0)) FROM_AMOUNT
,curr.CURR_ROUND_ACCTD_AMT FROM_ACCTD_AMOUNT -- Currency Rounding Amount
,l.PREV_FUND_SEG_REPLACE
,l.APP_CRH_STATUS
,l.PAIRED_CCID
,l.PAIRE_DIST_ID
,l.REF_DIST_CCID
,l.REF_MF_DIST_FLAG
,l.ORIGIN_EXTRACT_TABLE
,l.EVENT_TYPE_CODE
,l.EVENT_CLASS_CODE
,l.ENTITY_CODE
,l.REVERSAL_CODE
,l.BUSINESS_FLOW_CODE
,l.TAX_LINE_ID
,l.ADDITIONAL_CHAR1
,l.ADDITIONAL_CHAR2
,l.ADDITIONAL_CHAR3
,l.ADDITIONAL_CHAR4
,l.ADDITIONAL_CHAR5
,l.ADDITIONAL_ID1
,l.ADDITIONAL_ID2
,l.ADDITIONAL_ID3
,l.ADDITIONAL_ID4
,l.ADDITIONAL_ID5
,l.XLA_ENTITY_ID
,l.REF_CTLGD_CCID
,ard.code_combination_id
,l.FROM_EXCHANGE_RATE
,l.FROM_EXCHANGE_RATE_TYPE
,l.FROM_EXCHANGE_DATE
,l.FROM_CURRENCY_CODE
,l.TO_CURRENCY_CODE
,'Y'
,l.THIRD_PARTY_ID
,l.THIRD_PARTY_SITE_ID
,l.THIRD_PARTY_TYPE
,ard.source_type
FROM AR_XLA_LINES_EXTRACT l,
AR_DISTRIBUTIONS_ALL ard,
(select event_id,
source_id,
LINE_ID,
sum(-1*ACCTD_AMOUNT) CURR_ROUND_ACCTD_AMT
from ar_xla_lines_extract
where POSTING_ENTITY = 'APP'
AND CUSTOMER_TRX_LINE_ID is not null
AND ((MFAR_ADDITIONAL_ENTRY = 'Y')
OR (MFAR_ADDITIONAL_ENTRY = 'N' AND FROM_TO_FLAG = 'T'))
group by event_id, source_id, LINE_ID
having sum(-1*ACCTD_AMOUNT) <> 0) curr
WHERE l.MFAR_ADDITIONAL_ENTRY = 'N'
AND l.FROM_TO_FLAG = 'T'
AND l.CUSTOMER_TRX_LINE_ID IS NOT NULL
AND l.CM_APP_TO_TRX_LINE_ID IS NULL
AND nvl(l.Source_Type, 'XX') <> 'CURR_ROUND'
AND curr.source_id = ard.source_id
AND ard.source_type = 'CURR_ROUND'
AND ard.source_table = 'RA'
AND l.line_id = curr.line_id
AND l.event_id = curr.event_id
AND l.source_id = curr.source_id;
| to insert the MFAR additional lines to the extract table.
| These cursors fetch rows only for on-account CM applications.
+-------------------------------------------------------------------------------+
| History :
| 25-JUL-2010 Ram Manikandan Created due to bug#9860123
| 02-SEP-2010 Ram Manikandan Created due to bug#10047351
+------------------------------------------------------------------------------*/
PROCEDURE mfar_cmapp_from_to is
l_ar_cm_from_tab ar_cm_from_tab;
SELECT xla.entity_id --entity_id
,ra.receivable_application_id --receivable_application_id
,ard.line_id --line_id
,ard.source_type --source_type
,ra.customer_trx_id --customer_trx_id
,NVL(ard.amount_cr,0)-
NVL(ard.amount_dr,0) --amount
,NVL(ard.acctd_amount_cr,0)-
NVL(ard.acctd_amount_dr,0) --acctd_amount
,NVL(sign(ra.amount_applied)*ra.amount_applied,0) --amount_applied_from
,NVL(sign(ra.acctd_amount_applied_from)*ra.acctd_amount_applied_from,0) --acctd_amount_applied_from
,ard.code_combination_id --code_combination_id
,ard.currency_conversion_date --exchange_date
,ard.currency_conversion_rate --exchange_rate
,ard.currency_conversion_type --exchange_type
,ard.third_party_id --third_party_id
,ard.third_party_sub_id --third_party_site_id
,ra.event_id --event_id
,ra.set_of_books_id --ledger_id
,ard.currency_code --currency_code
,ra.org_id --org_id
,sob.currency_code --base currency code
FROM ar_receivable_applications_all ra
,ar_distributions_all ard
,(SELECT entity_id,
source_id_int_1,
event_id,
ledger_id
FROM xla_events_gt
WHERE application_id = 222
AND event_type_code IN ('CM_CREATE','CM_UPDATE')
GROUP BY entity_id,
source_id_int_1,
event_id,
ledger_id) xla
,gl_sets_of_books sob
,ar_xla_lines_extract ext -- 10047351
WHERE xla.source_id_int_1 = ra.customer_trx_id
AND xla.event_id = ra.event_id
AND ra.status = 'APP'
AND ra.receivable_application_id = ard.source_id
AND xla.ledger_id = sob.set_of_books_id
AND ard.source_table = 'RA'
AND ard.source_type = 'REC'
AND ard.source_id = ext.source_id
AND ard.line_id = ext.line_id
AND ext.event_class_code = 'CREDIT_MEMO'
AND ext.cm_app_to_trx_line_id IS NULL
AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS','EDISC','UNEDISC','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX','DEFERRED_TAX','TAX')
AND ( ard.ref_cust_trx_line_gl_dist_id IS NOT NULL
AND ard.ref_cust_trx_line_gl_dist_id IN (SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all ctlgd
WHERE ctlgd.customer_trx_id = ra.customer_trx_id)) -- Select only FROM rows which belong to Credit Memo
ORDER BY ra.receivable_application_id, ard.line_id;
SELECT xla.entity_id --entity_id
,ra.receivable_application_id --receivable_application_id
,ard.line_id --line_id
,NVL(ard.amount_cr,0)-
NVL(ard.amount_dr,0) --amount
,NVL(ard.acctd_amount_cr,0)-
NVL(ard.acctd_amount_dr,0) --acctd_amount
,NVL(ard.from_amount_cr,0)-
NVL(ard.from_amount_dr,0) --from_amount
,NVL(ard.from_acctd_amount_cr,0)-
NVL(ard.from_acctd_amount_dr,0) --from_acctd_amount
,ard.third_party_id --third_party_id
,ard.third_party_sub_id --third_party_site_id
,DECODE(ard.third_party_id,NULL,NULL,'C') --third_party_type
,ard.currency_code -- currency_code
,ard.currency_conversion_rate --exchange_rate
,ard.currency_conversion_type --exchange_type
,ard.currency_conversion_date --exchange_date
,ard.ref_customer_trx_line_id --ref_customer_trx_line_id
,ard.ref_cust_trx_line_gl_dist_id --ref_cust_trx_line_gl_dist_id
,ard.code_combination_id --code_combination_id
,ard.ref_dist_ccid --ref_dist_ccid
,ard.activity_bucket --activity_bucket
,ard.source_type --source_type
,ard.source_table --source_table
,DECODE(ra.posting_control_id,-3,'N','Y') --ra_post_indicator
,ra.applied_customer_trx_id --customer_trx_id
,ctl.inventory_item_id --inventory_item_id
,ctl.sales_tax_id --sales_tax_id
,ctl.tax_line_id --tax_line_id
,ct.bill_to_customer_id --bill_to_customer_id
,ct.bill_to_site_use_id --bill_to_site_use_id
,ct.sold_to_customer_id --sold_to_customer_id
,ct.sold_to_site_use_id --sold_to_site_use_id
,ct.ship_to_customer_id --ship_to_customer_id
,ct.ship_to_site_use_id --ship_to_site_use_id
FROM ar_receivable_applications_all ra
,ar_distributions_all ard
,ra_customer_trx_all ct
,ra_customer_trx_lines_all ctl
,(SELECT entity_id,
source_id_int_1,
event_id
FROM xla_events_gt
WHERE application_id = 222
AND event_type_code IN ('CM_CREATE','CM_UPDATE')
GROUP BY entity_id,
source_id_int_1,
event_id) xla
,ar_xla_lines_extract ext -- 10047351
WHERE xla.source_id_int_1 = ra.customer_trx_id
AND xla.event_id = ra.event_id
AND ra.status = 'APP'
AND ra.receivable_application_id = ard.source_id
AND ard.source_table = 'RA'
AND ard.source_type = 'REC'
AND ard.source_id = ext.source_id
AND ard.line_id = ext.line_id
AND ext.event_class_code = 'CREDIT_MEMO'
AND ext.cm_app_to_trx_line_id IS NULL
AND ra.applied_customer_trx_id = ct.customer_trx_id
AND ct.customer_trx_id = ctl.customer_trx_id
AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id
AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS','EDISC','UNEDISC','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX','DEFERRED_TAX','TAX')
AND ( ard.ref_cust_trx_line_gl_dist_id IS NOT NULL
AND ard.ref_cust_trx_line_gl_dist_id IN (SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all ctlgd
WHERE ctlgd.customer_trx_id = ra.applied_customer_trx_id)) -- Select only TO rows which belong to Invoice
ORDER BY ra.receivable_application_id, (NVL(ard.from_amount_cr,0)-NVL(ard.from_amount_dr,0)); -- Bug 14140214
mfar_cm_app_insert_extract(l_ar_cm_from_tab, l_ar_cm_to_tab);
| Procedure Name : mfar_cm_app_insert_extract
| Description : Pro-rates the CM line amounts across the Invoice line amounts
| irrespective of the funds they belong to and insert the
| MFAR additional lines to the extract table.
+-------------------------------------------------------------------------------+
| History :
| 25-JUL-2010 Ram Manikandan Created due to bug#9860123
+------------------------------------------------------------------------------*/
PROCEDURE mfar_cm_app_insert_extract(p_ar_cm_from_rec IN ar_cm_from_tab, p_ar_cm_to_rec IN OUT NOCOPY ar_cm_to_tab) IS
-- run time variables for proration
x_run_amt number := 0;
local_log(procedure_name => 'mfar_cm_app_insert_extract',
p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_cm_app_insert_extract()+');
local_log(procedure_name => 'mfar_cm_app_insert_extract',
p_msg_text => 'CM Line '||i||' Amount: '||p_ar_cm_from_rec(i).acctd_amount);
local_log(procedure_name => 'mfar_cm_app_insert_extract',
p_msg_text => 'Invoice Line '||j||' Amount: '||p_ar_cm_to_rec(j).from_acctd_amount);
local_log(procedure_name => 'mfar_cm_app_insert_extract',
p_msg_text => 'Prorated Amount: '||x_alloc_acctd_amt);
SELECT ar_mfar_extract_s.nextval INTO l_ar_mfar_extract_s
FROM dual;
l_cm_app_mfar_extract_tab(k).SELECT_FLAG := 'Y';
INSERT INTO ar_xla_lines_extract VALUES l_cm_app_mfar_extract_tab(r);
local_log(procedure_name => 'mfar_cm_app_insert_extract',
p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_cm_app_insert_extract()-');
local_log(procedure_name => 'mfar_cm_app_insert_extract',
p_msg_text => 'EXCEPTION OTHERS in mfar_cm_app_insert_extract '||
arp_global.CRLF || 'Error :'|| SQLERRM);
'Procedure :arp_xla_extract_main_pkg.mfar_cm_app_insert_extract'|| arp_global.CRLF||
'Error :'||SQLERRM);
END mfar_cm_app_insert_extract;
| RECP_UPDATE and RECP_RATE_ADJUST events which are necessary
| for the MFAR CRH building and populates the GT
| table ar_crh_app_gt.
+-------------------------------------------------------------------------------+
| History :
| 25-JUL-2010 Anshu Kaushal Created
+------------------------------------------------------------------------------*/
PROCEDURE mfar_app_dist_cr IS
BEGIN
local_log(procedure_name => 'mfar_app_dist_cr',
p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_app_dist_cr ()+');
INSERT INTO ar_crh_app_gt (
entity_id
,receivable_application_id
,cash_receipt_history_id
,cash_receipt_history_status
,line_id
,amount
,acctd_amount
,from_amount
,from_acctd_amount
,third_party_id
,third_party_site_id
,third_party_type
,from_currency_code
,from_exchange_rate
,from_exchange_type
,from_exchange_date
,to_currency_code
,to_exchange_rate
,to_exchange_type
,to_exchange_date
,ref_customer_trx_line_id
,ref_cust_trx_line_gl_dist_id
,code_combination_id
,ref_code_combination_id
,ref_dist_ccid
,activity_bucket
,source_type
,source_table
,ra_post_indicator
,crh_post_indicator
,customer_trx_id
,inventory_item_id
,sales_tax_id
,tax_line_id
,bill_to_customer_id
,bill_to_site_use_id
,sold_to_customer_id
,sold_to_site_use_id
,ship_to_customer_id
,ship_to_site_use_id
,event_id
,amount_applied
,acctd_amount_applied
,mfar_upg_trx)
SELECT xla.entity_id --entity_id
,ra.receivable_application_id --receivable_application_id
,crh.cash_receipt_history_id --cash_receipt_history_id
,crh.status --cash_receipt_history_status
,ard.line_id --line_id
,NVL(ard.amount_cr,0)-
NVL(ard.amount_dr,0) --amount
,NVL(ard.acctd_amount_cr,0)-
NVL(ard.acctd_amount_dr,0) --acctd_amount
,NVL(ard.from_amount_cr,0)-
NVL(ard.from_amount_dr,0) --from_amount
,NVL(ard.from_acctd_amount_cr,0)-
NVL(ard.from_acctd_amount_dr,0) --from_acctd_amount
,ard.third_party_id --third_party_id
,ard.third_party_sub_id --third_party_site_id
,DECODE(ard.third_party_id,NULL,NULL,'C') --third_party_type
,cr.currency_code --from_currency_code
,crh.exchange_rate --from_exchange_rate
,crh.exchange_rate_type --from_exchange_type
,crh.exchange_date --from_exchange_date
,ct.invoice_currency_code --to_currency_code
,ct.exchange_rate --to_exchange_rate
,ct.exchange_rate_type --to_exchange_type
,ct.exchange_date --to_exchange_date
,ard.ref_customer_trx_line_id --ref_customer_trx_line_id
,ard.ref_cust_trx_line_gl_dist_id --ref_cust_trx_line_gl_dist_id
,ard.code_combination_id --code_combination_id
,ctlgd.code_combination_id --ref_code_combination_id
,ard.ref_dist_ccid --ref_dist_ccid
,ard.activity_bucket --activity_bucket
,ard.source_type --source_type
,source_table --source_table
,DECODE(ra.posting_control_id,-3,'N','Y') --ra_post_indicator
,DECODE(crh.posting_control_id,-3,'N','Y') --crh_post_indicator
,ra.applied_customer_trx_id --customer_trx_id
,ctl.inventory_item_id
,ctl.sales_tax_id
,ctl.tax_line_id
,ct.bill_to_customer_id
,ct.bill_to_site_use_id
,ct.sold_to_customer_id
,ct.sold_to_site_use_id
,ct.ship_to_customer_id
,ct.ship_to_site_use_id
,ra.event_id
,ra.amount_applied
,ra.acctd_amount_applied_from
,decode(ct.upgrade_method,'R12_11ICASH','N','') mfar_upg_trx
FROM ar_cash_receipt_history_all crh
,ar_cash_receipts_all cr
,ar_receivable_applications_all ra
,ar_distributions_all ard
,ra_customer_trx_all ct
,ra_cust_trx_line_gl_dist_all ctlgd
,ra_customer_trx_lines_all ctl
,(SELECT entity_id,
source_id_int_1
FROM xla_events_gt
WHERE application_id = 222
AND event_type_code IN ('RECP_CREATE','RECP_UPDATE','RECP_RATE_ADJUST')
GROUP BY entity_id,
source_id_int_1) xla
WHERE xla.source_id_int_1 = crh.cash_receipt_id
AND crh.cash_receipt_history_id = ra.cash_receipt_history_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.cash_receipt_id = ra.cash_receipt_id
AND ra.status = 'APP'
AND ra.receivable_application_id = ard.source_id
-- Add MFAR UPG impacts
AND DECODE(ra.upgrade_method,
'11I_MFAR_UPG',DECODE(ard.source_table_secondary,'UPMFRAMIAR','Y','N'),
'R12_11ICASH' ,'N',
'11I_R12_POST','N',
'Y') = 'Y'
AND ra.applied_customer_trx_id = ct.customer_trx_id
AND ard.source_table = 'RA'
AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS','EDISC','UNEDISC','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX','DEFERRED_TAX','TAX', 'CURR_ROUND')
AND decode(ard.source_type,'REC',decode(ard.ref_mf_dist_flag,'D','N','Y'),'Y')='Y'
AND ard.ref_customer_trx_line_id = ctl.customer_trx_line_id(+)
AND ard.ref_cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id(+)
ORDER BY ard.line_id;
| Description : Fetches all the CRH distributions for RECP_CREATE, RECP_UPDATE
| MISC_RECP_CREATE, MISC_RECP_UPDATE, RECP_RATE_ADJUST events
| which are necessary for MFAR additional distribution building
| and populates the GT table ar_crh_gt.
+-------------------------------------------------------------------------------+
| History :
| 25-JUL-2010 Anshu Kaushal Created
+------------------------------------------------------------------------------*/
PROCEDURE mfar_crh_dist IS
BEGIN
local_log(procedure_name => 'mfar_crh_dist',
p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_crh_dist ()+');
INSERT INTO ar_crh_gt (
cash_receipt_id ,
cash_receipt_history_id,
source_type ,
posting_control_id ,
amount ,
acctd_amount ,
code_combination_id ,
exchange_date ,
exchange_rate ,
exchange_rate_type ,
third_party_id ,
third_party_sub_id ,
third_party_flag ,
event_id ,
entity_id ,
ledger_id ,
base_currency_code ,
org_id ,
status ,
crh_line_id ,
recp_amount ,
recp_acctd_amount ,
DIST_LINE_STATUS)
SELECT crh.cash_receipt_id
,crh.cash_receipt_history_id
,ard.source_type
,crh.posting_control_id
,NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)
,NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)
,ard.code_combination_id
,crh.exchange_date
,crh.exchange_rate
,crh.exchange_rate_type
,ard.third_party_id
,ard.third_party_sub_id
,DECODE(third_party_id,NULL,'N','Y')
,gt.event_id
,gt.entity_id
,cr.set_of_books_id -- ledger_id
,lg.currency_code
,crh.org_id
,DECODE(ard.source_type,'CASH' ,'CLEARED',
'REMITTANCE' ,'REMITTED',
'CONFIRMATION','CONFIRMED'
,'BANK_CHARGES','BANK_CHARGES'
,'CASH')
-- ,ard.line_id crh_line_id
,crh.cash_receipt_history_id crh_line_id
,(crh.amount+ nvl(crh.factor_discount_amount,0)) recp_amount
,(crh.acctd_amount+ nvl(crh.acctd_factor_discount_amount,0)) recp_acctd_amount
,decode(crh.status , DECODE(ard.source_type,'CASH','CLEARED',
'REMITTANCE','REMITTED',
'CONFIRMATION','CONFIRMED',
'BANK_CHARGES','CLEARED'
,'CASH'), 'ACTUAL','REVERSAL') DIST_LINE_STATUS
FROM xla_events_gt gt,
ar_cash_receipt_history_all crh,
ar_cash_receipts_all cr,
ar_distributions_all ard,
gl_ledgers lg
WHERE gt.application_id = 222
AND gt.event_type_code IN ('RECP_CREATE','RECP_UPDATE','MISC_RECP_CREATE','MISC_RECP_UPDATE','RECP_RATE_ADJUST')
AND gt.event_id = crh.event_id
AND crh.postable_flag = 'Y'
AND crh.cash_receipt_id= cr.cash_receipt_id
AND ard.source_id = crh.cash_receipt_history_id
AND ard.source_table = 'CRH'
AND (
(ard.source_type IN ('CASH','REMITTANCE','CONFIRMATION'))
OR
(ard.source_type ='BANK_CHARGES'
AND crh.factor_discount_amount = ( nvl(ard.amount_dr,0) - nvl(ard.amount_cr,0)))
)
AND cr.set_of_books_id = lg.ledger_id
/*
Whenever user updates the Bank Charges for a posted Receipt, the ar_distributions
table calculates the differential amount and stores the difference
But, for mfar accountin we need to build the detailed distributions with
reversal entries and the new entries.
The below select builds the detailed distributions for the bank charge changes
when the previous distribution does not involve the change in bank charges
*/
UNION ALL
SELECT crh.cash_receipt_id
,crh.cash_receipt_history_id
,pairard.source_type
,crh.posting_control_id
,decode(pairard.source_type, 'BANK_CHARGES',
decode(state.status, 'ORG_DIST',
-1*crh.factor_discount_amount,
-1*paircrh.factor_discount_amount),
NVL(pairard.amount_cr,0)-NVL(pairard.amount_dr,0)) amount
,decode(pairard.source_type, 'BANK_CHARGES',
decode(state.status, 'ORG_DIST',
-1*crh.acctd_factor_discount_amount,
-1*paircrh.acctd_factor_discount_amount),
NVL(pairard.acctd_amount_cr,0)-NVL(pairard.acctd_amount_dr,0)) acctd_amount
,pairard.code_combination_id
,paircrh.exchange_date
,paircrh.exchange_rate
,paircrh.exchange_rate_type
,pairard.third_party_id
,pairard.third_party_sub_id
,DECODE(pairard.third_party_id,NULL,'N','Y')
,gt.event_id
,gt.entity_id
,cr.set_of_books_id -- ledger_id
,lg.currency_code
,paircrh.org_id
,DECODE(pairard.source_type,'CASH' ,'CLEARED',
'REMITTANCE' ,'REMITTED',
'CONFIRMATION','CONFIRMED'
,'BANK_CHARGES','BANK_CHARGES'
,'CASH')
,decode(state.status, 'ORG_DIST', paircrh.cash_receipt_history_id, -1*paircrh.cash_receipt_history_id) crh_line_id
,decode(state.status, 'ORG_DIST', (crh.amount+ nvl(crh.factor_discount_amount,0))
, 'REV_DIST', (paircrh.amount+ nvl(paircrh.factor_discount_amount,0))) recp_amount
,decode(state.status, 'ORG_DIST',(crh.acctd_amount+ nvl(crh.acctd_factor_discount_amount,0))
, 'REV_DIST',(paircrh.acctd_amount+ nvl(paircrh.acctd_factor_discount_amount,0))) recp_acctd_amount
, decode(state.status, 'ORG_DIST','ACTUAL','REVERSAL') DIST_LINE_STATUS
FROM xla_events_gt gt,
ar_cash_receipt_history_all crh,
ar_cash_receipts_all cr,
ar_distributions_all ard,
ar_cash_receipt_history_all paircrh,
ar_distributions_all pairard,
gl_ledgers lg,
(SELECT 'ORG_DIST' AS status FROM DUAL UNION
SELECT 'REV_DIST' AS status FROM DUAL) state
WHERE gt.application_id = 222
AND gt.event_type_code IN ('RECP_CREATE','RECP_UPDATE','MISC_RECP_CREATE','MISC_RECP_UPDATE','RECP_RATE_ADJUST')
AND gt.event_id = crh.event_id
AND crh.postable_flag = 'Y'
AND crh.cash_receipt_id= cr.cash_receipt_id
AND ard.source_id = crh.cash_receipt_history_id
AND ard.source_table = 'CRH'
AND ard.source_type = 'BANK_CHARGES'
AND paircrh.reversal_cash_receipt_hist_id = crh.cash_receipt_history_id
AND paircrh.cash_receipt_history_id = pairard.source_id
AND pairard.source_table = 'CRH'
AND crh.factor_discount_amount <> ( nvl(ard.amount_dr,0) - nvl(ard.amount_cr,0) )
AND cr.set_of_books_id = lg.ledger_id
UNION ALL
/*
Build CASH and REMITTANCE Records
when ARD's Bank Charge Record is created as difference amount,
and previous CRH state also exists with a differential Bank Charge Amount
That means customer has updated the bank charges more than once consecutively
in receipt history.
*/
SELECT crh.cash_receipt_id
,crh.cash_receipt_history_id
,decode(paircrh.status, 'CLEARED', 'CASH'
, 'REMITTED', 'REMITTACE'
,'CONFIRMED','CONFIRMATION'
,'CASH')
,crh.posting_control_id
,decode(state.status, 'ORG_DIST',
-1*crh.amount,
-1*paircrh.amount) amount
,decode(state.status, 'ORG_DIST',
-1*crh.acctd_amount,
-1*paircrh.acctd_amount) acctd_amount
,pairard.code_combination_id
,paircrh.exchange_date
,paircrh.exchange_rate
,paircrh.exchange_rate_type
,pairard.third_party_id
,pairard.third_party_sub_id
,DECODE(pairard.third_party_id,NULL,'N','Y')
,gt.event_id
,gt.entity_id
,cr.set_of_books_id -- ledger_id
,lg.currency_code
,paircrh.org_id
, paircrh.status
,decode(state.status, 'ORG_DIST', paircrh.cash_receipt_history_id, -1*paircrh.cash_receipt_history_id) crh_line_id
,decode(state.status, 'ORG_DIST', (crh.amount+ nvl(crh.factor_discount_amount,0))
, 'REV_DIST', (paircrh.amount+ nvl(paircrh.factor_discount_amount,0))) recp_amount
,decode(state.status, 'ORG_DIST',(crh.acctd_amount+ nvl(crh.acctd_factor_discount_amount,0))
, 'REV_DIST',(paircrh.acctd_amount+ nvl(paircrh.acctd_factor_discount_amount,0))) recp_acctd_amount
, decode(state.status, 'ORG_DIST','ACTUAL','REVERSAL') DIST_LINE_STATUS
FROM xla_events_gt gt,
ar_cash_receipt_history_all crh,
ar_cash_receipts_all cr,
ar_distributions_all ard,
ar_cash_receipt_history_all paircrh,
ar_distributions_all pairard,
gl_ledgers lg,
(SELECT 'ORG_DIST' AS status FROM DUAL UNION
SELECT 'REV_DIST' AS status FROM DUAL) state
WHERE gt.application_id = 222
AND gt.event_type_code IN ('RECP_CREATE','RECP_UPDATE','MISC_RECP_CREATE','MISC_RECP_UPDATE','RECP_RATE_ADJUST')
AND gt.event_id = crh.event_id
AND crh.postable_flag = 'Y'
AND crh.cash_receipt_id= cr.cash_receipt_id
AND ard.source_id = crh.cash_receipt_history_id
AND ard.source_table = 'CRH'
AND ard.source_type = 'BANK_CHARGES'
AND pairard.source_type = ard.source_type
AND paircrh.reversal_cash_receipt_hist_id = crh.cash_receipt_history_id
AND paircrh.cash_receipt_history_id = pairard.source_id
AND pairard.source_table = 'CRH'
AND crh.factor_discount_amount <> ( nvl(ard.amount_dr,0) - nvl(ard.amount_cr,0) )
AND paircrh.factor_discount_amount <> ( nvl(pairard.amount_dr,0) - nvl(pairard.amount_cr,0) )
AND cr.set_of_books_id = lg.ledger_id;
| Calls the procedure to insert into the extract table after
| building the CRH MFAR distributions.
+-------------------------------------------------------------------------------+
| History :
| 25-JUL-2010 Anshu Kaushal Created
+------------------------------------------------------------------------------*/
PROCEDURE mfar_produit_app_by_crh IS
--
-- Build the MFAR distribution for CRH
--
CURSOR mfar_extract_cur IS
SELECT
ordered_crh_by_app.*
,-1 * ar_mfar_extract_s.nextval LINE_NUMBER
FROM (SELECT
crh.event_id event_id
-- ,-1 * ar_mfar_extract_s.nextval LINE_NUMBER
,'Y' MFAR_ADDITIONAL_ENTRY
,crh.ledger_id LEDGER_ID
,crh.base_currency_code BASE_CURRENCY_CODE
,crh.org_id ORG_ID
,decode(crh.dist_line_status, 'REVERSAL', -1*app.line_id, app.line_id) LINE_ID
,app.receivable_application_id SOURCE_ID
,'RA' SOURCE_TABLE
,crh.cash_receipt_id HEADER_TABLE_ID
,'RECEIPT_HISTORY' POSTING_ENTITY
,crh.entity_id xla_entity_id
--
,app.code_combination_id DIST_CCID
,app.ref_dist_ccid ref_dist_ccid
,app.ref_code_combination_id REF_CTLGD_CCID
--
,app.from_currency_code from_currency_code
,app.from_exchange_rate from_exchange_rate
,app.from_exchange_type FROM_EXCHANGE_RATE_TYPE
,app.from_exchange_date from_exchange_date
,decode(event_type_code, 'RECP_RATE_ADJUST',-app.from_amount,sign(crh.amount)*app.from_amount) from_amount
,decode(event_type_code, 'RECP_RATE_ADJUST',-app.from_acctd_amount,sign(crh.acctd_amount)*app.from_acctd_amount) from_acctd_amount
--
,app.to_currency_code to_currency_code
,app.to_exchange_rate exchange_rate
,app.to_exchange_type EXCHANGE_RATE_TYPE
,app.to_exchange_date EXCHANGE_DATE
,decode(event_type_code, 'RECP_RATE_ADJUST',-app.amount,sign(crh.amount)*app.amount) amount
,decode(event_type_code, 'RECP_RATE_ADJUST',-app.acctd_amount,sign(crh.acctd_amount)*app.acctd_amount) acctd_amount
--
,app.receivable_application_id RECEIVABLE_APPLICATION_ID
,crh.cash_receipt_id CASH_RECEIPT_ID
,app.customer_trx_id CUSTOMER_TRX_ID
,app.ref_customer_trx_line_id CUSTOMER_TRX_LINE_ID
,app.ref_cust_trx_line_gl_dist_id CUST_TRX_LINE_GL_DIST_ID
--
,app.inventory_item_id INVENTORY_ITEM_ID
,app.sales_tax_id SALES_TAX_ID
,crh.ledger_id SET_OF_BOOKS_ID
,app.bill_to_site_use_id BILL_SITE_USE_ID
,app.sold_to_site_use_id SOLD_SITE_USE_ID
,app.ship_to_site_use_id SHIP_SITE_USE_ID
,app.bill_to_customer_id BILL_CUSTOMER_ID
,app.sold_to_customer_id SOLD_CUSTOMER_ID
,app.ship_to_customer_id SHIP_CUSTOMER_ID
,app.tax_line_id TAX_LINE_ID
--
,'Y' SELECT_FLAG
,'L' LEVEL_FLAG
,'T' FROM_TO_FLAG
,crh.status CRH_STATUS
,app.cash_receipt_history_status APP_CRH_STATUS
--
,gt.event_type_code EVENT_TYPE_CODE
,gt.event_class_code EVENT_CLASS_CODE
,gt.entity_code ENTITY_CODE
--
,app.third_party_id third_party_id
,app.third_party_site_id third_party_site_id
,app.third_party_type third_party_type
,app.source_type source_type
,crh.recp_amount recp_amount
,crh.recp_acctd_amount recp_acctd_amount
,decode(crh.DIST_LINE_STATUS,'REVERSAL', crh.amount, -1*(crh.amount)) crh_amount
,decode(crh.DIST_LINE_STATUS,'REVERSAL', crh.acctd_amount, -1*(crh.acctd_amount)) crh_acctd_amount
,crh.crh_line_id CRH_RECORD_ID
,decode(crh.recp_amount, 0 , 0 , decode(crh.source_type, 'BANK_CHARGES',
ar_unposted_item_util.currRound(app.amount_applied-
((decode(crh.DIST_LINE_STATUS,'REVERSAL', crh.recp_amount-crh.amount, crh.recp_amount+crh.amount)/crh.recp_amount)
* app.amount_applied)
,app.from_currency_code),
ar_unposted_item_util.currRound((decode(crh.DIST_LINE_STATUS,'REVERSAL', crh.amount, -1*(crh.amount))/crh.recp_amount) * app.amount_applied,app.from_currency_code))) ALLOC_AMOUNT
,decode(crh.recp_acctd_amount, 0 , 0 , decode(crh.source_type, 'BANK_CHARGES',
ar_unposted_item_util.currRound(app.acctd_amount_applied-
((decode(crh.DIST_LINE_STATUS,'REVERSAL', crh.recp_acctd_amount-crh.acctd_amount, crh.recp_acctd_amount+crh.acctd_amount)/crh.recp_acctd_amount)
* app.acctd_amount_applied)
,app.from_currency_code),
ar_unposted_item_util.currRound((decode(crh.DIST_LINE_STATUS,'REVERSAL', crh.acctd_amount, -1*(crh.acctd_amount))/crh.recp_acctd_amount) * app.acctd_amount_applied
,app.from_currency_code))) ALLOC_ACCTD_AMOUNT
,decode(crh.dist_line_status, 'REVERSAL', -1*app_max_line.max_line_id, app_max_line.max_line_id)
,app.mfar_upg_trx
FROM ar_crh_gt crh,
ar_crh_app_gt app,
xla_events_gt gt,
(SELECT app1.receivable_application_id receivable_application_id,
MAX(app1.line_id) max_line_id
FROM ar_crh_app_gt app1
GROUP BY app1.receivable_application_id) app_max_line
WHERE crh.entity_id = app.entity_id
AND app.source_table = 'RA'
and app.receivable_application_id = app_max_line.receivable_application_id
/* Start fix for Bug 9644866 */
AND decode (crh.cash_receipt_history_id,
app.cash_receipt_history_id,
decode(app.event_id,
crh.event_id, 'Y'
,'N')
,'Y') = 'Y'
/* End fix for Bug 9644866 */
AND crh.event_id = gt.event_id
AND ((gt.event_type_code <> 'RECP_RATE_ADJUST' AND crh.DIST_LINE_STATUS = 'ACTUAL' AND crh.cash_receipt_history_id >= app.cash_receipt_history_id)
OR
(gt.event_type_code <> 'RECP_RATE_ADJUST' AND crh.DIST_LINE_STATUS = 'REVERSAL' AND crh.cash_receipt_history_id > app.cash_receipt_history_id)
OR
(gt.event_type_code = 'RECP_RATE_ADJUST' AND crh.cash_receipt_history_id = app.cash_receipt_history_id)
)
order by crh.cash_receipt_id, crh.dist_line_status, app.line_id, CRH_RECORD_ID) ordered_crh_by_app;
mfar_insert_crh_extract (l_crh_mfar_extract_record);
| Calls the procedure to insert into the extract after building
| the additional distributions.
+-------------------------------------------------------------------------------+
| History :
| 25-JUL-2010 Anshu Kaushal Created
+------------------------------------------------------------------------------*/
PROCEDURE mfar_get_ra IS
CURSOR mfar_extract_app_cur IS
Select
ordered_crh.*
,-1 * ar_mfar_extract_s.NEXTVAL LINE_NUMBER
FROM
(SELECT
gt.event_id EVENT_ID
-- ,-1 * ar_mfar_extract_s.NEXTVAL LINE_NUMBER
,'Y' MFAR_ADDITIONAL_ENTRY
,trx.set_of_books_id LEDGER_ID
,lg.currency_code BASE_CURRENCY_CODE
,ra.org_id ORG_ID
,ard.line_id LINE_ID
,ra.receivable_application_id SOURCE_ID
,'RA' SOURCE_TABLE
,ra.cash_receipt_id HEADER_TABLE_ID
,'APPLICATION' POSTING_ENTITY
,gt.entity_id XLA_ENTITY_ID
--
,ard.code_combination_id DIST_CCID
,ard.ref_dist_ccid REF_DIST_CCID
,ctlgd.code_Combination_id REF_CTLGD_CCID
--
,cr.currency_code FROM_CURRENCY_CODE
,crh.exchange_rate FROM_EXCHANGE_RATE
,crh.exchange_rate_type FROM_EXCHANGE_RATE_TYPE
,crh.exchange_date FROM_EXCHANGE_DATE
,-1 * (NVL(ard.from_amount_cr,0)-NVL(ard.from_amount_dr,0)) FROM_AMOUNT
,-1 * (NVL(ard.from_acctd_amount_cr,0)-NVL(ard.from_acctd_amount_dr,0)) FROM_ACCTD_AMOUNT
--
,trx.invoice_currency_code TO_CURRENCY_CODE
,trx.exchange_rate EXCHANGE_RATE
,trx.exchange_rate_type EXCHANGE_RATE_TYPE
,trx.exchange_date EXCHANGE_DATE
,-1 * (NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)) AMOUNT
,-1 * (NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)) ACCTD_AMOUNT
--
,ra.receivable_application_id RECEIVABLE_APPLICATION_ID
,ra.cash_receipt_id CASH_RECEIPT_ID
,ra.applied_customer_trx_id CUSTOMER_TRX_ID
,ard.ref_customer_trx_line_id CUSTOMER_TRX_LINE_ID
,ard.ref_cust_trx_line_gl_dist_id CUST_TRX_LINE_GL_DIST_ID
--
,ctl.inventory_item_id INVENTORY_ITEM_ID
,ctl.sales_tax_id SALES_TAX_ID
,trx.set_of_books_id SET_OF_BOOKS_ID
,trx.bill_to_site_use_id BILL_SITE_USE_ID
,trx.sold_to_site_use_id SOLD_SITE_USE_ID
,trx.ship_to_site_use_id SHIP_SITE_USE_ID
,trx.bill_to_customer_id BILL_CUSTOMER_ID
,trx.sold_to_customer_id SOLD_CUSTOMER_ID
,trx.ship_to_customer_id SHIP_CUSTOMER_ID
,ctl.tax_line_id TAX_LINE_ID
--
,'Y' SELECT_FLAG
,'L' LEVEL_FLAG
,'T' FROM_TO_FLAG
,crhlatest.status CRH_STATUS
,crh.status APP_CRH_STATUS
--
,gt.event_type_code EVENT_TYPE_CODE
,gt.event_class_code EVENT_CLASS_CODE
,gt.entity_code ENTITY_CODE
--
,ard.third_party_id third_party_id
,ard.third_party_sub_id third_party_site_id
,DECODE(ard.third_party_id,NULL,NULL,'C') third_party_type
,ard.source_type source_type
,abs(crhlatest.amount+ nvl(crhlatest.factor_discount_amount,0)) RECP_AMOUNT
,abs(crhlatest.acctd_amount+ nvl(crhlatest.acctd_factor_discount_amount,0)) RECP_ACCTD_AMOUNT
,abs(crhlatest.amount) crh_amount
,abs(crhlatest.acctd_amount) crh_acctd_amount
,crhlatest.cash_receipt_history_id CRH_RECORD_ID
,decode(crhlatest.amount+ nvl(crhlatest.factor_discount_amount,0),0,0,
ar_unposted_item_util.currRound((abs(crhlatest.amount)/abs(crhlatest.amount+ nvl(crhlatest.factor_discount_amount,0)))*ra.amount_applied,cr.currency_code)) ALLOC_AMOUNT
,decode(crhlatest.acctd_amount+ nvl(crhlatest.acctd_factor_discount_amount,0),0,0,
ar_unposted_item_util.currRound((abs(crhlatest.acctd_amount)/abs(crhlatest.acctd_amount+ nvl(crhlatest.acctd_factor_discount_amount,0)))*ra.acctd_amount_applied_from,cr.currency_code)) ALLOC_ACCTD_AMOUNT
,ext.max_line_id MAX_LINE_ID
,decode(trx.upgrade_method,'R12_11ICASH','N','') mfar_upg_trx
FROM xla_events_gt gt,
ar_receivable_applications_all ra,
ar_cash_receipt_history_all crh,
ar_distributions_all ard,
ra_customer_trx_all trx,
ra_cust_trx_line_gl_dist_all ctlgd,
ra_customer_trx_lines_all ctl,
ar_cash_receipts_all cr,
gl_ledgers lg,
ar_cash_receipt_history_all crhlatest,
(SELECT source_id,
MAX(line_id) max_line_id
FROM ar_xla_lines_extract
WHERE source_table='RA'
AND posting_entity='APP'
AND event_class_code='RECEIPT'
AND customer_trx_line_id IS NOT NULL
GROUP BY source_id) ext
WHERE gt.application_id = 222
AND gt.event_type_code IN ('RECP_CREATE','RECP_UPDATE')
AND gt.event_id = ra.event_id
AND ra.status = 'APP'
AND ard.source_id = ra.receivable_application_id
AND ard.source_table = 'RA'
AND nvl(ard.REF_MF_DIST_FLAG, 'Z') <> 'U'
AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS')
AND ra.cash_receipt_history_id = crh.cash_receipt_history_id
AND ra.cash_receipt_id = cr.cash_receipt_id
AND trx.set_of_books_id = lg.ledger_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.posting_control_id <> -3
AND crh.cash_receipt_id = crhlatest.cash_receipt_id
AND ard.ref_cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id(+)
AND ard.ref_customer_trx_line_id = ctl.customer_trx_line_id(+)
AND ra.applied_customer_trx_id = trx.customer_trx_id
AND ra.receivable_application_id = ext.source_id
AND crhlatest.cash_receipt_id = cr.cash_receipt_id
AND crhlatest.cash_receipt_history_id =
( SELECT MAX(a.cash_receipt_history_id)
FROM ar_cash_receipt_history_all a
WHERE a.cash_receipt_id = cr.cash_receipt_id
AND posting_control_id <> -3)
UNION ALL
SELECT
gt.event_id EVENT_ID
-- ,-1 * ar_mfar_extract_s.NEXTVAL LINE_NUMBER
,'Y' MFAR_ADDITIONAL_ENTRY
,trx.set_of_books_id LEDGER_ID
,lg.currency_code BASE_CURRENCY_CODE
,ra.org_id ORG_ID
,ard.line_id LINE_ID
,ra.receivable_application_id SOURCE_ID
,'RA' SOURCE_TABLE
,ra.cash_receipt_id HEADER_TABLE_ID
,'APPLICATION' POSTING_ENTITY
,gt.entity_id XLA_ENTITY_ID
--
,ard.code_combination_id DIST_CCID
,ard.ref_dist_ccid REF_DIST_CCID
,ctlgd.code_Combination_id REF_CTLGD_CCID
--
,cr.currency_code FROM_CURRENCY_CODE
,crh.exchange_rate FROM_EXCHANGE_RATE
,crh.exchange_rate_type FROM_EXCHANGE_RATE_TYPE
,crh.exchange_date FROM_EXCHANGE_DATE
,-1 * (NVL(ard.from_amount_cr,0)-NVL(ard.from_amount_dr,0)) FROM_AMOUNT
,-1 * (NVL(ard.from_acctd_amount_cr,0)-NVL(ard.from_acctd_amount_dr,0)) FROM_ACCTD_AMOUNT
--
,trx.invoice_currency_code TO_CURRENCY_CODE
,trx.exchange_rate EXCHANGE_RATE
,trx.exchange_rate_type EXCHANGE_RATE_TYPE
,trx.exchange_date EXCHANGE_DATE
,-1 * (NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)) AMOUNT
,-1 * (NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)) ACCTD_AMOUNT
--
,ra.receivable_application_id RECEIVABLE_APPLICATION_ID
,ra.cash_receipt_id CASH_RECEIPT_ID
,ra.applied_customer_trx_id CUSTOMER_TRX_ID
,ard.ref_customer_trx_line_id CUSTOMER_TRX_LINE_ID
,ard.ref_cust_trx_line_gl_dist_id CUST_TRX_LINE_GL_DIST_ID
--
,ctl.inventory_item_id INVENTORY_ITEM_ID
,ctl.sales_tax_id SALES_TAX_ID
,trx.set_of_books_id SET_OF_BOOKS_ID
,trx.bill_to_site_use_id BILL_SITE_USE_ID
,trx.sold_to_site_use_id SOLD_SITE_USE_ID
,trx.ship_to_site_use_id SHIP_SITE_USE_ID
,trx.bill_to_customer_id BILL_CUSTOMER_ID
,trx.sold_to_customer_id SOLD_CUSTOMER_ID
,trx.ship_to_customer_id SHIP_CUSTOMER_ID
,ctl.tax_line_id TAX_LINE_ID
--
,'Y' SELECT_FLAG
,'L' LEVEL_FLAG
,'T' FROM_TO_FLAG
,crh.status CRH_STATUS
,crh.status APP_CRH_STATUS
--
,gt.event_type_code EVENT_TYPE_CODE
,gt.event_class_code EVENT_CLASS_CODE
,gt.entity_code ENTITY_CODE
--
,ard.third_party_id third_party_id
,ard.third_party_sub_id third_party_site_id
,DECODE(ard.third_party_id,NULL,NULL,'C') third_party_type
,ard.source_type source_type
,abs(crh.amount+ nvl(crh.factor_discount_amount,0)) RECP_AMOUNT
,abs(crh.acctd_amount+ nvl(crh.acctd_factor_discount_amount,0)) RECP_ACCTD_AMOUNT
,abs(crh.amount) crh_amount
,abs(crh.acctd_amount) crh_acctd_amount
,crh.cash_receipt_history_id CRH_RECORD_ID
,decode(crh.amount+ nvl(crh.factor_discount_amount,0),0,0,
ar_unposted_item_util.currRound((abs(crh.amount)/abs(crh.amount+ nvl(crh.factor_discount_amount,0)))*ra.amount_applied,cr.currency_code)) ALLOC_AMOUNT
,decode(crh.acctd_amount+ nvl(crh.acctd_factor_discount_amount,0),0,0,
ar_unposted_item_util.currRound((abs(crh.acctd_amount)/abs(crh.acctd_amount+ nvl(crh.acctd_factor_discount_amount,0)))*ra.acctd_amount_applied_from,cr.currency_code)) ALLOC_ACCTD_AMOUNT
,ext.max_line_id MAX_LINE_ID
,decode(trx.upgrade_method,'R12_11ICASH','N','') mfar_upg_trx
FROM xla_events_gt gt,
ar_receivable_applications_all ra,
ar_cash_receipt_history_all crh,
ar_distributions_all ard,
ra_customer_trx_all trx,
ra_cust_trx_line_gl_dist_all ctlgd,
ra_customer_trx_lines_all ctl,
ar_cash_receipts_all cr,
gl_ledgers lg,
(SELECT source_id,
MAX(line_id) max_line_id
FROM ar_xla_lines_extract
WHERE source_table='RA'
AND posting_entity='APP'
AND event_class_code='RECEIPT'
AND customer_trx_line_id IS NOT NULL
GROUP BY source_id) ext
WHERE gt.application_id = 222
AND gt.event_type_code IN ('RECP_CREATE','RECP_UPDATE')
AND gt.event_id = ra.event_id
AND ra.status = 'APP'
AND ard.source_id = ra.receivable_application_id
AND ard.source_table = 'RA'
AND ra.receivable_application_id = ext.source_id
AND nvl(ard.REF_MF_DIST_FLAG, 'Z') <> 'U'
AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS')
AND ra.cash_receipt_history_id = crh.cash_receipt_history_id
AND ra.cash_receipt_id = cr.cash_receipt_id
AND trx.set_of_books_id = lg.ledger_id
AND crh.cash_receipt_id = cr.cash_receipt_id
/* Start fix for Bug 9644866 */
AND crh.posting_control_id = -3
AND ra.cash_receipt_history_id = crh.cash_receipt_history_id
AND ra.event_id <> crh.event_id
/* End fix for Bug 9644866 */
AND ard.ref_cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id(+)
AND ard.ref_customer_trx_line_id = ctl.customer_trx_line_id(+)
AND ra.applied_customer_trx_id = trx.customer_trx_id
UNION ALL
SELECT
gt.event_id EVENT_ID
-- ,-1 * ar_mfar_extract_s.NEXTVAL LINE_NUMBER
,'Y' MFAR_ADDITIONAL_ENTRY
,trx.set_of_books_id LEDGER_ID
,lg.currency_code BASE_CURRENCY_CODE
,ra.org_id ORG_ID
,ard.line_id LINE_ID
,ra.receivable_application_id SOURCE_ID
,'RA' SOURCE_TABLE
,ra.cash_receipt_id HEADER_TABLE_ID
,'APPLICATION' POSTING_ENTITY
,gt.entity_id XLA_ENTITY_ID
--
,ard.code_combination_id DIST_CCID
,ard.ref_dist_ccid REF_DIST_CCID
,ctlgd.code_Combination_id REF_CTLGD_CCID
--
,cr.currency_code FROM_CURRENCY_CODE
,crh.exchange_rate FROM_EXCHANGE_RATE
,crh.exchange_rate_type FROM_EXCHANGE_RATE_TYPE
,crh.exchange_date FROM_EXCHANGE_DATE
,-1 * (NVL(ard.from_amount_cr,0)-NVL(ard.from_amount_dr,0)) FROM_AMOUNT
,-1 * (NVL(ard.from_acctd_amount_cr,0)-NVL(ard.from_acctd_amount_dr,0)) FROM_ACCTD_AMOUNT
--
,trx.invoice_currency_code TO_CURRENCY_CODE
,trx.exchange_rate EXCHANGE_RATE
,trx.exchange_rate_type EXCHANGE_RATE_TYPE
,trx.exchange_date EXCHANGE_DATE
,-1 * (NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)) AMOUNT
,-1 * (NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)) ACCTD_AMOUNT
--
,ra.receivable_application_id RECEIVABLE_APPLICATION_ID
,ra.cash_receipt_id CASH_RECEIPT_ID
,ra.applied_customer_trx_id CUSTOMER_TRX_ID
,ard.ref_customer_trx_line_id CUSTOMER_TRX_LINE_ID
,ard.ref_cust_trx_line_gl_dist_id CUST_TRX_LINE_GL_DIST_ID
--
,ctl.inventory_item_id INVENTORY_ITEM_ID
,ctl.sales_tax_id SALES_TAX_ID
,trx.set_of_books_id SET_OF_BOOKS_ID
,trx.bill_to_site_use_id BILL_SITE_USE_ID
,trx.sold_to_site_use_id SOLD_SITE_USE_ID
,trx.ship_to_site_use_id SHIP_SITE_USE_ID
,trx.bill_to_customer_id BILL_CUSTOMER_ID
,trx.sold_to_customer_id SOLD_CUSTOMER_ID
,trx.ship_to_customer_id SHIP_CUSTOMER_ID
,ctl.tax_line_id TAX_LINE_ID
--
,'Y' SELECT_FLAG
,'L' LEVEL_FLAG
,'T' FROM_TO_FLAG
,'BANK_CHARGES' CRH_STATUS
,crh.status APP_CRH_STATUS
--
,gt.event_type_code EVENT_TYPE_CODE
,gt.event_class_code EVENT_CLASS_CODE
,gt.entity_code ENTITY_CODE
--
,ard.third_party_id third_party_id
,ard.third_party_sub_id third_party_site_id
,DECODE(ard.third_party_id,NULL,NULL,'C') third_party_type
,ard.source_type source_type
,abs(crhlatest.amount+ nvl(crhlatest.factor_discount_amount,0)) RECP_AMOUNT
,abs(crhlatest.acctd_amount+ nvl(crhlatest.acctd_factor_discount_amount,0)) RECP_ACCTD_AMOUNT
,abs(crhlatest.factor_discount_amount) crh_amount
,abs(crhlatest.acctd_factor_discount_amount) crh_acctd_amount
,crhlatest.cash_receipt_history_id CRH_RECORD_ID
,decode(crhlatest.amount+ nvl(crhlatest.factor_discount_amount,0),0,0,
ar_unposted_item_util.currRound(ra.amount_applied-((abs(crhlatest.amount)/abs(crhlatest.amount+ nvl(crhlatest.factor_discount_amount,0)))*ra.amount_applied),cr.currency_code)) ALLOC_AMOUNT
,decode(crhlatest.acctd_amount+ nvl(crhlatest.acctd_factor_discount_amount,0),0,0,
ar_unposted_item_util.currRound(ra.acctd_amount_applied_from-((abs(crhlatest.acctd_amount)/abs(crhlatest.acctd_amount+ nvl(crhlatest.acctd_factor_discount_amount,0)))*ra.acctd_amount_applied_from),cr.currency_code)) ALLOC_ACCTD_AMOUNT
,ext.max_line_id MAX_LINE_ID
,decode(trx.upgrade_method,'R12_11ICASH','N','') mfar_upg_trx
FROM xla_events_gt gt,
ar_receivable_applications_all ra,
ar_cash_receipt_history_all crh,
ar_distributions_all ard,
ra_customer_trx_all trx,
ra_cust_trx_line_gl_dist_all ctlgd,
ra_customer_trx_lines_all ctl,
ar_cash_receipts_all cr,
gl_ledgers lg,
ar_cash_receipt_history_all crhlatest,
(SELECT source_id,
MAX(line_id) max_line_id
FROM ar_xla_lines_extract
WHERE source_table='RA'
AND posting_entity='APP'
AND event_class_code='RECEIPT'
AND customer_trx_line_id IS NOT NULL
GROUP BY source_id) ext
WHERE gt.application_id = 222
AND gt.event_type_code IN ('RECP_CREATE','RECP_UPDATE')
AND gt.event_id = ra.event_id
AND ra.status = 'APP'
AND ard.source_id = ra.receivable_application_id
AND ard.source_table = 'RA'
AND ra.receivable_application_id = ext.source_id
AND nvl(ard.REF_MF_DIST_FLAG, 'Z') <> 'U'
AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS')
AND ra.cash_receipt_history_id = crh.cash_receipt_history_id
AND ra.cash_receipt_id = cr.cash_receipt_id
AND trx.set_of_books_id = lg.ledger_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.posting_control_id <> -3
AND crh.cash_receipt_id = crhlatest.cash_receipt_id
AND ard.ref_cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id(+)
AND ard.ref_customer_trx_line_id = ctl.customer_trx_line_id(+)
AND ra.applied_customer_trx_id = trx.customer_trx_id
AND crhlatest.cash_receipt_id = cr.cash_receipt_id
AND crhlatest.cash_receipt_history_id =
( SELECT MAX(a.cash_receipt_history_id)
FROM ar_cash_receipt_history_all a
WHERE a.cash_receipt_id = cr.cash_receipt_id
AND a.posting_control_id <> -3)
AND crhlatest.status = 'CLEARED'
AND NVL(crhlatest.factor_discount_amount,0) <> 0
AND NVL(crhlatest.acctd_factor_discount_amount,0) <> 0
UNION ALL
SELECT
gt.event_id EVENT_ID
-- ,-1 * ar_mfar_extract_s.NEXTVAL LINE_NUMBER
,'Y' MFAR_ADDITIONAL_ENTRY
,trx.set_of_books_id LEDGER_ID
,lg.currency_code BASE_CURRENCY_CODE
,ra.org_id ORG_ID
,ard.line_id LINE_ID
,ra.receivable_application_id SOURCE_ID
,'RA' SOURCE_TABLE
,ra.cash_receipt_id HEADER_TABLE_ID
,'APPLICATION' POSTING_ENTITY
,gt.entity_id XLA_ENTITY_ID
--
,ard.code_combination_id DIST_CCID
,ard.ref_dist_ccid REF_DIST_CCID
,ctlgd.code_Combination_id REF_CTLGD_CCID
--
,cr.currency_code FROM_CURRENCY_CODE
,crh.exchange_rate FROM_EXCHANGE_RATE
,crh.exchange_rate_type FROM_EXCHANGE_RATE_TYPE
,crh.exchange_date FROM_EXCHANGE_DATE
,-1 * (NVL(ard.from_amount_cr,0)-NVL(ard.from_amount_dr,0)) FROM_AMOUNT
,-1 * (NVL(ard.from_acctd_amount_cr,0)-NVL(ard.from_acctd_amount_dr,0)) FROM_ACCTD_AMOUNT
--
,trx.invoice_currency_code TO_CURRENCY_CODE
,trx.exchange_rate EXCHANGE_RATE
,trx.exchange_rate_type EXCHANGE_RATE_TYPE
,trx.exchange_date EXCHANGE_DATE
,-1 * (NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)) AMOUNT
,-1 * (NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)) ACCTD_AMOUNT
--
,ra.receivable_application_id RECEIVABLE_APPLICATION_ID
,ra.cash_receipt_id CASH_RECEIPT_ID
,ra.applied_customer_trx_id CUSTOMER_TRX_ID
,ard.ref_customer_trx_line_id CUSTOMER_TRX_LINE_ID
,ard.ref_cust_trx_line_gl_dist_id CUST_TRX_LINE_GL_DIST_ID
--
,ctl.inventory_item_id INVENTORY_ITEM_ID
,ctl.sales_tax_id SALES_TAX_ID
,trx.set_of_books_id SET_OF_BOOKS_ID
,trx.bill_to_site_use_id BILL_SITE_USE_ID
,trx.sold_to_site_use_id SOLD_SITE_USE_ID
,trx.ship_to_site_use_id SHIP_SITE_USE_ID
,trx.bill_to_customer_id BILL_CUSTOMER_ID
,trx.sold_to_customer_id SOLD_CUSTOMER_ID
,trx.ship_to_customer_id SHIP_CUSTOMER_ID
,ctl.tax_line_id TAX_LINE_ID
--
,'Y' SELECT_FLAG
,'L' LEVEL_FLAG
,'T' FROM_TO_FLAG
,'BANK_CHARGES' CRH_STATUS
,crh.status APP_CRH_STATUS
--
,gt.event_type_code EVENT_TYPE_CODE
,gt.event_class_code EVENT_CLASS_CODE
,gt.entity_code ENTITY_CODE
--
,ard.third_party_id third_party_id
,ard.third_party_sub_id third_party_site_id
,DECODE(ard.third_party_id,NULL,NULL,'C') third_party_type
,ard.source_type source_type
,abs(crh.amount + nvl(crh.factor_discount_amount,0)) RECP_AMOUNT
,abs(crh.acctd_amount + nvl(crh.acctd_factor_discount_amount,0)) RECP_ACCTD_AMOUNT
,abs(crhgt.amount) crh_amount
,abs(crhgt.acctd_amount) crh_acctd_amount
,crh.cash_receipt_history_id CRH_RECORD_ID
,decode(crh.amount+ nvl(crh.factor_discount_amount,0),0,0,
ar_unposted_item_util.currRound(ra.amount_applied-((abs(crh.amount)/abs(crh.amount+ nvl(crh.factor_discount_amount,0)))*ra.amount_applied),cr.currency_code)) ALLOC_AMOUNT
,decode(crh.acctd_amount+ nvl(crh.acctd_factor_discount_amount,0),0,0,
ar_unposted_item_util.currRound(ra.acctd_amount_applied_from-((abs(crh.acctd_amount)/abs(crh.acctd_amount+ nvl(crh.acctd_factor_discount_amount,0)))*ra.acctd_amount_applied_from),cr.currency_code)) ALLOC_ACCTD_AMOUNT
,ext.max_line_id MAX_LINE_ID
,decode(trx.upgrade_method,'R12_11ICASH','N','') mfar_upg_trx
FROM xla_events_gt gt,
ar_receivable_applications_all ra,
ar_cash_receipt_history_all crh,
ar_distributions_all ard,
ra_customer_trx_all trx,
ra_cust_trx_line_gl_dist_all ctlgd,
ra_customer_trx_lines_all ctl,
ar_cash_receipts_all cr,
gl_ledgers lg,
ar_crh_gt crhgt,
(SELECT source_id,
MAX(line_id) max_line_id
FROM ar_xla_lines_extract
WHERE source_table='RA'
AND posting_entity='APP'
AND event_class_code='RECEIPT'
AND customer_trx_line_id IS NOT NULL
GROUP BY source_id) ext
WHERE gt.application_id = 222
AND gt.event_type_code IN ('RECP_CREATE','RECP_UPDATE')
AND gt.event_id = ra.event_id
AND ra.status = 'APP'
AND ard.source_id = ra.receivable_application_id
AND ard.source_table = 'RA'
AND ra.receivable_application_id = ext.source_id
AND nvl(ard.REF_MF_DIST_FLAG, 'Z') <> 'U'
AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS')
AND ra.cash_receipt_history_id = crh.cash_receipt_history_id
AND ra.cash_receipt_id = cr.cash_receipt_id
AND trx.set_of_books_id = lg.ledger_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.posting_control_id = -3
AND ra.event_id <> crh.event_id
AND ard.ref_cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id(+)
AND ard.ref_customer_trx_line_id = ctl.customer_trx_line_id(+)
AND ra.applied_customer_trx_id = trx.customer_trx_id
AND crhgt.cash_receipt_id = crh.cash_receipt_id
AND crhgt.cash_receipt_history_id = crh.cash_receipt_history_id
AND crhgt.status = 'BANK_CHARGES'
AND sign(crhgt.CRH_LINE_ID) <> -1
AND crh.status = 'CLEARED'
AND NVL(crh.factor_discount_amount,0) <> 0
AND NVL(crh.acctd_factor_discount_amount,0) <> 0
order by CASH_RECEIPT_ID, LINE_ID, CRH_RECORD_ID, CRH_STATUS
) ordered_crh;
mfar_insert_crh_extract (l_crh_mfar_extract_record);
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
,CRH_STATUS
,event_type_code
,event_class_code
,entity_code
,tax_line_id
,additional_char1
,FROM_EXCHANGE_RATE
,FROM_EXCHANGE_RATE_TYPE
,FROM_EXCHANGE_DATE
,FROM_CURRENCY_CODE
,TO_CURRENCY_CODE
,MFAR_ADDITIONAL_ENTRY
,SOURCE_TYPE
,DIST_CCID
,REF_DIST_CCID
)
SELECT /*+LEADING(gt) USE_NL(gt, app)*/
gt.event_id, -- EVENT_ID
-1 * ar_mfar_extract_s.nextval, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
trxt.exchange_rate_type, -- EXCHANGE_RATE_TYPE
trxt.exchange_rate, -- EXCHANGE_RATE
decode(trxt.invoice_currency_code,sob.currency_code,
trxt.trx_date, trxt.exchange_date), -- EXCHANGE_DATE
(NVL(dist.acctd_amount_dr,0) -
NVL(dist.acctd_amount_cr,0)) - ( NVL(dist.from_acctd_amount_dr,0) -
NVL(dist.from_acctd_amount_cr,0)) ACCTD_AMOUNT
,NVL(dist.taxable_accounted_dr,0) -
NVL(dist.taxable_accounted_cr,0), -- TAXABLE_ACCTD_AMOUNT
app.org_id, -- ORG_ID
app.receivable_application_id, -- HEADER_TABLE_ID
'APP_CURR_ROUND', -- POSTING_ENTITY
NULL, -- CASH_RECEIPT_ID
trxt.customer_trx_id, -- CUSTOMER_TRX_ID
tlt.customer_trx_line_id, -- CUSTOMER_TRX_LINE_ID
gldt.cust_trx_line_gl_dist_id, -- CUST_TRX_LINE_GL_DIST_ID
gldt.cust_trx_line_salesrep_id, -- CUST_TRX_LINE_SALESREP_ID
tlt.inventory_item_id, -- INVENTORY_ITEM_ID
tlt.sales_tax_id, -- SALES_TAX_ID
osp.master_organization_id, -- SO_ORGANIZATION_ID
tlt.tax_exemption_id, -- TAX_EXEMPTION_ID
tlt.uom_code, -- UOM_CODE
tlt.warehouse_id, -- WAREHOUSE_ID
trxt.agreement_id, -- AGREEMENT_ID
trxt.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
trxt.drawee_bank_account_id, -- DRAWEE_BANK_ACCOUNT_ID
trxt.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
NULL, -- DISTRIBUTION_SET_ID
psch.payment_schedule_id, -- PAYMENT_SCHEDULE_ID
trxt.receipt_method_id, -- RECEIPT_METHOD_ID
NULL, -- RECEIVABLES_TRX_ID
arp_xla_extract_main_pkg.ed_uned_trx('EDISC',app.org_id), -- ED_ADJ_RECEIVABLES_TRX_ID
arp_xla_extract_main_pkg.ed_uned_trx('UNEDISC',app.org_id), -- UNED_RECEIVABLES_TRX_ID
trxt.set_of_books_id, -- SET_OF_BOOKS_ID
trxt.primary_salesrep_id, -- SALESREP_ID
trxt.bill_to_site_use_id, -- BILL_SITE_USE_ID
trxt.drawee_site_use_id, -- DRAWEE_SITE_USE_ID
trxt.paying_site_use_id, -- PAYING_SITE_USE_ID
trxt.sold_to_site_use_id, -- SOLD_SITE_USE_ID
trxt.ship_to_site_use_id, -- SHIP_SITE_USE_ID
NULL, -- RECEIPT_CUSTOMER_SITE_USE_ID
trxt.bill_to_contact_id, -- BILL_CUST_ROLE_ID
trxt.drawee_contact_id, -- DRAWEE_CUST_ROLE_ID
trxt.ship_to_contact_id, -- SHIP_CUST_ROLE_ID
trxt.sold_to_contact_id, -- SOLD_CUST_ROLE_ID
trxt.bill_to_customer_id, -- BILL_CUSTOMER_ID
trxt.drawee_id, -- DRAWEE_CUSTOMER_ID
trxt.paying_customer_id, -- PAYING_CUSTOMER_ID
trxt.sold_to_customer_id, -- SOLD_CUSTOMER_ID
trxt.ship_to_customer_id, -- SHIP_CUSTOMER_ID
trxt.remit_to_address_id, -- REMIT_ADDRESS_ID
NULL, -- RECEIPT_BATCH_ID
NULL, -- RECEIVABLE_APPLICATION_ID
NULL, -- CUSTOMER_BANK_BRANCH_ID
NULL, -- ISSUER_BANK_BRANCH_ID
trxt.batch_source_id, -- BATCH_SOURCE_ID
trxt.batch_id, -- BATCH_ID
trxt.term_id, -- TERM_ID
'Y', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'T', -- FROM_TO_FLAG
(NVL(dist.amount_dr,0) - NVL(dist.amount_cr,0))
- (NVL(dist.from_amount_dr,0) - NVL(dist.from_amount_cr,0)) FROM_AMOUNT
,(NVL(dist.amount_dr,0) - NVL(dist.amount_cr,0))
- (NVL(dist.from_amount_dr,0) - NVL(dist.from_amount_cr,0)) AMOUNT
, (NVL(dist.acctd_amount_dr,0) -
NVL(dist.acctd_amount_cr,0)) - ( NVL(dist.from_acctd_amount_dr,0) -
NVL(dist.from_acctd_amount_cr,0)) FROM_ACCTD_AMOUNT
,'CURR_ROUND'
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
,tlt.tax_line_id
,app.upgrade_method
,cr.exchange_rate
,cr.exchange_rate_type
,cr.exchange_date
,cr.currency_code
,trxt.invoice_currency_code
,'Y' --MFAR_ADDITIONAL_ENTRY
, 'CURR_ROUND'
, currard.code_combination_id
, dist.ref_dist_ccid
FROM xla_events_gt gt,
ar_receivable_applications_all app,
ar_distributions_all dist,
gl_sets_of_books sob,
oe_system_parameters_all osp,
ra_customer_trx_all trxt,
ra_customer_trx_lines_all tlt,
ra_cust_trx_line_gl_dist_all gldt,
ar_payment_schedules_all psch,
ar_cash_receipts_all cr,
ar_distributions_all currard
WHERE gt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE', 'RECP_RATE_ADJUST' )
AND gt.application_id = 222
AND gt.event_id = app.event_id
AND (app.upgrade_method IN ('R12_NLB','R12', 'R12_11IMFAR', 'R12_11ICASH','11I_R12_POST','R12_MERGE')
OR (app.upgrade_method IS NULL AND app.status = 'APP')
OR (DECODE(app.upgrade_method,
'11I_MFAR_UPG' ,DECODE(dist.source_table_secondary,'UPMFRAMIAR','Y','N'),
'N') = 'Y'))
AND app.set_of_books_id = sob.set_of_books_id
AND app.org_id = osp.org_id(+)
AND app.applied_customer_trx_id = trxt.customer_trx_id
AND app.cash_receipt_id = cr.cash_receipt_id
AND dist.source_id = app.receivable_application_id
AND dist.source_table = 'RA'
AND dist.ref_customer_trx_line_id = tlt.customer_trx_line_id(+)
AND dist.ref_cust_trx_line_gl_dist_id = gldt.cust_trx_line_gl_dist_id(+)
AND trxt.customer_trx_id = psch.customer_trx_id
AND NVL(psch.terms_sequence_number,1) = 1
AND (NVL(dist.acctd_amount_cr,0) - NVL(dist.acctd_amount_dr,0))
<> (NVL(dist.from_acctd_amount_cr,0) - NVL(dist.from_acctd_amount_dr,0))
AND dist.source_type = 'REC'
AND dist.source_id = currard.source_id
AND dist.source_table = currard.source_table
AND currard.source_type = 'CURR_ROUND';
INSERT INTO ar_crh_app_gt (
entity_id
,receivable_application_id
,cash_receipt_history_id
,cash_receipt_history_status
,line_id
,amount
,acctd_amount
,from_amount
,from_acctd_amount
,third_party_id
,third_party_site_id
,third_party_type
,from_currency_code
,from_exchange_rate
,from_exchange_type
,from_exchange_date
,to_currency_code
,to_exchange_rate
,to_exchange_type
,to_exchange_date
,ref_customer_trx_line_id
,ref_cust_trx_line_gl_dist_id
,code_combination_id
,ref_code_combination_id
,ref_dist_ccid
,activity_bucket
,source_type
,source_table
,ra_post_indicator
,crh_post_indicator
,customer_trx_id
,inventory_item_id
,sales_tax_id
,tax_line_id
,bill_to_customer_id
,bill_to_site_use_id
,sold_to_customer_id
,sold_to_site_use_id
,ship_to_customer_id
,ship_to_site_use_id
,signed_receipt_amount)
SELECT xla.entity_id --entity_id
,mcd.misc_cash_distribution_id --receivable_application_id
,NULL --cash_receipt_history_id
,NULL --cash_receipt_history_status
,ard.line_id --line_id
,NVL(ard.amount_cr,0)-
NVL(ard.amount_dr,0) --amount
,NVL(ard.acctd_amount_cr,0)-
NVL(ard.acctd_amount_dr,0) --acctd_amount
,NVL(ard.from_amount_cr,0)-
NVL(ard.from_amount_dr,0) --from_amount
,NVL(ard.from_acctd_amount_cr,0)-
NVL(ard.from_acctd_amount_dr,0) --from_acctd_amount
,NULL --third_party_id
,NULL --third_party_site_id
,NULL --third_party_type
,cr.currency_code --from_currency_code
,NVL(crh.exchange_rate,cr.exchange_rate) --from_exchange_rate
,NVL(crh.exchange_rate_type,cr.exchange_rate_type) --from_exchange_type
,NVL(crh.exchange_date,cr.exchange_date) --from_exchange_date
,NULL --to_currency_code
,NULL --to_exchange_rate
,NULL --to_exchange_type
,NULL --to_exchange_date
,NULL --ref_customer_trx_line_id
,NULL --ref_cust_trx_line_gl_dist_id
,ard.code_combination_id --code_combination_id
,NULL --ref_code_combination_id
,ard.ref_dist_ccid --ref_dist_ccid
,ard.activity_bucket --activity_bucket
,ard.source_type --source_type
,ard.source_table --source_table
,NULL --ra_post_indicator
,NULL --crh_post_indicator
,NULL --customer_trx_id
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,cr.amount
FROM ar_cash_receipts_all cr
,ar_misc_cash_distributions_all mcd
,ar_cash_receipt_history_all crh
,ar_distributions_all ard
,(SELECT entity_id,
source_id_int_1
FROM xla_events_gt
WHERE application_id = 222
AND event_type_code IN ('MISC_RECP_CREATE','MISC_RECP_UPDATE')
GROUP BY entity_id,
source_id_int_1) xla
WHERE xla.source_id_int_1 = cr.cash_receipt_id
AND cr.cash_receipt_id = mcd.cash_receipt_id
AND nvl(mcd.event_id, -1) <> NVL((select event_id from ar_cash_receipt_history_all crh1 where
crh1.cash_receipt_id = cr.cash_receipt_id and
crh1.status='REVERSED' and crh1.current_record_flag = 'Y'),0)
AND mcd.cash_receipt_history_id = crh.cash_receipt_history_id(+)
AND mcd.misc_cash_distribution_id = ard.source_id
AND ard.source_table = 'MCD';
INSERT INTO ar_xla_lines_extract
(EVENT_ID
,LINE_NUMBER
,MFAR_ADDITIONAL_ENTRY
,LEDGER_ID
,BASE_CURRENCY_CODE
,ORG_ID
,LINE_ID
,SOURCE_ID
,SOURCE_TABLE
,HEADER_TABLE_ID
,POSTING_ENTITY
,XLA_ENTITY_ID
--
,DIST_CCID
,REF_DIST_CCID
,REF_CTLGD_CCID
--
,FROM_CURRENCY_CODE
,FROM_EXCHANGE_RATE
,FROM_EXCHANGE_RATE_TYPE
,FROM_EXCHANGE_DATE
,FROM_AMOUNT
,FROM_ACCTD_AMOUNT
--
,TO_CURRENCY_CODE
,EXCHANGE_RATE
,EXCHANGE_RATE_TYPE
,EXCHANGE_DATE
,AMOUNT
,ACCTD_AMOUNT
--
,RECEIVABLE_APPLICATION_ID
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
--
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SET_OF_BOOKS_ID
,BILL_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,BILL_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,TAX_LINE_ID
--
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,CRH_STATUS
,APP_CRH_STATUS
--
,EVENT_TYPE_CODE
,EVENT_CLASS_CODE
,ENTITY_CODE)
SELECT
crh.event_id --event_id
,-1 * ar_mfar_extract_s.NEXTVAL --LINE_NUMBER
,'Y' --MFAR_ADDITIONAL_ENTRY
,crh.ledger_id --LEDGER_ID
,crh.base_currency_code --BASE_CURRENCY_CODE
,crh.org_id --ORG_ID
,mcd.line_id --LINE_ID
,mcd.receivable_application_id --SOURCE_ID --This misc_cash_dist_id only
,'MCD' --SOURCE_TABLE
,crh.cash_receipt_id --HEADER_TABLE_ID
,'MISC_RECEIPT_HISTORY' --POSTING_ENTITY
,crh.entity_id --xla_entity_id
--
,mcd.code_combination_id --DIST_CCID
,mcd.code_combination_id --ref_dist_ccid
,mcd.code_combination_id --REF_CTLGD_CCID
--
,mcd.from_currency_code --from_currency_code
,mcd.from_exchange_rate --from_exchange_rate
,mcd.from_exchange_type --FROM_EXCHANGE_RATE_TYPE
,mcd.from_exchange_date --from_exchange_date
,SIGN(crh.amount)*mcd.from_amount*SIGN(signed_receipt_amount) --from_amount
,SIGN(crh.acctd_amount)*mcd.from_acctd_amount*SIGN(signed_receipt_amount)--from_acctd_amount
--
,mcd.from_currency_code --to_currency_code
,mcd.from_exchange_rate --exchange_rate
,mcd.from_exchange_type --EXCHANGE_RATE_TYPE
,mcd.from_exchange_date --EXCHANGE_DATE
,SIGN(crh.amount)*mcd.amount*SIGN(signed_receipt_amount) --amount
,SIGN(crh.acctd_amount)*mcd.acctd_amount*SIGN(signed_receipt_amount) --acctd_amount
--
,mcd.receivable_application_id --RECEIVABLE_APPLICATION_ID --MISC_CASH_DIST_ID
,crh.cash_receipt_id --CASH_RECEIPT_ID
,NULL --CUSTOMER_TRX_ID
,NULL --CUSTOMER_TRX_LINE_ID
,NULL --CUST_TRX_LINE_GL_DIST_ID
--
,NULL --INVENTORY_ITEM_ID
,NULL --SALES_TAX_ID
,crh.ledger_id --SET_OF_BOOKS_ID
,NULL --BILL_SITE_USE_ID
,NULL --SOLD_SITE_USE_ID
,NULL --SHIP_SITE_USE_ID
,NULL --BILL_CUSTOMER_ID
,NULL --SOLD_CUSTOMER_ID
,NULL --SHIP_CUSTOMER_ID
,NULL --TAX_LINE_ID
--
,'Y' --SELECT_FLAG
,'L' --LEVEL_FLAG
,'T' --FROM_TO_FLAG
,crh.status --CRH_STATUS
,NULL --APP_CRH_STATUS
--
,gt.event_type_code --EVENT_TYPE_CODE
,gt.event_class_code --EVENT_CLASS_CODE
,gt.entity_code --ENTITY_CODE
FROM ar_crh_gt crh,
ar_crh_app_gt mcd,
xla_events_gt gt
WHERE crh.entity_id = mcd.entity_id
AND mcd.source_table = 'MCD'
AND crh.event_id = gt.event_id;
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
,PAIRED_CCID
--{BUG#4356088
,event_type_code
,event_class_code
,entity_code
--BUG#4645389
,tax_line_id
--}
,MFAR_ADDITIONAL_ENTRY
)
SELECT /*+LEADING(gt) USE_NL(gt,th)*/
gt.event_id, -- EVENT_ID
ar_mfar_extract_s.NEXTVAL, --LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
trx.exchange_rate_type , -- EXCHANGE_RATE_TYPE
trx.exchange_rate , -- EXCHANGE_RATE
trx.exchange_date , -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0) -
NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0) -
NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
th.org_id, -- ORG_ID
th.transaction_history_id, -- HEADER_ID
'TH', -- POSTING_ENTITY
'', -- CASH_RECEIPT_ID
th.customer_trx_id, -- CUSTOMER_TRX_ID
dist.ref_customer_trx_line_id, -- CUSTOMER_TRX_LINE_ID
dist.ref_cust_trx_line_gl_dist_id,--CUST_TRX_LINE_GL_DIST_ID
gld.cust_trx_line_salesrep_id, -- CUST_TRX_LINE_SALESREP_ID
tl.inventory_item_id, --INVENTORY_ITEM_ID
tl.sales_tax_id, --SALES_TAX_ID
osp.master_organization_id, --SO_ORGANIZATION_ID
tl.tax_exemption_id, --TAX_EXEMPTION_ID
tl.uom_code, --UOM_CODE
tl.warehouse_id, --WAREHOUSE_ID
trx.agreement_id, --AGREEMENT_ID
trx.customer_bank_account_id, --CUSTOMER_BANK_ACCT_ID
'', --DRAWEE_BANK_ACCOUNT_ID
trx.remit_bank_acct_use_id, --REMITTANCE_BANK_ACCT_ID
'', --DISTRIBUTION_SET_ID
psch.payment_schedule_id, --PAYMENT_SCHEDULE_ID
'', --RECEIPT_METHOD_ID
'', --RECEIVABLES_TRX_ID
'', --ED_ADJ_RECEIVABLES_TRX_ID
'', --UNED_RECEIVABLES_TRX_ID
sob.set_of_books_id, --SET_OF_BOOKS_ID
trx.primary_salesrep_id, --SALESREP_ID
trx.bill_to_site_use_id, --BILL_SITE_USE_ID
trx.drawee_site_use_id, --DRAWEE_SITE_USE_ID
trx.paying_site_use_id, --PAYING_SITE_USE_ID
trx.sold_to_site_use_id, --SOLD_SITE_USE_ID
trx.ship_to_site_use_id, --SHIP_SITE_USE_ID
'', --RECEIPT_CUSTOMER_SITE_USE_ID
trx.bill_to_contact_id, --BILL_CUST_ROLE_ID
trx.drawee_contact_id, --DRAWEE_CUST_ROLE_ID
trx.ship_to_contact_id, --SHIP_CUST_ROLE_ID
trx.sold_to_contact_id, --SOLD_CUST_ROLE_ID
trx.bill_to_customer_id, --BILL_CUSTOMER_ID
trx.drawee_id, --DRAWEE_CUSTOMER_ID
trx.paying_customer_id, --PAYING_CUSTOMER_ID
trx.sold_to_customer_id, --SOLD_CUSTOMER_ID
trx.ship_to_customer_id, --SHIP_CUSTOMER_ID
'', --REMIT_ADDRESS_ID
'', --RECEIPT_BATCH_ID
'', --RECEIVABLE_APPLICATION_ID
'', --CUSTOMER_BANK_BRANCH_ID
'', --ISSUER_BANK_BRANCH_ID
trx.batch_source_id, --BATCH_SOURCE_ID
trx.batch_id, --BATCH_ID
trx.term_id, --TERM_ID
'N', --SELECT_FLAG
'L', --LEVEL_FLAG
'', --FROM_TO_FLAG
NVL(dist.from_amount_cr,0)
-NVL(dist.from_amount_dr,0), -- FROM_AMOUNT,
NVL(dist.amount_cr,0)
-NVL(dist.amount_dr,0), -- AMOUNT
NVL(dist.from_acctd_amount_cr,0)
-NVL(dist.from_acctd_amount_dr,0), -- FROM_ACCTD_AMOUNT
NULL -- PAIRED_CCID
--{BUG#4356088
,gt.event_type_code
,gt.event_class_code
,gt.entity_code
--BUG#4645389
,tl.tax_line_id --tax_line_id
,'N'
FROM xla_events_gt gt,
ar_transaction_history_all th,
ra_customer_trx_all trx,
ra_customer_trx_lines_all tl,
ra_cust_trx_line_gl_dist_all gld,
oe_system_parameters_all osp,
ar_distributions_all dist,
gl_sets_of_books sob,
ar_payment_schedules_all psch
WHERE gt.event_type_code IN ('BILL_CREATE' ,
'BILL_UPDATE' ,
'BILL_REVERSE' )
AND gt.application_id = p_application_id
AND gt.event_id = th.event_id
AND dist.source_table = 'TH'
AND dist.source_id = th.transaction_history_id
AND th.customer_trx_id = trx.customer_trx_id
-- AND trx.customer_trx_id = tl.customer_trx_id
-- AND trx.customer_trx_id = gld.customer_trx_id
AND th.org_id = osp.org_id(+)
/*Pass double entries accounting*/
--AND ( ( dist.source_type = 'REC' AND dist.source_table_secondary = 'CTL')
-- OR ( dist.source_type <> 'REC'))
AND dist.ref_customer_trx_line_id = tl.customer_trx_line_id(+)
AND dist.ref_cust_trx_line_gl_dist_id = gld.cust_trx_line_gl_dist_id(+)
AND trx.set_of_books_id = sob.set_of_books_id
AND trx.customer_trx_id = psch.customer_trx_id
AND NVL(psch.terms_sequence_number,1) = 1;
INSERT INTO ar_xla_lines_extract (
EVENT_ID
,LINE_NUMBER
,LANGUAGE
,LEDGER_ID
,SOURCE_ID
,SOURCE_TABLE
,LINE_ID
,TAX_CODE_ID
,LOCATION_SEGMENT_ID
,BASE_CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_DATE
,ACCTD_AMOUNT
,TAXABLE_ACCTD_AMOUNT
,ORG_ID
,HEADER_TABLE_ID
,POSTING_ENTITY
,CASH_RECEIPT_ID
,CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,CUST_TRX_LINE_GL_DIST_ID
,CUST_TRX_LINE_SALESREP_ID
,INVENTORY_ITEM_ID
,SALES_TAX_ID
,SO_ORGANIZATION_ID
,TAX_EXEMPTION_ID
,UOM_CODE
,WAREHOUSE_ID
,AGREEMENT_ID
,CUSTOMER_BANK_ACCT_ID
,DRAWEE_BANK_ACCOUNT_ID
,REMITTANCE_BANK_ACCT_ID
,DISTRIBUTION_SET_ID
,PAYMENT_SCHEDULE_ID
,RECEIPT_METHOD_ID
,RECEIVABLES_TRX_ID
,ED_ADJ_RECEIVABLES_TRX_ID
,UNED_RECEIVABLES_TRX_ID
,SET_OF_BOOKS_ID
,SALESREP_ID
,BILL_SITE_USE_ID
,DRAWEE_SITE_USE_ID
,PAYING_SITE_USE_ID
,SOLD_SITE_USE_ID
,SHIP_SITE_USE_ID
,RECEIPT_CUSTOMER_SITE_USE_ID
,BILL_CUST_ROLE_ID
,DRAWEE_CUST_ROLE_ID
,SHIP_CUST_ROLE_ID
,SOLD_CUST_ROLE_ID
,BILL_CUSTOMER_ID
,DRAWEE_CUSTOMER_ID
,PAYING_CUSTOMER_ID
,SOLD_CUSTOMER_ID
,SHIP_CUSTOMER_ID
,REMIT_ADDRESS_ID
,RECEIPT_BATCH_ID
,RECEIVABLE_APPLICATION_ID
,CUSTOMER_BANK_BRANCH_ID
,ISSUER_BANK_BRANCH_ID
,BATCH_SOURCE_ID
,BATCH_ID
,TERM_ID
,SELECT_FLAG
,LEVEL_FLAG
,FROM_TO_FLAG
,FROM_AMOUNT
,AMOUNT
,FROM_ACCTD_AMOUNT
,reversal_code
,MFAR_ADDITIONAL_ENTRY
)
-- FROM document type Cash Receipt
SELECT /*+LEADING(gt) USE_NL(gt,mcd)*/
gt.event_id, -- EVENT_ID
dist.line_id, -- LINE_NUMBER
'', -- LANGUAGE
sob.set_of_books_id, -- LEDGER_ID
dist.source_id, -- SOURCE_ID
dist.source_table, -- SOURCE_TABLE
dist.line_id, -- LINE_ID
dist.tax_code_id, -- TAX_CODE_ID
dist.location_segment_id, -- LOCATION_SEGMENT_ID
sob.currency_code, -- BASE_CURRENCY
NVL(crh.exchange_rate_type,cr.exchange_rate_type), -- EXCHANGE_RATE_TYPE
NVL(crh.exchange_rate,cr.exchange_rate) , -- EXCHANGE_RATE
NVL(crh.exchange_date,cr.exchange_date) , -- EXCHANGE_DATE
NVL(dist.acctd_amount_cr,0)
- NVL(dist.acctd_amount_dr,0), -- ACCTD_AMOUNT
NVL(dist.taxable_accounted_cr,0)
- NVL(dist.taxable_accounted_dr,0), -- TAXABLE_ACCTD_AMOUNT
mcd.org_id, -- ORG_ID
mcd.cash_receipt_id, -- HEADER_ID
'MCD', -- POSTING_ENTITY
cr.cash_receipt_id, -- CASH_RECEIPT_ID
NULL, -- CUSTOMER_TRX_ID
NULL, -- CUSTOMER_TRX_LINE_ID
NULL, -- CUST_TRX_LINE_GL_DIST_ID
NULL, -- CUST_TRX_LINE_SALESREP_ID
NULL, -- INVENTORY_ITEM_ID
NULL, -- SALES_TAX_ID
NULL, -- SO_ORGANIZATION_ID
NULL, -- TAX_EXEMPTION_ID
NULL, -- UOM_CODE
NULL, -- WAREHOUSE_ID
NULL, -- AGREEMENT_ID
cr.customer_bank_account_id, -- CUSTOMER_BANK_ACCT_ID
NULL, -- DRAWEE_BANK_ACCOUNT_ID
cr.remit_bank_acct_use_id, -- REMITTANCE_BANK_ACCT_ID
cr.distribution_set_id, -- DISTRIBUTION_SET_ID
NULL, -- PAYMENT_SCHEDULE_ID
cr.receipt_method_id, -- RECEIPT_METHOD_ID
cr.receivables_trx_id, -- RECEIVABLES_TRX_ID
NULL, -- ED_ADJ_RECEIVABLES_TRX_ID
NULL, -- UNED_RECEIVABLES_TRX_ID
cr.set_of_books_id, -- SET_OF_BOOKS_ID
NULL, -- SALESREP_ID
cr.customer_site_use_id, -- BILL_SITE_USE_ID
NULL, -- DRAWEE_SITE_USE_ID
cr.customer_site_use_id, -- PAYING_SITE_USE_ID -- synch with PAYING_CUSTOMER_ID
NULL, -- SOLD_SITE_USE_ID
NULL, -- SHIP_SITE_USE_ID
cr.customer_site_use_id, -- RECEIPT_CUSTOMER_SITE_USE_ID
NULL, -- BILL_CUST_ROLE_ID
NULL, -- DRAWEE_CUST_ROLE_ID
NULL, -- SHIP_CUST_ROLE_ID
NULL, -- SOLD_CUST_ROLE_ID
NULL, -- BILL_CUSTOMER_ID
NULL, -- DRAWEE_CUSTOMER_ID
cr.pay_from_customer, -- PAYING_CUSTOMER_ID
NULL, -- SOLD_CUSTOMER_ID
NULL, -- SHIP_CUSTOMER_ID
NULL, -- REMIT_ADDRESS_ID
cr.SELECTED_REMITTANCE_BATCH_ID, -- RECEIPT_BATCH_ID
NULL, -- RECEIVABLE_APPLICATION_ID
cr.customer_bank_branch_id, -- CUSTOMER_BANK_BRANCH_ID
cr.issuer_bank_branch_id, -- ISSUER_BANK_BRANCH_ID
NULL, -- BATCH_SOURCE_ID
NULL, -- BATCH_ID
NULL, -- TERM_ID
'N', -- SELECT_FLAG
'L', -- LEVEL_FLAG
'', -- FROM_TO_FLAG
NVL(dist.from_amount_cr,0)
-NVL(dist.from_amount_dr,0), -- FROM_AMOUNT,
NVL(dist.amount_cr,0)
-NVL(dist.amount_dr,0), -- AMOUNT
NVL(dist.from_acctd_amount_cr,0)
-NVL(dist.from_acctd_amount_dr,0) -- AMOUNT
,DECODE(gt.event_type_code,'MISC_RECP_REVERSE' ,'Y','N') --reversal_code
,'N'
FROM xla_events_gt gt,
ar_misc_cash_distributions_all mcd,
ar_distributions_all dist,
gl_sets_of_books sob,
ar_cash_receipts_all cr,
--5201086
ar_cash_receipt_history_all crh
WHERE gt.event_type_code IN ( 'MISC_RECP_CREATE','MISC_RECP_RATE_ADJUST',
'MISC_RECP_UPDATE') --Uptake XLA Reversal 'MISC_RECP_REVERSE' )
--'MISC_RECP_REVERSE' REVERSAL only needs header level source
AND gt.event_id = mcd.event_id
AND gt.application_id = p_application_id
AND dist.source_table = 'MCD'
AND dist.source_id = mcd.misc_cash_distribution_id
AND mcd.set_of_books_id = sob.set_of_books_id
AND mcd.cash_receipt_id = cr.cash_receipt_id
AND mcd.cash_receipt_history_id = crh.cash_receipt_history_id(+);
SELECT set_of_books_id
FROM ar_xla_lines_extract gt
WHERE posting_entity in ('CR','CTLGD')
AND select_flag = 'Y'
AND level_flag = 'H'
AND set_of_books_id IS NOT NULL
AND event_class_code in ('RECEIPT','MISC_RECEIPT','CREDIT_MEMO');
SELECT ar_posting_control_s.NEXTVAL
INTO l_pst_id
FROM DUAL;
SELECT trunc(sysdate) INTO l_date FROM SYS.DUAL;
UPDATE ar_cash_receipt_history_all
SET posting_control_id = l_pst_id,
gl_posted_date = l_date,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.user_id
WHERE posting_control_id = -3
AND (cash_receipt_id, event_id) IN
(SELECT /*+ cardinality(EV,1) unnest */ ev.source_id_int_1, ev.event_id
/* added hint as suggest by the perf team thru bug 13702457 */
FROM xla_post_acctg_events_v ev
WHERE ev.application_id = p_application_id
AND ev.process_status_code = 'P'
AND ev.event_type_code IN
('RECP_CREATE' ,
'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,
'RECP_REVERSE' ,
'MISC_RECP_CREATE' ,
'MISC_RECP_UPDATE' ,
'MISC_RECP_RATE_ADJUST',
'MISC_RECP_REVERSE' ));
UPDATE AR_MISC_CASH_DISTRIBUTIONS_ALL
SET posting_control_id = l_pst_id,
gl_posted_date = l_date,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.user_id
WHERE posting_control_id = -3
AND (cash_receipt_id, event_id) IN
(SELECT ev.source_id_int_1, ev.event_id
FROM xla_post_acctg_events_v ev
WHERE ev.application_id = p_application_id
AND ev.process_status_code = 'P'
AND ev.event_type_code IN
('MISC_RECP_CREATE' ,
'MISC_RECP_UPDATE' ,
'MISC_RECP_RATE_ADJUST',
'MISC_RECP_REVERSE' ));
UPDATE /*+ INDEX(ra_cust_trx_line_gl_dist_all ra_cust_trx_line_gl_dist_n6) */
ra_cust_trx_line_gl_dist_all
SET posting_control_id = l_pst_id,
gl_posted_date = l_date,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.user_id
WHERE posting_control_id = -3
AND (customer_trx_id, event_id) IN
(SELECT ev.source_id_int_1, ev.event_id
FROM xla_post_acctg_events_v ev
WHERE ev.application_id = p_application_id
AND ev.process_status_code = 'P'
AND ev.event_type_code IN
('INV_CREATE' , 'INV_UPDATE' ,
'CM_CREATE' , 'CM_UPDATE' ,
'DM_CREATE' , 'DM_UPDATE' ,
'DEP_CREATE' , 'DEP_UPDATE' ,
'GUAR_CREATE' , 'GUAR_UPDATE' ,
'CB_CREATE' ));
UPDATE ar_adjustments_all
SET posting_control_id = l_pst_id,
gl_posted_date = l_date,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.user_id
WHERE posting_control_id = -3
AND adjustment_id IN
(SELECT ev.source_id_int_1
FROM xla_post_acctg_events_v ev
WHERE ev.application_id = p_application_id
AND ev.process_status_code = 'P'
AND ev.event_type_code = 'ADJ_CREATE');
UPDATE ar_receivable_applications_all
SET posting_control_id = l_pst_id,
gl_posted_date = l_date,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.user_id
WHERE posting_control_id = -3
AND event_id IN
(SELECT /*+ LEADING(ev) CARDINALITY(ev,1) */ ev.event_id -- Added hint for bug 14335425
FROM xla_post_acctg_events_v ev
WHERE ev.application_id = p_application_id
AND ev.process_status_code = 'P'
AND ev.event_type_code IN
('CM_CREATE' ,'CM_UPDATE' ,
'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST','RECP_REVERSE' ,
'MISC_RECP_RATE_ADJUST','MISC_RECP_REVERSE' ));
UPDATE AR_TRANSACTION_HISTORY_ALL
SET posting_control_id = l_pst_id,
gl_posted_date = l_date,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.user_id
WHERE posting_control_id = -3
AND postable_flag='Y'
AND event_id IN
(SELECT ev.event_id
FROM xla_post_acctg_events_v ev
WHERE ev.application_id = p_application_id
AND ev.process_status_code = 'P'
AND ev.event_type_code IN
( 'BILL_CREATE' ,
'BILL_UPDATE' ,
'BILL_REVERSE' ));
SELECT 'lock'
BULK COLLECT INTO l_lock
FROM xla_entity_events_v eve,
ra_cust_trx_line_gl_dist_all ctlgd
WHERE eve.request_id = p_report_request_id
AND eve.application_id = p_application_id
AND eve.entity_code = 'TRANSACTIONS'
AND eve.event_id = ctlgd.event_id
AND ctlgd.posting_control_id = -3
AND ctlgd.account_set_flag = 'N'
FOR UPDATE OF ctlgd.cust_trx_line_gl_dist_id;
SELECT 'lock'
BULK COLLECT INTO l_lock
FROM xla_entity_events_v eve,
ar_adjustments_all adj
WHERE eve.request_id = p_report_request_id
AND eve.application_id = p_application_id
AND eve.entity_code = 'ADJUSTMENTS'
AND eve.event_id = adj.event_id
AND adj.posting_control_id = -3
AND NVL(adj.postable,'Y') = 'Y'
FOR UPDATE OF adjustment_id;
SELECT 'lock'
BULK COLLECT INTO l_lock
FROM xla_entity_events_v eve,
ar_receivable_applications_all app
WHERE eve.request_id = p_report_request_id
AND eve.application_id = p_application_id
AND eve.entity_code IN ('RECEIPTS','TRANSACTIONS')
AND eve.event_id = app.event_id
AND app.posting_control_id = -3
AND NVL(app.postable,'Y') ='Y'
AND NVL(app.confirmed_flag,'Y')='Y'
FOR UPDATE OF receivable_application_id;
SELECT 'lock'
BULK COLLECT INTO l_lock
FROM xla_entity_events_v eve,
ar_cash_receipt_history_all crh
WHERE eve.request_id = p_report_request_id
AND eve.application_id = p_application_id
AND eve.entity_code = 'RECEIPTS'
AND eve.event_id = crh.event_id
AND crh.posting_control_id = -3
FOR UPDATE OF crh.cash_receipt_history_id;
SELECT 'lock'
BULK COLLECT INTO l_lock
FROM xla_entity_events_v eve,
ar_misc_cash_distributions_all mcd
WHERE eve.request_id = p_report_request_id
AND eve.application_id = p_application_id
AND eve.entity_code = 'RECEIPTS'
AND eve.event_id = mcd.event_id
AND mcd.posting_control_id = -3
FOR UPDATE OF misc_cash_distribution_id;
SELECT 'lock'
BULK COLLECT INTO l_lock
FROM xla_entity_events_v eve,
ar_transaction_history_all trh
WHERE eve.request_id = p_report_request_id
AND eve.application_id = p_application_id
AND eve.entity_code = 'BILLS_RECEIVABLE'
AND eve.event_id = trh.event_id
AND trh.postable_flag = 'Y'
AND trh.posting_control_id = -3
FOR UPDATE OF trh.transaction_history_id;
' SELECT '||p_segment_name||'
FROM gl_code_combinations
WHERE chart_of_accounts_id = :coa_id
AND code_combination_id = :ccid ';
| Procedure Name : mfar_insert_crh_extract
| Description : Pro-rates the CRH entries over the Receipt application
| distributions and inserts the MFAR additional entries to the
| extract table. The pro-ration happens only when the CRH_STATUS
| is in CLEARED or BANK_CHARGES.
+-------------------------------------------------------------------------------+
| History :
| 25-JUL-2010 Anshu Kaushal Created
+------------------------------------------------------------------------------*/
PROCEDURE mfar_insert_crh_extract (p_crh_mfar_extract_record crh_mfar_extract_record_type)
IS
-- run time variables for proration
x_run_amt number := 0;
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_insert_crh_extract ()+');
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'Reset Run Amounts LINE_ID : '||p_crh_mfar_extract_record(i).LINE_ID||', CRH_RECORD_ID : '||p_crh_mfar_extract_record(i).CRH_RECORD_ID);
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'crh_status : '||p_crh_mfar_extract_record(i).crh_status);
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'crh_acctd_amount : '||p_crh_mfar_extract_record(i).crh_acctd_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'recp_acctd_amount : '||p_crh_mfar_extract_record(i).recp_acctd_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'amount : '||p_crh_mfar_extract_record(i).amount);
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'acctd_amount : '||p_crh_mfar_extract_record(i).acctd_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'from_amount : '||p_crh_mfar_extract_record(i).from_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'from_acctd_amount : '||p_crh_mfar_extract_record(i).from_acctd_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_run_amt : '||x_run_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_alloc_amt : '||x_alloc_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_run_alloc_amt : '||x_run_alloc_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_run_acctd_amt : '||x_run_acctd_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_alloc_acctd_amt : '||x_alloc_acctd_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_run_alloc_acctd_amt : '||x_run_alloc_acctd_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_run_from_amt : '||x_run_from_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_alloc_from_amt : '||x_alloc_from_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_run_alloc_from_amt : '||x_run_alloc_from_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_run_from_acctd_amt : '||x_run_from_acctd_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_alloc_from_acctd_amt : '||x_alloc_from_acctd_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_run_alloc_from_acctd_amt : '||x_run_alloc_from_acctd_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_cleared_amount : '||x_cleared_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_cleared_acctd_amount : '||x_cleared_acctd_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_cleared_from_amount : '||x_cleared_from_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_cleared_from_acctd_amount: '||x_cleared_from_acctd_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_bc_amount : '||x_bc_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_bc_acctd_amount : '||x_bc_acctd_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_bc_from_amount : '||x_bc_from_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_bc_from_acctd_amount : '||x_bc_from_acctd_amount);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_diff_amt : '||x_diff_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_diff_acctd_amt : '||x_diff_acctd_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_diff_from_amt : '||x_diff_from_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_diff_from_acctd_amt : '||x_diff_from_acctd_amt);
local_log(procedure_name => 'mfar_insert_crh_extract',p_msg_text => ' x_max_line_id : '||x_max_line_id);
l_mfar_extract_tab(i).SELECT_FLAG := p_crh_mfar_extract_record(i).SELECT_FLAG;
INSERT INTO ar_xla_lines_extract VALUES l_mfar_extract_tab(r);
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_insert_crh_extract ()-');
local_log(procedure_name => 'mfar_insert_crh_extract',
p_msg_text => 'EXCEPTION OTHERS in mfar_insert_crh_extract '||
arp_global.CRLF || 'Error :'|| SQLERRM);
'Procedure :arp_xla_extract_main_pkg.mfar_insert_crh_extract'|| arp_global.CRLF||
'Error :'||SQLERRM);
END mfar_insert_crh_extract;
l_extract_tab(i).SELECT_FLAG := p_extract_record(i).SELECT_FLAG;
INSERT INTO ar_xla_lines_extract VALUES l_extract_tab(r);
SELECT application_column_name
FROM FND_SEGMENT_ATTRIBUTE_VALUES
WHERE id_flex_num = p_coa_id
AND segment_attribute_type = p_qual_code
AND id_flex_code = 'GL#'
AND attribute_value = 'Y';
SELECT receivables_trx_id
FROM ar_receivables_trx_all
WHERE org_id = p_org_id
AND type = p_type;
select ard.code_combination_id
into l_ccid
from ar_distributions_all ard
where ard.source_table = 'RA'
and ard.source_type = p_gain_loss_identifier
and ard.source_id = p_ra_id;
SELECT *
FROM ar_xla_lines_extract;
SELECT *
FROM xla_events_gt
WHERE application_id = 222;
DELETE FROM ar_xla_event_tmp;
DELETE FROM ar_xla_lines_extract_tmp;
INSERT INTO ar_xla_event_tmp
(LINE_NUMBER ,
ENTITY_ID ,
APPLICATION_ID ,
LEDGER_ID ,
LEGAL_ENTITY_ID ,
ENTITY_CODE ,
TRANSACTION_NUMBER ,
SOURCE_ID_INT_1 ,
SOURCE_ID_INT_2 ,
SOURCE_ID_INT_3 ,
SOURCE_ID_INT_4 ,
SOURCE_ID_CHAR_1 ,
SOURCE_ID_CHAR_2 ,
SOURCE_ID_CHAR_3 ,
SOURCE_ID_CHAR_4 ,
EVENT_ID ,
EVENT_CLASS_CODE ,
EVENT_TYPE_CODE ,
EVENT_NUMBER ,
EVENT_DATE ,
EVENT_STATUS_CODE ,
PROCESS_STATUS_CODE ,
EVENT_CREATED_BY ,
REFERENCE_NUM_1 ,
REFERENCE_NUM_2 ,
REFERENCE_NUM_3 ,
REFERENCE_NUM_4 ,
REFERENCE_CHAR_1 ,
REFERENCE_CHAR_2 ,
REFERENCE_CHAR_3 ,
REFERENCE_CHAR_4 ,
REFERENCE_DATE_1 ,
REFERENCE_DATE_2 ,
REFERENCE_DATE_3 ,
REFERENCE_DATE_4 ,
VALUATION_METHOD ,
SECURITY_ID_INT_1 ,
SECURITY_ID_INT_2 ,
SECURITY_ID_INT_3 ,
SECURITY_ID_CHAR_1 ,
SECURITY_ID_CHAR_2 ,
SECURITY_ID_CHAR_3 ,
ON_HOLD_FLAG ,
TRANSACTION_DATE ,
BUDGETARY_CONTROL_FLAG )
SELECT
LINE_NUMBER ,
ENTITY_ID ,
APPLICATION_ID ,
LEDGER_ID ,
LEGAL_ENTITY_ID ,
ENTITY_CODE ,
TRANSACTION_NUMBER ,
SOURCE_ID_INT_1 ,
SOURCE_ID_INT_2 ,
SOURCE_ID_INT_3 ,
SOURCE_ID_INT_4 ,
SOURCE_ID_CHAR_1 ,
SOURCE_ID_CHAR_2 ,
SOURCE_ID_CHAR_3 ,
SOURCE_ID_CHAR_4 ,
EVENT_ID ,
EVENT_CLASS_CODE ,
EVENT_TYPE_CODE ,
EVENT_NUMBER ,
EVENT_DATE ,
EVENT_STATUS_CODE ,
PROCESS_STATUS_CODE ,
EVENT_CREATED_BY ,
REFERENCE_NUM_1 ,
REFERENCE_NUM_2 ,
REFERENCE_NUM_3 ,
REFERENCE_NUM_4 ,
REFERENCE_CHAR_1 ,
REFERENCE_CHAR_2 ,
REFERENCE_CHAR_3 ,
REFERENCE_CHAR_4 ,
REFERENCE_DATE_1 ,
REFERENCE_DATE_2 ,
REFERENCE_DATE_3 ,
REFERENCE_DATE_4 ,
VALUATION_METHOD ,
SECURITY_ID_INT_1 ,
SECURITY_ID_INT_2 ,
SECURITY_ID_INT_3 ,
SECURITY_ID_CHAR_1 ,
SECURITY_ID_CHAR_2 ,
SECURITY_ID_CHAR_3 ,
ON_HOLD_FLAG ,
TRANSACTION_DATE ,
BUDGETARY_CONTROL_FLAG
FROM xla_events_gt
WHERE application_id = 222;
INSERT INTO ar_xla_lines_extract_tmp
( EVENT_ID ,
LINE_NUMBER ,
LANGUAGE ,
LEDGER_ID ,
SOURCE_ID ,
SOURCE_TABLE ,
LINE_ID ,
TAX_CODE_ID ,
LOCATION_SEGMENT_ID ,
BASE_CURRENCY_CODE ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_RATE ,
EXCHANGE_DATE ,
ACCTD_AMOUNT ,
TAXABLE_ACCTD_AMOUNT ,
ORG_ID ,
HEADER_TABLE_ID ,
POSTING_ENTITY ,
CASH_RECEIPT_ID ,
CUSTOMER_TRX_ID ,
CUSTOMER_TRX_LINE_ID ,
CUST_TRX_LINE_GL_DIST_ID ,
CUST_TRX_LINE_SALESREP_ID ,
INVENTORY_ITEM_ID ,
SALES_TAX_ID ,
SO_ORGANIZATION_ID ,
TAX_EXEMPTION_ID ,
UOM_CODE ,
WAREHOUSE_ID ,
AGREEMENT_ID ,
CUSTOMER_BANK_ACCT_ID ,
DRAWEE_BANK_ACCOUNT_ID ,
REMITTANCE_BANK_ACCT_ID ,
DISTRIBUTION_SET_ID ,
PAYMENT_SCHEDULE_ID ,
RECEIPT_METHOD_ID ,
RECEIVABLES_TRX_ID ,
ED_ADJ_RECEIVABLES_TRX_ID ,
UNED_RECEIVABLES_TRX_ID ,
SET_OF_BOOKS_ID ,
SALESREP_ID ,
BILL_SITE_USE_ID ,
DRAWEE_SITE_USE_ID ,
PAYING_SITE_USE_ID ,
SOLD_SITE_USE_ID ,
SHIP_SITE_USE_ID ,
RECEIPT_CUSTOMER_SITE_USE_ID ,
BILL_CUST_ROLE_ID ,
DRAWEE_CUST_ROLE_ID ,
SHIP_CUST_ROLE_ID ,
SOLD_CUST_ROLE_ID ,
BILL_CUSTOMER_ID ,
DRAWEE_CUSTOMER_ID ,
PAYING_CUSTOMER_ID ,
SOLD_CUSTOMER_ID ,
SHIP_CUSTOMER_ID ,
REMIT_ADDRESS_ID ,
RECEIPT_BATCH_ID ,
RECEIVABLE_APPLICATION_ID ,
CUSTOMER_BANK_BRANCH_ID ,
ISSUER_BANK_BRANCH_ID ,
BATCH_SOURCE_ID ,
BATCH_ID ,
TERM_ID ,
SELECT_FLAG ,
LEVEL_FLAG ,
FROM_TO_FLAG ,
CRH_STATUS ,
CRH_PRV_STATUS ,
AMOUNT ,
FROM_AMOUNT ,
FROM_ACCTD_AMOUNT ,
PREV_FUND_SEG_REPLACE ,
APP_CRH_STATUS ,
PAIRED_CCID ,
PAIRE_DIST_ID ,
REF_DIST_CCID ,
REF_MF_DIST_FLAG ,
ORIGIN_EXTRACT_TABLE ,
EVENT_TYPE_CODE ,
EVENT_CLASS_CODE ,
ENTITY_CODE ,
REVERSAL_CODE ,
BUSINESS_FLOW_CODE ,
TAX_LINE_ID ,
ADDITIONAL_CHAR1 ,
ADDITIONAL_CHAR2 ,
ADDITIONAL_CHAR3 ,
ADDITIONAL_CHAR4 ,
ADDITIONAL_CHAR5 ,
ADDITIONAL_ID1 ,
ADDITIONAL_ID2 ,
ADDITIONAL_ID3 ,
ADDITIONAL_ID4 ,
ADDITIONAL_ID5 ,
XLA_ENTITY_ID
,REF_CTLGD_CCID
,DIST_CCID
,FROM_EXCHANGE_RATE
,FROM_EXCHANGE_RATE_TYPE
,FROM_EXCHANGE_DATE
,FROM_CURRENCY_CODE
,TO_CURRENCY_CODE
,MFAR_ADDITIONAL_ENTRY
,third_party_id
,third_party_site_id
,third_party_type
,source_type )
SELECT
EVENT_ID ,
LINE_NUMBER ,
LANGUAGE ,
LEDGER_ID ,
SOURCE_ID ,
SOURCE_TABLE ,
LINE_ID ,
TAX_CODE_ID ,
LOCATION_SEGMENT_ID ,
BASE_CURRENCY_CODE ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_RATE ,
EXCHANGE_DATE ,
ACCTD_AMOUNT ,
TAXABLE_ACCTD_AMOUNT ,
ORG_ID ,
HEADER_TABLE_ID ,
POSTING_ENTITY ,
CASH_RECEIPT_ID ,
CUSTOMER_TRX_ID ,
CUSTOMER_TRX_LINE_ID ,
CUST_TRX_LINE_GL_DIST_ID ,
CUST_TRX_LINE_SALESREP_ID ,
INVENTORY_ITEM_ID ,
SALES_TAX_ID ,
SO_ORGANIZATION_ID ,
TAX_EXEMPTION_ID ,
UOM_CODE ,
WAREHOUSE_ID ,
AGREEMENT_ID ,
CUSTOMER_BANK_ACCT_ID ,
DRAWEE_BANK_ACCOUNT_ID ,
REMITTANCE_BANK_ACCT_ID ,
DISTRIBUTION_SET_ID ,
PAYMENT_SCHEDULE_ID ,
RECEIPT_METHOD_ID ,
RECEIVABLES_TRX_ID ,
ED_ADJ_RECEIVABLES_TRX_ID ,
UNED_RECEIVABLES_TRX_ID ,
SET_OF_BOOKS_ID ,
SALESREP_ID ,
BILL_SITE_USE_ID ,
DRAWEE_SITE_USE_ID ,
PAYING_SITE_USE_ID ,
SOLD_SITE_USE_ID ,
SHIP_SITE_USE_ID ,
RECEIPT_CUSTOMER_SITE_USE_ID ,
BILL_CUST_ROLE_ID ,
DRAWEE_CUST_ROLE_ID ,
SHIP_CUST_ROLE_ID ,
SOLD_CUST_ROLE_ID ,
BILL_CUSTOMER_ID ,
DRAWEE_CUSTOMER_ID ,
PAYING_CUSTOMER_ID ,
SOLD_CUSTOMER_ID ,
SHIP_CUSTOMER_ID ,
REMIT_ADDRESS_ID ,
RECEIPT_BATCH_ID ,
RECEIVABLE_APPLICATION_ID ,
CUSTOMER_BANK_BRANCH_ID ,
ISSUER_BANK_BRANCH_ID ,
BATCH_SOURCE_ID ,
BATCH_ID ,
TERM_ID ,
SELECT_FLAG ,
LEVEL_FLAG ,
FROM_TO_FLAG ,
CRH_STATUS ,
CRH_PRV_STATUS ,
AMOUNT ,
FROM_AMOUNT ,
FROM_ACCTD_AMOUNT ,
PREV_FUND_SEG_REPLACE ,
APP_CRH_STATUS ,
PAIRED_CCID ,
PAIRE_DIST_ID ,
REF_DIST_CCID ,
REF_MF_DIST_FLAG ,
ORIGIN_EXTRACT_TABLE ,
EVENT_TYPE_CODE ,
EVENT_CLASS_CODE ,
ENTITY_CODE ,
REVERSAL_CODE ,
BUSINESS_FLOW_CODE ,
TAX_LINE_ID ,
ADDITIONAL_CHAR1 ,
ADDITIONAL_CHAR2 ,
ADDITIONAL_CHAR3 ,
ADDITIONAL_CHAR4 ,
ADDITIONAL_CHAR5 ,
ADDITIONAL_ID1 ,
ADDITIONAL_ID2 ,
ADDITIONAL_ID3 ,
ADDITIONAL_ID4 ,
ADDITIONAL_ID5
,XLA_ENTITY_ID
,REF_CTLGD_CCID
,DIST_CCID
,FROM_EXCHANGE_RATE
,FROM_EXCHANGE_RATE_TYPE
,FROM_EXCHANGE_DATE
,FROM_CURRENCY_CODE
,TO_CURRENCY_CODE
,MFAR_ADDITIONAL_ENTRY
,third_party_id
,third_party_site_id
,third_party_type
,source_type
FROM ar_xla_lines_extract;
local_log('diag_data',''||l_c.SELECT_FLAG||' ');