[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 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);
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
,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
--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
--
-- 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');
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
)
-- 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,trxf.exchange_date), -- EXCHANGE_DATE
/* End bug7311808 -vavenugo */
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
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 = 'REC' /* Bug 6119725 Start Changes */
AND ((dist.ref_cust_trx_line_gl_dist_id IS NOT NULL
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
AND 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 (((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
,trx.exchange_date -- 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 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
'', -- 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
'', -- 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';
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
hd.exchange_date, -- 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
--}
)
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
--}
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
)
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
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
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')
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) 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
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
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')
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 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
AND 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 (((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) 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
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
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) 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;
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)
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
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')
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.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(+);
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 )
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','CASH')
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')
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')
AND cr.set_of_books_id = lg.ledger_id;
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
--
,third_party_id
,third_party_site_id
,third_party_type
,source_type )
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
,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
,SIGN(crh.amount)*app.from_amount --from_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
,SIGN(crh.amount)*app.amount --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
FROM ar_crh_gt crh,
ar_crh_app_gt app,
(SELECT 'CLEARED' AS status FROM DUAL UNION
SELECT 'REMITTED' AS status FROM DUAL UNION
SELECT 'CONFIRMED' AS status FROM DUAL ) state,
xla_events_gt gt
WHERE crh.entity_id = app.entity_id
AND app.source_table = 'RA'
AND DECODE(state.status,'CLEARED' ,DECODE(crh.status,'CLEARED' ,'Y','N'),
'REMITTED' ,DECODE(crh.status,'REMITTED' ,'Y','N'),
'CONFIRMED' ,DECODE(crh.status,'CONFIRMED','Y','N'),'N') = 'Y'
AND DECODE(state.status,'CLEARED' ,DECODE(app.cash_receipt_history_status,
'CLEARED' ,'Y',
'REMITTED' ,'Y',
'CONFIRMED','Y','N'),
'REMITTED' ,DECODE(app.cash_receipt_history_status,
'REMITTED' ,'Y',
'CONFIRMED','Y','N'),
'CONFIRMED' ,DECODE(app.cash_receipt_history_status,
'CONFIRMED','Y','N'),'N') = 'Y'
AND crh.event_id = gt.event_id;
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
--
,third_party_id
,third_party_site_id
,third_party_type
,source_type)
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
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
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.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);
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)
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
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 mcd.event_id <> 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)*abs(mcd.from_amount) --from_amount
,SIGN(crh.acctd_amount)*abs(mcd.from_acctd_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)*abs(mcd.amount) --amount
,SIGN(crh.acctd_amount)*abs(mcd.acctd_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 = 'CR'
AND select_flag = 'Y'
AND level_flag = 'H'
AND set_of_books_id IS NOT NULL;
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
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
('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
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
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
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
WHERE posting_control_id = -3
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
('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
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 ';
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 in ('EXCH_GAIN','EXCH_LOSS')
and ard.source_id = 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||' ');