[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sob.set_of_books_id,
sob.chart_of_accounts_id,
sob.currency_code,
c.precision,
c.minimum_accountable_unit,
sysp.code_combination_id_gain,
sysp.code_combination_id_loss,
sysp.code_combination_id_round,
sysp.accounting_method
INTO g_ae_sys_rec.set_of_books_id,
g_ae_sys_rec.coa_id,
g_ae_sys_rec.base_currency,
g_ae_sys_rec.base_precision,
g_ae_sys_rec.base_min_acc_unit,
g_ae_sys_rec.gain_cc_id,
g_ae_sys_rec.loss_cc_id,
g_ae_sys_rec.round_cc_id,
x_accounting_method
FROM ar_system_parameters_all sysp,
gl_sets_of_books sob,
fnd_currencies c
WHERE sysp.org_id = p_org_id
AND sob.set_of_books_id = sysp.set_of_books_id --would be the row returned from multi org view
AND sob.currency_code = c.currency_code;
UPDATE ar_receivable_applications_all ra
SET ra.upgrade_method = 'R12_11ICASH_POST'
WHERE ra.receivable_application_id IN (
SELECT app.receivable_application_id
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1'
FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11I'
AND adj.status = 'A'
AND adj.postable = 'Y'));
INSERT INTO RA_AR_GT
( GT_ID ,
AMT ,
ACCTD_AMT ,
ACCOUNT_CLASS ,
CCID_SECONDARY ,
REF_CUST_TRX_LINE_GL_DIST_ID,
REF_CUSTOMER_TRX_LINE_ID ,
REF_CUSTOMER_TRX_ID ,
TO_CURRENCY ,
BASE_CURRENCY ,
-- ADJ and APP Elmt
DIST_AMT ,
DIST_ACCTD_AMT ,
DIST_CHRG_AMT ,
DIST_CHRG_ACCTD_AMT ,
DIST_FRT_AMT ,
DIST_FRT_ACCTD_AMT ,
DIST_TAX_AMT ,
DIST_TAX_ACCTD_AMT ,
-- Buc
tl_alloc_amt ,
tl_alloc_acctd_amt ,
tl_chrg_alloc_amt ,
tl_chrg_alloc_acctd_amt,
tl_frt_alloc_amt ,
tl_frt_alloc_acctd_amt,
tl_tax_alloc_amt ,
tl_tax_alloc_acctd_amt,
-- ED Elmt
DIST_ed_AMT,
DIST_ed_ACCTD_AMT,
DIST_ed_chrg_AMT,
DIST_ed_chrg_ACCTD_AMT,
DIST_ed_frt_AMT ,
DIST_ed_frt_ACCTD_AMT,
DIST_ed_tax_AMT ,
DIST_ed_tax_ACCTD_AMT,
--
tl_ed_alloc_amt ,
tl_ed_alloc_acctd_amt ,
tl_ed_chrg_alloc_amt ,
tl_ed_chrg_alloc_acctd_amt,
tl_ed_frt_alloc_amt ,
tl_ed_frt_alloc_acctd_amt,
tl_ed_tax_alloc_amt ,
tl_ed_tax_alloc_acctd_amt,
-- UNED
DIST_uned_AMT ,
DIST_uned_ACCTD_AMT ,
DIST_uned_chrg_AMT ,
DIST_uned_chrg_ACCTD_AMT ,
DIST_uned_frt_AMT ,
DIST_uned_frt_ACCTD_AMT ,
DIST_uned_tax_AMT ,
DIST_uned_tax_ACCTD_AMT ,
--
tl_uned_alloc_amt ,
tl_uned_alloc_acctd_amt ,
tl_uned_chrg_alloc_amt ,
tl_uned_chrg_alloc_acctd_amt,
tl_uned_frt_alloc_amt ,
tl_uned_frt_alloc_acctd_amt,
tl_uned_tax_alloc_amt ,
tl_uned_tax_alloc_acctd_amt,
--
source_type ,
source_table ,
source_id ,
line_type,
--
group_id,
--{HYUBPAGP
source_data_key1 ,
source_data_key2 ,
source_data_key3 ,
source_data_key4 ,
source_data_key5 ,
--}
gp_level,
--
set_of_books_id,
sob_type,
se_gt_id,
--{Taxable Amount
tax_link_id,
tax_inc_flag,
--}
tax_code_id,
location_segment_id
)
SELECT
p_gt_id -- GT_ID
, ctlgd.amount -- AMT
, ctlgd.acctd_amount -- ACCTD_AMT
, DECODE(ctl.line_type,'LINE','REV',
'TAX','TAX',
'FREIGHT','FREIGHT',
'CHARGES','CHARGES',
'CB','REV') -- ACCOUNT_CLASS
, DECODE(ctlgd.collected_tax_ccid,
NULL, ctlgd.code_combination_id,
0 , ctlgd.code_combination_id,
ctlgd.collected_tax_ccid) -- CCID_SECONDARY
, DECODE(ctl.line_type,'LINE',-6,
'TAX',-8,
'FREIGHT',-9,
'CHARGES',-7,
'CB',-6) -- REF_CUST_TRX_LINE_GL_DIST_ID
--, ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
, DECODE(ctl.line_type,'LINE',-6,
'TAX',-8,
'FREIGHT',-9,
'CHARGES',-7,
'CB',-6) -- REF_CUSTOMER_TRX_LINE_ID
, trx.customer_trx_id -- REF_CUSTOMER_TRX_ID
, trx.invoice_currency_code -- TO_CURRENCY
, NULL -- BASE_CURRENCY
-- ADJ and APP Elmt
, DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_CHRG_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_CHRG_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_FRT_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_FRT_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_TAX_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_TAX_ACCTD_AMT
-- Buc
, 0 -- tl_alloc_amt
, 0 -- tl_alloc_acctd_amt
, 0 -- tl_chrg_alloc_amt
, 0 -- tl_chrg_alloc_acctd_amt
, 0 -- tl_frt_alloc_amt
, 0 -- tl_frt_alloc_acctd_amt
, 0 -- tl_tax_alloc_amt
, 0 -- tl_tax_alloc_acctd_amt
-- ED Elmt
, DECODE(ctl.line_type,'LINE',ctlgd.amount,
'CB' ,ctlgd.amount, 0) -- DIST_ed_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,
'CB' ,ctlgd.acctd_amount, 0) -- DIST_ed_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_ed_chrg_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_ed_chrg_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_ed_frt_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_ed_frt_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_ed_tax_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_ed_tax_ACCTD_AMT
--
, 0 -- tl_ed_alloc_amt
, 0 -- tl_ed_alloc_acctd_amt
, 0 -- tl_ed_chrg_alloc_amt
, 0 -- tl_ed_chrg_alloc_acctd_amt
, 0 -- tl_ed_frt_alloc_amt
, 0 -- tl_ed_frt_alloc_acctd_amt
, 0 -- tl_ed_tax_alloc_amt
, 0 -- tl_ed_tax_alloc_acctd_amt
-- UNED
, DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_uned_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_uned_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_uned_chrg_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_uned_chrg_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_uned_frt_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_uned_frt_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_uned_tax_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_uned_tax_ACCTD_AMT
--
, 0 -- tl_uned_alloc_amt
, 0 -- tl_uned_alloc_acctd_amt
, 0 -- tl_uned_chrg_alloc_amt
, 0 -- tl_uned_chrg_alloc_acctd_amt
, 0 -- tl_uned_frt_alloc_amt
, 0 -- tl_uned_frt_alloc_acctd_amt
, 0 -- tl_uned_tax_alloc_amt
, 0 -- tl_uned_tax_alloc_acctd_amt
--
, NULL -- source_type
, 'CTLGD' -- source_table
, NULL -- source_id
, ctl.line_type -- line_type
--
, NULL -- group_id
, '00' -- source_data_key1
, '00' -- source_data_key2
, '00' -- source_data_key3
, '00' -- source_data_key4
, '00' -- source_data_key5
, 'D' -- gp_level
--
, trx.set_of_books_id -- set_of_books_id
, 'P' -- sob_type
, USERENV('SESSIONID') -- se_gt_id
--{Taxable Amount
, NULL -- tax_link_id
, NULL -- tax_inc_flag
--}
, DECODE(ctl.line_type,'LINE',taxx.vat_tax_id,ctl.vat_tax_id) vat_tax_id
, DECODE(ctl.line_type,'LINE',taxx.location_segment_id,ctl.location_segment_id)
FROM ra_customer_trx trx,
ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist ctlgd,
( select ctl_tax.*
from ra_customer_trx_lines ctl_tax
where customer_trx_id = p_trx_id
and nvl(ctl_tax.line_number,1) = 1
and line_type = 'TAX'
) taxx
WHERE trx.customer_trx_id = p_trx_id
AND ctl.customer_trx_id = trx.customer_trx_id
AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
AND ctl.customer_trx_line_id = taxx.link_to_cust_trx_line_id(+)
AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
AND ctlgd.account_set_flag = 'N'
AND NOT EXISTS (SELECT '1' FROM RA_AR_GT
WHERE source_table = 'CTLGD'
AND REF_CUSTOMER_TRX_ID = p_trx_id );
INSERT INTO RA_AR_GT
( GT_ID ,
AMT ,
ACCTD_AMT ,
ACCOUNT_CLASS ,
CCID_SECONDARY ,
REF_CUST_TRX_LINE_GL_DIST_ID,
REF_CUSTOMER_TRX_LINE_ID ,
REF_CUSTOMER_TRX_ID ,
TO_CURRENCY ,
BASE_CURRENCY ,
-- ADJ and APP Elmt
DIST_AMT ,
DIST_ACCTD_AMT ,
DIST_CHRG_AMT ,
DIST_CHRG_ACCTD_AMT ,
DIST_FRT_AMT ,
DIST_FRT_ACCTD_AMT ,
DIST_TAX_AMT ,
DIST_TAX_ACCTD_AMT ,
-- Buc
tl_alloc_amt ,
tl_alloc_acctd_amt ,
tl_chrg_alloc_amt ,
tl_chrg_alloc_acctd_amt,
tl_frt_alloc_amt ,
tl_frt_alloc_acctd_amt,
tl_tax_alloc_amt ,
tl_tax_alloc_acctd_amt,
-- ED Elmt
DIST_ed_AMT,
DIST_ed_ACCTD_AMT,
DIST_ed_chrg_AMT,
DIST_ed_chrg_ACCTD_AMT,
DIST_ed_frt_AMT ,
DIST_ed_frt_ACCTD_AMT,
DIST_ed_tax_AMT ,
DIST_ed_tax_ACCTD_AMT,
--
tl_ed_alloc_amt ,
tl_ed_alloc_acctd_amt ,
tl_ed_chrg_alloc_amt ,
tl_ed_chrg_alloc_acctd_amt,
tl_ed_frt_alloc_amt ,
tl_ed_frt_alloc_acctd_amt,
tl_ed_tax_alloc_amt ,
tl_ed_tax_alloc_acctd_amt,
-- UNED
DIST_uned_AMT ,
DIST_uned_ACCTD_AMT ,
DIST_uned_chrg_AMT ,
DIST_uned_chrg_ACCTD_AMT ,
DIST_uned_frt_AMT ,
DIST_uned_frt_ACCTD_AMT ,
DIST_uned_tax_AMT ,
DIST_uned_tax_ACCTD_AMT ,
--
tl_uned_alloc_amt ,
tl_uned_alloc_acctd_amt ,
tl_uned_chrg_alloc_amt ,
tl_uned_chrg_alloc_acctd_amt,
tl_uned_frt_alloc_amt ,
tl_uned_frt_alloc_acctd_amt,
tl_uned_tax_alloc_amt ,
tl_uned_tax_alloc_acctd_amt,
--
source_type ,
source_table ,
source_id ,
line_type,
--
group_id,
source_data_key1 ,
source_data_key2 ,
source_data_key3 ,
source_data_key4 ,
source_data_key5 ,
gp_level,
--
set_of_books_id,
sob_type,
se_gt_id,
tax_link_id,
tax_inc_flag,
tax_code_id,
location_segment_id
)
SELECT
p_gt_id -- GT_ID
, ctlgd.amount -- AMT
, ctlgd.acctd_amount -- ACCTD_AMT
, DECODE(ctl.line_type,'LINE','REV',
'TAX','TAX',
'FREIGHT','FREIGHT',
'CHARGES','CHARGES',
'CB','REV') -- ACCOUNT_CLASS
, DECODE(ctlgd.collected_tax_ccid,
NULL, ctlgd.code_combination_id,
0 , ctlgd.code_combination_id,
ctlgd.collected_tax_ccid) -- CCID_SECONDARY
, ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
, DECODE(ctl.line_type,'LINE',-6,
'TAX',-8,
'FREIGHT',-9,
'CHARGES',-7,
'CB',-6) --ctl.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
, trx.customer_trx_id -- REF_CUSTOMER_TRX_ID
, trx.invoice_currency_code -- TO_CURRENCY
, NULL -- BASE_CURRENCY
-- ADJ and APP Elmt
, DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_CHRG_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_CHRG_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_FRT_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_FRT_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_TAX_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_TAX_ACCTD_AMT
-- Buc
, 0 -- tl_alloc_amt
, 0 -- tl_alloc_acctd_amt
, 0 -- tl_chrg_alloc_amt
, 0 -- tl_chrg_alloc_acctd_amt
, 0 -- tl_frt_alloc_amt
, 0 -- tl_frt_alloc_acctd_amt
, 0 -- tl_tax_alloc_amt
, 0 -- tl_tax_alloc_acctd_amt
-- ED Elmt
, DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_ed_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ed_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_ed_chrg_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_ed_chrg_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_ed_frt_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_ed_frt_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_ed_tax_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_ed_tax_ACCTD_AMT
--
, 0 -- tl_ed_alloc_amt
, 0 -- tl_ed_alloc_acctd_amt
, 0 -- tl_ed_chrg_alloc_amt
, 0 -- tl_ed_chrg_alloc_acctd_amt
, 0 -- tl_ed_frt_alloc_amt
, 0 -- tl_ed_frt_alloc_acctd_amt
, 0 -- tl_ed_tax_alloc_amt
, 0 -- tl_ed_tax_alloc_acctd_amt
-- UNED
, DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_uned_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_uned_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_uned_chrg_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_uned_chrg_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_uned_frt_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_uned_frt_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_uned_tax_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_uned_tax_ACCTD_AMT
--
, 0 -- tl_uned_alloc_amt
, 0 -- tl_uned_alloc_acctd_amt
, 0 -- tl_uned_chrg_alloc_amt
, 0 -- tl_uned_chrg_alloc_acctd_amt
, 0 -- tl_uned_frt_alloc_amt
, 0 -- tl_uned_frt_alloc_acctd_amt
, 0 -- tl_uned_tax_alloc_amt
, 0 -- tl_uned_tax_alloc_acctd_amt
--
, NULL -- source_type
, 'CTLGD' -- source_table
, NULL -- source_id
, ctl.line_type -- line_type
--
, NULL -- group_id
, '00' -- source_data_key1
, '00' -- source_data_key2
, '00' -- source_data_key3
, '00' -- source_data_key4
, '00' -- source_data_key5
, 'D' -- gp_level
--
, trx.set_of_books_id -- set_of_books_id
, 'P' -- sob_type
, USERENV('SESSIONID') -- se_gt_id
, NULL -- tax_link_id
, NULL -- tax_inc_flag
, DECODE(ctl.line_type,'LINE',taxx.vat_tax_id,ctl.vat_tax_id) vat_tax_id
, DECODE(ctl.line_type,'LINE',taxx.location_segment_id,ctl.location_segment_id)
FROM xla_events_gt evt,
ar_receivable_applications_all app,
ar_system_parameters_all ars,
ra_customer_trx_all trx,
ra_customer_trx_lines_all ctl,
ra_cust_trx_line_gl_dist_all ctlgd,
( select ctl_tax.*
from ra_customer_trx_lines ctl_tax
where nvl(ctl_tax.line_number,1) = 1
and line_type = 'TAX'
) taxx
WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE' )
AND evt.application_id = 222
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND app.org_id = ars.org_id
AND ars.accounting_method = 'CASH'
AND app.applied_customer_trx_id = trx.customer_trx_id
AND trx.customer_trx_id = ctl.customer_trx_id
AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
AND ctlgd.account_set_flag = 'N'
AND ctl.customer_trx_line_id = taxx.link_to_cust_trx_line_id(+)
AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11I'
AND adj.status = 'A'
AND adj.postable = 'Y');
SELECT DISTINCT inv.customer_trx_id,
inv.invoice_currency_code,
inv.set_of_books_id
FROM xla_events_gt evt,
ar_receivable_applications_all app,
ra_customer_trx_all inv,
ar_system_parameters_all ars
WHERE evt.event_type_code
IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE' )
AND evt.event_id = app.event_id
AND app.applied_customer_trx_id = inv.customer_trx_id
AND app.upgrade_method IS NULL
AND ars.org_id = app.org_id
AND ars.accounting_method = 'CASH'
AND NOT EXISTS ( SELECT '1'
FROM psa_trx_types_all psa
WHERE inv.cust_trx_type_id = psa.psa_trx_type_id)
UNION
-- From CM in the case of CM APP
SELECT DISTINCT inv.customer_trx_id,
inv.invoice_currency_code,
inv.set_of_books_id
FROM xla_events_gt evt,
ar_receivable_applications_all app,
ra_customer_trx_all inv,
ar_system_parameters_all ars
WHERE evt.event_type_code
IN ( 'CM_CREATE' ,'CM_UPDATE' )
AND evt.event_id = app.event_id
AND app.customer_trx_id = inv.customer_trx_id
AND app.upgrade_method IS NULL
AND ars.org_id = app.org_id
AND ars.accounting_method = 'CASH'
AND NOT EXISTS ( SELECT '1'
FROM psa_trx_types_all psa
WHERE inv.cust_trx_type_id = psa.psa_trx_type_id)) trx,
ra_customer_trx_lines_all ctl,
ra_cust_trx_line_gl_dist_all ctlgd
WHERE trx.customer_trx_id = ctl.customer_trx_id
AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
AND ctlgd.account_set_flag = 'N';
INSERT INTO RA_AR_GT
( GT_ID ,
AMT ,
ACCTD_AMT ,
ACCOUNT_CLASS ,
CCID_SECONDARY ,
REF_CUST_TRX_LINE_GL_DIST_ID,
REF_CUSTOMER_TRX_LINE_ID ,
REF_CUSTOMER_TRX_ID ,
TO_CURRENCY ,
BASE_CURRENCY ,
-- ADJ and APP Elmt
DIST_AMT ,
DIST_ACCTD_AMT ,
DIST_CHRG_AMT ,
DIST_CHRG_ACCTD_AMT ,
DIST_FRT_AMT ,
DIST_FRT_ACCTD_AMT ,
DIST_TAX_AMT ,
DIST_TAX_ACCTD_AMT ,
-- Buc
tl_alloc_amt ,
tl_alloc_acctd_amt ,
tl_chrg_alloc_amt ,
tl_chrg_alloc_acctd_amt,
tl_frt_alloc_amt ,
tl_frt_alloc_acctd_amt,
tl_tax_alloc_amt ,
tl_tax_alloc_acctd_amt,
-- ED Elmt
DIST_ed_AMT,
DIST_ed_ACCTD_AMT,
DIST_ed_chrg_AMT,
DIST_ed_chrg_ACCTD_AMT,
DIST_ed_frt_AMT ,
DIST_ed_frt_ACCTD_AMT,
DIST_ed_tax_AMT ,
DIST_ed_tax_ACCTD_AMT,
--
tl_ed_alloc_amt ,
tl_ed_alloc_acctd_amt ,
tl_ed_chrg_alloc_amt ,
tl_ed_chrg_alloc_acctd_amt,
tl_ed_frt_alloc_amt ,
tl_ed_frt_alloc_acctd_amt,
tl_ed_tax_alloc_amt ,
tl_ed_tax_alloc_acctd_amt,
-- UNED
DIST_uned_AMT ,
DIST_uned_ACCTD_AMT ,
DIST_uned_chrg_AMT ,
DIST_uned_chrg_ACCTD_AMT ,
DIST_uned_frt_AMT ,
DIST_uned_frt_ACCTD_AMT ,
DIST_uned_tax_AMT ,
DIST_uned_tax_ACCTD_AMT ,
--
tl_uned_alloc_amt ,
tl_uned_alloc_acctd_amt ,
tl_uned_chrg_alloc_amt ,
tl_uned_chrg_alloc_acctd_amt,
tl_uned_frt_alloc_amt ,
tl_uned_frt_alloc_acctd_amt,
tl_uned_tax_alloc_amt ,
tl_uned_tax_alloc_acctd_amt,
--
source_type ,
source_table ,
source_id ,
line_type,
--
group_id,
source_data_key1 ,
source_data_key2 ,
source_data_key3 ,
source_data_key4 ,
source_data_key5 ,
gp_level,
--
set_of_books_id,
sob_type,
se_gt_id,
tax_link_id,
tax_inc_flag
)
SELECT
p_gt_id -- GT_ID
, ctlgd.amount -- AMT
, ctlgd.acctd_amount -- ACCTD_AMT
, DECODE(ctl.line_type,'LINE','REV',
'TAX','TAX',
'FREIGHT','FREIGHT',
'CHARGES','CHARGES',
'CB','REV') -- ACCOUNT_CLASS
, DECODE(ctlgd.collected_tax_ccid,
NULL, ctlgd.code_combination_id,
0 , ctlgd.code_combination_id,
ctlgd.collected_tax_ccid) -- CCID_SECONDARY
, ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
, ctl.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
, trx.customer_trx_id -- REF_CUSTOMER_TRX_ID
, trx.invoice_currency_code -- TO_CURRENCY
, NULL -- BASE_CURRENCY
-- ADJ and APP Elmt
, DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_CHRG_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_CHRG_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_FRT_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_FRT_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_TAX_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_TAX_ACCTD_AMT
-- Buc
, 0 -- tl_alloc_amt
, 0 -- tl_alloc_acctd_amt
, 0 -- tl_chrg_alloc_amt
, 0 -- tl_chrg_alloc_acctd_amt
, 0 -- tl_frt_alloc_amt
, 0 -- tl_frt_alloc_acctd_amt
, 0 -- tl_tax_alloc_amt
, 0 -- tl_tax_alloc_acctd_amt
-- ED Elmt
, DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_ed_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ed_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_ed_chrg_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_ed_chrg_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_ed_frt_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_ed_frt_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_ed_tax_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_ed_tax_ACCTD_AMT
--
, 0 -- tl_ed_alloc_amt
, 0 -- tl_ed_alloc_acctd_amt
, 0 -- tl_ed_chrg_alloc_amt
, 0 -- tl_ed_chrg_alloc_acctd_amt
, 0 -- tl_ed_frt_alloc_amt
, 0 -- tl_ed_frt_alloc_acctd_amt
, 0 -- tl_ed_tax_alloc_amt
, 0 -- tl_ed_tax_alloc_acctd_amt
-- UNED
, DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_uned_AMT
, DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_uned_ACCTD_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_uned_chrg_AMT
, DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_uned_chrg_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_uned_frt_AMT
, DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_uned_frt_ACCTD_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_uned_tax_AMT
, DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_uned_tax_ACCTD_AMT
--
, 0 -- tl_uned_alloc_amt
, 0 -- tl_uned_alloc_acctd_amt
, 0 -- tl_uned_chrg_alloc_amt
, 0 -- tl_uned_chrg_alloc_acctd_amt
, 0 -- tl_uned_frt_alloc_amt
, 0 -- tl_uned_frt_alloc_acctd_amt
, 0 -- tl_uned_tax_alloc_amt
, 0 -- tl_uned_tax_alloc_acctd_amt
--
, NULL -- source_type
, 'CTLGD' -- source_table
, NULL -- source_id
, ctl.line_type -- line_type
--
, NULL -- group_id
, '00' -- source_data_key1
, '00' -- source_data_key2
, '00' -- source_data_key3
, '00' -- source_data_key4
, '00' -- source_data_key5
, 'D' -- gp_level
--
, trx.set_of_books_id -- set_of_books_id
, 'P' -- sob_type
, USERENV('SESSIONID') -- se_gt_id
, NULL -- tax_link_id
, NULL -- tax_inc_flag
FROM ( -- Applied to transactions
SELECT DISTINCT inv.customer_trx_id,
inv.invoice_currency_code,
inv.set_of_books_id
FROM xla_events_gt evt,
ar_receivable_applications_all app,
ra_customer_trx_all inv,
psa_trx_types_all psa
WHERE evt.event_type_code
IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE' )
AND evt.event_id = app.event_id
AND app.applied_customer_trx_id = inv.customer_trx_id
AND inv.cust_trx_type_id = psa.psa_trx_type_id
AND app.upgrade_method IS NULL
UNION
-- From CM in the case of CM APP
SELECT DISTINCT inv.customer_trx_id,
inv.invoice_currency_code,
inv.set_of_books_id
FROM xla_events_gt evt,
ar_receivable_applications_all app,
ra_customer_trx_all inv,
psa_trx_types_all psa
WHERE evt.event_type_code
IN ( 'CM_CREATE' ,'CM_UPDATE' )
AND evt.event_id = app.event_id
AND app.customer_trx_id = inv.customer_trx_id
AND inv.cust_trx_type_id = psa.psa_trx_type_id
AND app.upgrade_method IS NULL) trx,
ra_customer_trx_lines_all ctl,
ra_cust_trx_line_gl_dist_all ctlgd
WHERE trx.customer_trx_id = ctl.customer_trx_id
AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
AND ctlgd.account_set_flag = 'N';
INSERT INTO RA_AR_GT
( GT_ID ,
AMT ,
ACCTD_AMT ,
ACCOUNT_CLASS ,
CCID_SECONDARY ,
REF_CUST_TRX_LINE_GL_DIST_ID,
REF_CUSTOMER_TRX_LINE_ID ,
REF_CUSTOMER_TRX_ID ,
TO_CURRENCY ,
BASE_CURRENCY ,
-- ADJ and APP Elmt
DIST_AMT ,
DIST_ACCTD_AMT ,
DIST_CHRG_AMT ,
DIST_CHRG_ACCTD_AMT ,
DIST_FRT_AMT ,
DIST_FRT_ACCTD_AMT ,
DIST_TAX_AMT ,
DIST_TAX_ACCTD_AMT ,
-- Buc
tl_alloc_amt ,
tl_alloc_acctd_amt ,
tl_chrg_alloc_amt ,
tl_chrg_alloc_acctd_amt,
tl_frt_alloc_amt ,
tl_frt_alloc_acctd_amt,
tl_tax_alloc_amt ,
tl_tax_alloc_acctd_amt,
-- ED Elmt
DIST_ed_AMT,
DIST_ed_ACCTD_AMT,
DIST_ed_chrg_AMT,
DIST_ed_chrg_ACCTD_AMT,
DIST_ed_frt_AMT ,
DIST_ed_frt_ACCTD_AMT,
DIST_ed_tax_AMT ,
DIST_ed_tax_ACCTD_AMT,
--
tl_ed_alloc_amt ,
tl_ed_alloc_acctd_amt ,
tl_ed_chrg_alloc_amt ,
tl_ed_chrg_alloc_acctd_amt,
tl_ed_frt_alloc_amt ,
tl_ed_frt_alloc_acctd_amt,
tl_ed_tax_alloc_amt ,
tl_ed_tax_alloc_acctd_amt,
-- UNED
DIST_uned_AMT ,
DIST_uned_ACCTD_AMT ,
DIST_uned_chrg_AMT ,
DIST_uned_chrg_ACCTD_AMT ,
DIST_uned_frt_AMT ,
DIST_uned_frt_ACCTD_AMT ,
DIST_uned_tax_AMT ,
DIST_uned_tax_ACCTD_AMT ,
--
tl_uned_alloc_amt ,
tl_uned_alloc_acctd_amt ,
tl_uned_chrg_alloc_amt ,
tl_uned_chrg_alloc_acctd_amt,
tl_uned_frt_alloc_amt ,
tl_uned_frt_alloc_acctd_amt,
tl_uned_tax_alloc_amt ,
tl_uned_tax_alloc_acctd_amt,
--
source_type ,
source_table ,
source_id ,
line_type,
--
group_id,
source_data_key1 ,
source_data_key2 ,
source_data_key3 ,
source_data_key4 ,
source_data_key5 ,
gp_level,
--
set_of_books_id,
sob_type,
se_gt_id,
--{Taxable Amount
tax_link_id,
tax_inc_flag,
--}
ref_line_id,
tax_code_id,
location_segment_id
)
SELECT
p_gt_id -- GT_ID
, NVL(ard.amount_cr,0)
- NVL(ard.amount_dr,0) -- AMT
, NVL(ard.acctd_amount_cr,0)
- NVL(ard.acctd_amount_dr,0) -- ACCTD_AMT
, DECODE(adj.type,
'LINE',DECODE(ard.source_type,'ADJ','REV',
'TAX','TAX',
'DEFERRED_TAX','TAX',
'ADJ_NON_REC_TAX','TAX','REV'),
'TAX' ,DECODE(ard.source_type,'TAX','TAX',
'ADJ','TAX',
'DEFERRED_TAX','TAX',
'ADJ_NON_REC_TAX','TAX','TAX'),
'FREIGHT' ,DECODE(ard.source_type,'ADJ',
'FREIGHT','FREIGHT'),
'CHARGES',DECODE(ard.source_type,'FINCHRG',
'CHARGES','CHARGES'),
'REV') -- ACCOUNT_CLASS
, ard.code_combination_id -- CCID_SECONDARY
, DECODE(adj.type,
'LINE',DECODE(ard.source_type,'ADJ',-6,
'TAX',-8,
'DEFERRED_TAX',-8,
'ADJ_NON_REC_TAX',-8,-6),
'TAX' ,DECODE(ard.source_type,'TAX',-8,
'ADJ',-8,
'DEFERRED_TAX',-8,
'ADJ_NON_REC_TAX',-8,-8),
'FREIGHT' ,DECODE(ard.source_type,'ADJ',
-9,-9),
'CHARGES',DECODE(ard.source_type,'FINCHRG',
-7,-7),
-6) -- REF_CUST_TRX_LINE_GL_DIST_ID
, DECODE(adj.type,
'LINE',DECODE(ard.source_type,'ADJ',-6,
'TAX',-8,
'DEFERRED_TAX',-8,
'ADJ_NON_REC_TAX',-8,-6),
'TAX' ,DECODE(ard.source_type,'TAX',-8,
'ADJ',-8,
'DEFERRED_TAX',-8,
'ADJ_NON_REC_TAX',-8,-8),
'FREIGHT' ,DECODE(ard.source_type,'ADJ',
-9,-9),
'CHARGES',DECODE(ard.source_type,'FINCHRG',
-7,-7),
-6) -- REF_CUSTOMER_TRX_LINE_ID
, adj.customer_trx_id -- REF_CUSTOMER_TRX_ID
, trx.invoice_currency_code -- TO_CURRENCY
, NULL -- BASE_CURRENCY
-- ADJ and APP Elmt
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_AMT
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ACCTD_AMT
--
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_CHRG_AMT
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_CHRG_ACCTD_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_FRT_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_FRT_ACCTD_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_TAX_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_TAX_ACCTD_AMT
-- Buc
, 0 -- tl_alloc_amt
, 0 -- tl_alloc_acctd_amt
, 0 -- tl_chrg_alloc_amt
, 0 -- tl_chrg_alloc_acctd_amt
, 0 -- tl_frt_alloc_amt
, 0 -- tl_frt_alloc_acctd_amt
, 0 -- tl_tax_alloc_amt
, 0 -- tl_tax_alloc_acctd_amt
-- ED Elmt
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_ed_AMT
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ed_ACCTD_AMT
--
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_ed_chrg_AMT
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ed_chrg_ACCTD_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_ed_frt_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ed_frt_ACCTD_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_ed_tax_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ed_tax_ACCTD_AMT
--, 0 -- DIST_ed_AMT
--, 0 -- DIST_ed_ACCTD_AMT
--, 0 -- DIST_ed_chrg_AMT
--, 0 -- DIST_ed_chrg_ACCTD_AMT
--, 0 -- DIST_ed_frt_AMT
--, 0 -- DIST_ed_frt_ACCTD_AMT
--, 0 -- DIST_ed_tax_AMT
--, 0 -- DIST_ed_tax_ACCTD_AMT
--
, 0 -- tl_ed_alloc_amt
, 0 -- tl_ed_alloc_acctd_amt
, 0 -- tl_ed_chrg_alloc_amt
, 0 -- tl_ed_chrg_alloc_acctd_amt
, 0 -- tl_ed_frt_alloc_amt
, 0 -- tl_ed_frt_alloc_acctd_amt
, 0 -- tl_ed_tax_alloc_amt
, 0 -- tl_ed_tax_alloc_acctd_amt
-- UNED
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_uned_AMT
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_uned_ACCTD_AMT
--
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_uned_chrg_AMT
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_uned_chrg_ACCTD_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_uned_frt_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_uned_frt_ACCTD_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_uned_tax_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_uned_tax_ACCTD_AMT
--, 0 -- DIST_uned_AMT
--, 0 -- DIST_uned_ACCTD_AMT
--, 0 -- DIST_uned_chrg_AMT
--, 0 -- DIST_uned_chrg_ACCTD_AMT
--, 0 -- DIST_uned_frt_AMT
--, 0 -- DIST_uned_frt_ACCTD_AMT
--, 0 -- DIST_uned_tax_AMT
--, 0 -- DIST_uned_tax_ACCTD_AMT
--
, 0 -- tl_uned_alloc_amt
, 0 -- tl_uned_alloc_acctd_amt
, 0 -- tl_uned_chrg_alloc_amt
, 0 -- tl_uned_chrg_alloc_acctd_amt
, 0 -- tl_uned_frt_alloc_amt
, 0 -- tl_uned_frt_alloc_acctd_amt
, 0 -- tl_uned_tax_alloc_amt
, 0 -- tl_uned_tax_alloc_acctd_amt
--
, ard.source_type -- source_type
, ard.source_table -- source_table
, ard.source_id -- source_id
, DECODE(adj.type,
'LINE',DECODE(ard.source_type,'ADJ','LINE',
'TAX','TAX',
'DEFERRED_TAX','TAX','LINE'),
'TAX','TAX',
'CHARGES','CHARGES',
'FREIGHT','FREIGHT', 'LINE') -- line_type
--
, NULL -- group_id
, '00' -- source_data_key1
, '00' -- source_data_key2
, '00' -- source_data_key3
, '00' -- source_data_key4
, '00' -- source_data_key5
, 'D' -- gp_level
--
, adj.set_of_books_id -- set_of_books_id
, 'P' -- sob_type
, USERENV('SESSIONID') -- se_gt_id
, NULL -- tax_link_id
, NULL -- tax_inc_flag
, ard.line_id -- ref_line_id
, decode(adj.type, 'TAX', nvl(ard.tax_code_id, taxx.vat_tax_id), taxx.vat_tax_id)
, decode(adj.type, 'TAX', nvl(ard.location_segment_id, taxx.location_segment_id), taxx.location_segment_id)
FROM ar_adjustments adj,
ar_distributions ard,
( select customer_trx_id, vat_tax_id, location_segment_id
from ra_customer_trx_lines
where customer_trx_id = p_trx_id
and line_type = 'LINE'
and rownum = 1
) taxx,
(SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
MAX(to_currency) invoice_currency_code
FROM ra_ar_gt
WHERE gt_id = p_gt_id
GROUP BY ref_customer_trx_id, to_currency) trx
WHERE adj.customer_trx_id= p_trx_id
AND adj.customer_trx_id= trx.ref_customer_trx_id
AND adj.customer_trx_id= taxx.customer_trx_id(+)
AND adj.status = 'A'
AND adj.postable = 'Y'
AND ard.source_table = 'ADJ'
AND ard.source_id = adj.adjustment_id
AND adj.type IN ('LINE','CHARGES','TAX','FREIGHT')
AND DECODE(
adj.type, 'LINE',DECODE(ard.source_type,
'ADJ','Y',
'TAX','Y',
'DEFERRED_TAX','Y',
'ADJ_NON_REC_TAX','Y','N'),
'CHARGES',DECODE(ard.source_type,
'FINCHRG','Y','N'),
'TAX',DECODE(ard.source_type,
'TAX','Y',
'DEFERRED_TAX','Y',
'ADJ','Y',
'ADJ_NON_REC_TAX','Y','N'),
'FREIGHT',DECODE(ard.source_type,
'ADJ','Y','N'),
'N') = 'Y';
INSERT INTO RA_AR_GT
( GT_ID ,
AMT ,
ACCTD_AMT ,
ACCOUNT_CLASS ,
CCID_SECONDARY ,
REF_CUST_TRX_LINE_GL_DIST_ID,
REF_CUSTOMER_TRX_LINE_ID ,
REF_CUSTOMER_TRX_ID ,
TO_CURRENCY ,
BASE_CURRENCY ,
-- ADJ and APP Elmt
DIST_AMT ,
DIST_ACCTD_AMT ,
DIST_CHRG_AMT ,
DIST_CHRG_ACCTD_AMT ,
DIST_FRT_AMT ,
DIST_FRT_ACCTD_AMT ,
DIST_TAX_AMT ,
DIST_TAX_ACCTD_AMT ,
-- Buc
tl_alloc_amt ,
tl_alloc_acctd_amt ,
tl_chrg_alloc_amt ,
tl_chrg_alloc_acctd_amt,
tl_frt_alloc_amt ,
tl_frt_alloc_acctd_amt,
tl_tax_alloc_amt ,
tl_tax_alloc_acctd_amt,
-- ED Elmt
DIST_ed_AMT,
DIST_ed_ACCTD_AMT,
DIST_ed_chrg_AMT,
DIST_ed_chrg_ACCTD_AMT,
DIST_ed_frt_AMT ,
DIST_ed_frt_ACCTD_AMT,
DIST_ed_tax_AMT ,
DIST_ed_tax_ACCTD_AMT,
--
tl_ed_alloc_amt ,
tl_ed_alloc_acctd_amt ,
tl_ed_chrg_alloc_amt ,
tl_ed_chrg_alloc_acctd_amt,
tl_ed_frt_alloc_amt ,
tl_ed_frt_alloc_acctd_amt,
tl_ed_tax_alloc_amt ,
tl_ed_tax_alloc_acctd_amt,
-- UNED
DIST_uned_AMT ,
DIST_uned_ACCTD_AMT ,
DIST_uned_chrg_AMT ,
DIST_uned_chrg_ACCTD_AMT ,
DIST_uned_frt_AMT ,
DIST_uned_frt_ACCTD_AMT ,
DIST_uned_tax_AMT ,
DIST_uned_tax_ACCTD_AMT ,
--
tl_uned_alloc_amt ,
tl_uned_alloc_acctd_amt ,
tl_uned_chrg_alloc_amt ,
tl_uned_chrg_alloc_acctd_amt,
tl_uned_frt_alloc_amt ,
tl_uned_frt_alloc_acctd_amt,
tl_uned_tax_alloc_amt ,
tl_uned_tax_alloc_acctd_amt,
--
source_type ,
source_table ,
source_id ,
line_type,
--
group_id,
source_data_key1 ,
source_data_key2 ,
source_data_key3 ,
source_data_key4 ,
source_data_key5 ,
gp_level,
--
set_of_books_id,
sob_type,
se_gt_id,
--{Taxable Amount
tax_link_id,
tax_inc_flag,
--}
ref_line_id,
tax_code_id,
location_segment_id
)
SELECT
p_gt_id -- GT_ID
, NVL(ard.amount_cr,0)
- NVL(ard.amount_dr,0) -- AMT
, NVL(ard.acctd_amount_cr,0)
- NVL(ard.acctd_amount_dr,0) -- ACCTD_AMT
, DECODE(adj.type,
'LINE',DECODE(ard.source_type,'ADJ','REV',
'TAX','TAX',
'DEFERRED_TAX','TAX',
'ADJ_NON_REC_TAX','TAX','REV'),
'TAX' ,DECODE(ard.source_type,'TAX','TAX',
'ADJ','TAX',
'DEFERRED_TAX','TAX',
'ADJ_NON_REC_TAX','TAX','TAX'),
'FREIGHT' ,DECODE(ard.source_type,'ADJ',
'FREIGHT','FREIGHT'),
'CHARGES',DECODE(ard.source_type,'FINCHRG',
'CHARGES','CHARGES'),
'REV') -- ACCOUNT_CLASS
, ard.code_combination_id -- CCID_SECONDARY
, DECODE(adj.type,
'LINE',DECODE(ard.source_type,'ADJ',-6,
'TAX',-8,
'DEFERRED_TAX',-8,
'ADJ_NON_REC_TAX',-8,-6),
'TAX' ,DECODE(ard.source_type,'TAX',-8,
'ADJ',-8,
'DEFERRED_TAX',-8,
'ADJ_NON_REC_TAX',-8,-8),
'FREIGHT' ,DECODE(ard.source_type,'ADJ',
-9,-9),
'CHARGES',DECODE(ard.source_type,'FINCHRG',
-7,-7),
-6) -- REF_CUST_TRX_LINE_GL_DIST_ID
, DECODE(adj.type,
'LINE',DECODE(ard.source_type,'ADJ',-6,
'TAX',-8,
'DEFERRED_TAX',-8,
'ADJ_NON_REC_TAX',-8,-6),
'TAX' ,DECODE(ard.source_type,'TAX',-8,
'ADJ',-8,
'DEFERRED_TAX',-8,
'ADJ_NON_REC_TAX',-8,-8),
'FREIGHT' ,DECODE(ard.source_type,'ADJ',
-9,-9),
'CHARGES',DECODE(ard.source_type,'FINCHRG',
-7,-7),
-6) -- REF_CUSTOMER_TRX_LINE_ID
, adj.customer_trx_id -- REF_CUSTOMER_TRX_ID
, trx.invoice_currency_code -- TO_CURRENCY
, NULL -- BASE_CURRENCY
-- ADJ and APP Elmt
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_AMT
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ACCTD_AMT
--
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ', (NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_CHRG_AMT
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_CHRG_ACCTD_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_FRT_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_FRT_ACCTD_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_TAX_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_TAX_ACCTD_AMT
-- Buc
, 0 -- tl_alloc_amt
, 0 -- tl_alloc_acctd_amt
, 0 -- tl_chrg_alloc_amt
, 0 -- tl_chrg_alloc_acctd_amt
, 0 -- tl_frt_alloc_amt
, 0 -- tl_frt_alloc_acctd_amt
, 0 -- tl_tax_alloc_amt
, 0 -- tl_tax_alloc_acctd_amt
-- ED Elmt
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_ed_AMT
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ed_ACCTD_AMT
--
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_ed_chrg_AMT
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ed_chrg_ACCTD_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_ed_frt_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ed_frt_ACCTD_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_ed_tax_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_ed_tax_ACCTD_AMT
--, 0 -- DIST_ed_AMT
--, 0 -- DIST_ed_ACCTD_AMT
--, 0 -- DIST_ed_chrg_AMT
--, 0 -- DIST_ed_chrg_ACCTD_AMT
--, 0 -- DIST_ed_frt_AMT
--, 0 -- DIST_ed_frt_ACCTD_AMT
--, 0 -- DIST_ed_tax_AMT
--, 0 -- DIST_ed_tax_ACCTD_AMT
--
, 0 -- tl_ed_alloc_amt
, 0 -- tl_ed_alloc_acctd_amt
, 0 -- tl_ed_chrg_alloc_amt
, 0 -- tl_ed_chrg_alloc_acctd_amt
, 0 -- tl_ed_frt_alloc_amt
, 0 -- tl_ed_frt_alloc_acctd_amt
, 0 -- tl_ed_tax_alloc_amt
, 0 -- tl_ed_tax_alloc_acctd_amt
-- UNED
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_uned_AMT
, DECODE(adj.type,'LINE', DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_uned_ACCTD_AMT
--
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_uned_chrg_AMT
, DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_uned_chrg_ACCTD_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_uned_frt_AMT
, DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_uned_frt_ACCTD_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
0) -- DIST_uned_tax_AMT
, DECODE(adj.type,'TAX', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
'LINE', DECODE(ard.source_type,
'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
0) -- DIST_uned_tax_ACCTD_AMT
--, 0 -- DIST_uned_AMT
--, 0 -- DIST_uned_ACCTD_AMT
--, 0 -- DIST_uned_chrg_AMT
--, 0 -- DIST_uned_chrg_ACCTD_AMT
--, 0 -- DIST_uned_frt_AMT
--, 0 -- DIST_uned_frt_ACCTD_AMT
--, 0 -- DIST_uned_tax_AMT
--, 0 -- DIST_uned_tax_ACCTD_AMT
--
, 0 -- tl_uned_alloc_amt
, 0 -- tl_uned_alloc_acctd_amt
, 0 -- tl_uned_chrg_alloc_amt
, 0 -- tl_uned_chrg_alloc_acctd_amt
, 0 -- tl_uned_frt_alloc_amt
, 0 -- tl_uned_frt_alloc_acctd_amt
, 0 -- tl_uned_tax_alloc_amt
, 0 -- tl_uned_tax_alloc_acctd_amt
--
, ard.source_type -- source_type
, ard.source_table -- source_table
, ard.source_id -- source_id
, DECODE(adj.type,
'LINE',DECODE(ard.source_type,'ADJ','LINE',
'TAX','TAX',
'DEFERRED_TAX','TAX','LINE'),
'TAX','TAX',
'CHARGES','CHARGES',
'FREIGHT','FREIGHT', 'LINE') -- line_type
--
, NULL -- group_id
, '00' -- source_data_key1
, '00' -- source_data_key2
, '00' -- source_data_key3
, '00' -- source_data_key4
, '00' -- source_data_key5
, 'D' -- gp_level
--
, adj.set_of_books_id -- set_of_books_id
, 'P' -- sob_type
, USERENV('SESSIONID') -- se_gt_id
, NULL -- tax_link_id
, NULL -- tax_inc_flag
, ard.line_id -- ref_line_id
, ard.tax_code_id
, ard.location_segment_id
FROM ar_adjustments_all adj,
ar_distributions_all ard,
ar_system_parameters_all ars,
(SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
MAX(to_currency) invoice_currency_code
FROM ra_ar_gt
GROUP BY ref_customer_trx_id, to_currency) trx
WHERE adj.customer_trx_id = trx.ref_customer_trx_id
AND adj.status = 'A'
AND adj.postable = 'Y'
AND adj.upgrade_method = '11I'
AND adj.adjustment_id = ard.source_id
AND ard.source_table = 'ADJ'
AND adj.type IN ('LINE','CHARGES','TAX','FREIGHT')
AND adj.org_id = ars.org_id
AND ars.accounting_method = 'CASH'
AND DECODE(adj.type, 'LINE',DECODE(ard.source_type,
'ADJ','Y',
'TAX','Y',
'DEFERRED_TAX','Y',
'ADJ_NON_REC_TAX','Y','N'),
'CHARGES',DECODE(ard.source_type,
'FINCHRG','Y',
'ADJ','Y','N'),
'TAX',DECODE(ard.source_type,
'TAX','Y',
'DEFERRED_TAX','Y',
'ADJ','Y',
'ADJ_NON_REC_TAX','Y','N'),
'FREIGHT',DECODE(ard.source_type,
'ADJ','Y','N'),
'N') = 'Y';
INSERT INTO RA_AR_GT
( GT_ID ,
AMT ,
ACCTD_AMT ,
ACCOUNT_CLASS ,
CCID_SECONDARY ,
REF_CUST_TRX_LINE_GL_DIST_ID,
REF_CUSTOMER_TRX_LINE_ID ,
REF_CUSTOMER_TRX_ID ,
TO_CURRENCY ,
BASE_CURRENCY ,
-- ADJ and APP Elmt
DIST_AMT ,
DIST_ACCTD_AMT ,
DIST_CHRG_AMT ,
DIST_CHRG_ACCTD_AMT ,
DIST_FRT_AMT ,
DIST_FRT_ACCTD_AMT ,
DIST_TAX_AMT ,
DIST_TAX_ACCTD_AMT ,
-- Buc
tl_alloc_amt ,
tl_alloc_acctd_amt ,
tl_chrg_alloc_amt ,
tl_chrg_alloc_acctd_amt,
tl_frt_alloc_amt ,
tl_frt_alloc_acctd_amt,
tl_tax_alloc_amt ,
tl_tax_alloc_acctd_amt,
-- ED Elmt
DIST_ed_AMT,
DIST_ed_ACCTD_AMT,
DIST_ed_chrg_AMT,
DIST_ed_chrg_ACCTD_AMT,
DIST_ed_frt_AMT ,
DIST_ed_frt_ACCTD_AMT,
DIST_ed_tax_AMT ,
DIST_ed_tax_ACCTD_AMT,
--
tl_ed_alloc_amt ,
tl_ed_alloc_acctd_amt ,
tl_ed_chrg_alloc_amt ,
tl_ed_chrg_alloc_acctd_amt,
tl_ed_frt_alloc_amt ,
tl_ed_frt_alloc_acctd_amt,
tl_ed_tax_alloc_amt ,
tl_ed_tax_alloc_acctd_amt,
-- UNED
DIST_uned_AMT ,
DIST_uned_ACCTD_AMT ,
DIST_uned_chrg_AMT ,
DIST_uned_chrg_ACCTD_AMT ,
DIST_uned_frt_AMT ,
DIST_uned_frt_ACCTD_AMT ,
DIST_uned_tax_AMT ,
DIST_uned_tax_ACCTD_AMT ,
--
tl_uned_alloc_amt ,
tl_uned_alloc_acctd_amt ,
tl_uned_chrg_alloc_amt ,
tl_uned_chrg_alloc_acctd_amt,
tl_uned_frt_alloc_amt ,
tl_uned_frt_alloc_acctd_amt,
tl_uned_tax_alloc_amt ,
tl_uned_tax_alloc_acctd_amt,
--
source_type ,
source_table ,
source_id ,
line_type,
--
group_id,
source_data_key1 ,
source_data_key2 ,
source_data_key3 ,
source_data_key4 ,
source_data_key5 ,
gp_level,
--
set_of_books_id,
sob_type,
se_gt_id,
--{Taxable Amount
tax_link_id,
tax_inc_flag,
--}
ref_line_id,
ref_mf_dist_flag
)
SELECT
p_gt_id -- GT_ID
, NVL(psad.amount,0) -- AMT
, NVL(psad.amount,0) -- ACCTD_AMT
, ctlgd.account_class -- ACCOUNT_CLASS
, psad.mf_adjustment_ccid -- CCID_SECONDARY
, ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
, ctl.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
, ctlgd.customer_trx_id -- REF_CUSTOMER_TRX_ID
, trx.invoice_currency_code -- TO_CURRENCY
, NULL -- BASE_CURRENCY
-- ADJ and APP Elmt
, DECODE(ctl.line_type,'LINE', NVL(psad.amount,0),0) -- DIST_AMT
, DECODE(ctl.line_type,'LINE', NVL(psad.amount,0),0) -- DIST_ACCTD_AMT
-- PSA 11i Charges adj are prorated over all distributions
, NVL(psad.amount,0) -- DIST_CHRG_AMT
, NVL(psad.amount,0) -- DIST_CHRG_ACCTD_AMT
, DECODE(ctl.line_type,'FREIGHT',NVL(psad.amount,0),0) -- DIST_FRT_AMT
, DECODE(ctl.line_type,'FREIGHT',NVL(psad.amount,0),0) -- DIST_FRT_ACCTD_AMT
, DECODE(ctl.line_type,'TAX' ,NVL(psad.amount,0),0) -- DIST_TAX_AMT
, DECODE(ctl.line_type,'TAX' ,NVL(psad.amount,0),0) -- DIST_TAX_ACCTD_AMT
-- Buc
, 0 -- tl_alloc_amt
, 0 -- tl_alloc_acctd_amt
, 0 -- tl_chrg_alloc_amt
, 0 -- tl_chrg_alloc_acctd_amt
, 0 -- tl_frt_alloc_amt
, 0 -- tl_frt_alloc_acctd_amt
, 0 -- tl_tax_alloc_amt
, 0 -- tl_tax_alloc_acctd_amt
-- ED Elmt
, 0 -- DIST_ed_AMT
, 0 -- DIST_ed_ACCTD_AMT
, 0 -- DIST_ed_chrg_AMT
, 0 -- DIST_ed_chrg_ACCTD_AMT
, 0 -- DIST_ed_frt_AMT
, 0 -- DIST_ed_frt_ACCTD_AMT
, 0 -- DIST_ed_tax_AMT
, 0 -- DIST_ed_tax_ACCTD_AMT
--
, 0 -- tl_ed_alloc_amt
, 0 -- tl_ed_alloc_acctd_amt
, 0 -- tl_ed_chrg_alloc_amt
, 0 -- tl_ed_chrg_alloc_acctd_amt
, 0 -- tl_ed_frt_alloc_amt
, 0 -- tl_ed_frt_alloc_acctd_amt
, 0 -- tl_ed_tax_alloc_amt
, 0 -- tl_ed_tax_alloc_acctd_amt
-- UNED
, 0 -- DIST_uned_AMT
, 0 -- DIST_uned_ACCTD_AMT
, 0 -- DIST_uned_chrg_AMT
, 0 -- DIST_uned_chrg_ACCTD_AMT
, 0 -- DIST_uned_frt_AMT
, 0 -- DIST_uned_frt_ACCTD_AMT
, 0 -- DIST_uned_tax_AMT
, 0 -- DIST_uned_tax_ACCTD_AMT
--
, 0 -- tl_uned_alloc_amt
, 0 -- tl_uned_alloc_acctd_amt
, 0 -- tl_uned_chrg_alloc_amt
, 0 -- tl_uned_chrg_alloc_acctd_amt
, 0 -- tl_uned_frt_alloc_amt
, 0 -- tl_uned_frt_alloc_acctd_amt
, 0 -- tl_uned_tax_alloc_amt
, 0 -- tl_uned_tax_alloc_acctd_amt
--
, adj.type -- source_type
, 'ADJ' -- source_table
, adj.adjustment_id -- source_id
, ctl.line_type -- line_type
--
, NULL -- group_id
, '00' -- source_data_key1
, '00' -- source_data_key2
, '00' -- source_data_key3
, '00' -- source_data_key4
, '00' -- source_data_key5
, 'D' -- gp_level
--
, adj.set_of_books_id -- set_of_books_id
, 'P' -- sob_type
, USERENV('SESSIONID') -- se_gt_id
, NULL -- tax_link_id
, NULL -- tax_inc_flag
, NULL -- ref_line_id
, 'Y' -- REF_MF_DIST_FLAG
FROM ar_adjustments_all adj,
psa_mf_adj_dist_all psad,
(SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
MAX(to_currency) invoice_currency_code
FROM ra_ar_gt
GROUP BY ref_customer_trx_id, to_currency) trx,
ra_customer_trx_lines_all ctl,
ra_cust_trx_line_gl_dist_all ctlgd
WHERE adj.customer_trx_id = trx.ref_customer_trx_id
AND adj.status = 'A'
AND adj.postable = 'Y'
AND adj.upgrade_method = '11IMFAR'
AND adj.type IN ('LINE','CHARGES','TAX','FREIGHT','INVOICE')
AND adj.adjustment_id = psad.adjustment_id
AND psad.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id;
INSERT INTO RA_AR_GT
( GT_ID ,
AMT ,
ACCTD_AMT ,
ACCOUNT_CLASS ,
CCID_SECONDARY ,
REF_CUST_TRX_LINE_GL_DIST_ID,
REF_CUSTOMER_TRX_LINE_ID ,
REF_CUSTOMER_TRX_ID ,
TO_CURRENCY ,
BASE_CURRENCY ,
-- ADJ and APP Elmt
DIST_AMT ,
DIST_ACCTD_AMT ,
DIST_CHRG_AMT ,
DIST_CHRG_ACCTD_AMT ,
DIST_FRT_AMT ,
DIST_FRT_ACCTD_AMT ,
DIST_TAX_AMT ,
DIST_TAX_ACCTD_AMT ,
-- Buc
tl_alloc_amt ,
tl_alloc_acctd_amt ,
tl_chrg_alloc_amt ,
tl_chrg_alloc_acctd_amt,
tl_frt_alloc_amt ,
tl_frt_alloc_acctd_amt,
tl_tax_alloc_amt ,
tl_tax_alloc_acctd_amt,
-- ED Elmt
DIST_ed_AMT,
DIST_ed_ACCTD_AMT,
DIST_ed_chrg_AMT,
DIST_ed_chrg_ACCTD_AMT,
DIST_ed_frt_AMT ,
DIST_ed_frt_ACCTD_AMT,
DIST_ed_tax_AMT ,
DIST_ed_tax_ACCTD_AMT,
--
tl_ed_alloc_amt ,
tl_ed_alloc_acctd_amt ,
tl_ed_chrg_alloc_amt ,
tl_ed_chrg_alloc_acctd_amt,
tl_ed_frt_alloc_amt ,
tl_ed_frt_alloc_acctd_amt,
tl_ed_tax_alloc_amt ,
tl_ed_tax_alloc_acctd_amt,
-- UNED
DIST_uned_AMT ,
DIST_uned_ACCTD_AMT ,
DIST_uned_chrg_AMT ,
DIST_uned_chrg_ACCTD_AMT ,
DIST_uned_frt_AMT ,
DIST_uned_frt_ACCTD_AMT ,
DIST_uned_tax_AMT ,
DIST_uned_tax_ACCTD_AMT ,
--
tl_uned_alloc_amt ,
tl_uned_alloc_acctd_amt ,
tl_uned_chrg_alloc_amt ,
tl_uned_chrg_alloc_acctd_amt,
tl_uned_frt_alloc_amt ,
tl_uned_frt_alloc_acctd_amt,
tl_uned_tax_alloc_amt ,
tl_uned_tax_alloc_acctd_amt,
--
source_type ,
source_table ,
source_id ,
line_type,
--
group_id,
source_data_key1 ,
source_data_key2 ,
source_data_key3 ,
source_data_key4 ,
source_data_key5 ,
gp_level,
--
set_of_books_id,
sob_type,
se_gt_id,
--{Taxable Amount
tax_link_id,
tax_inc_flag,
--}
ref_line_id
)
SELECT
p_gt_id -- GT_ID
, NVL(adj.amount,0) -- AMT
, NVL(adj.acctd_amount,0) -- ACCTD_AMT
, 'INVOICE' -- ACCOUNT_CLASS
, adj.code_combination_id -- CCID_SECONDARY
, -10 -- REF_CUST_TRX_LINE_GL_DIST_ID
, -10 -- REF_CUSTOMER_TRX_LINE_ID
, adj.customer_trx_id -- REF_CUSTOMER_TRX_ID
, trx.invoice_currency_code -- TO_CURRENCY
, NULL -- BASE_CURRENCY
-- ADJ and APP Elmt
, NVL(adj.line_adjusted,0) -- DIST_AMT
, fct_acct_amt(NVL(adj.line_adjusted,0),
NVL(adj.amount,0),
NVL(adj.acctd_amount,0),
trx.invoice_currency_code,
sob.currency_code,
adj.adjustment_id) -- DIST_ACCTD_AMT
--
, NVL(adj.receivables_charges_adjusted,0) -- DIST_CHRG_AMT
, fct_acct_amt(NVL(adj.receivables_charges_adjusted,0),
NVL(adj.amount,0),
NVL(adj.acctd_amount,0),
trx.invoice_currency_code,
sob.currency_code,
adj.adjustment_id) -- DIST_CHRG_ACCTD_AMT
, NVL(adj.freight_adjusted,0) -- DIST_FRT_AMT
, fct_acct_amt(NVL(adj.freight_adjusted,0),
NVL(adj.amount,0),
NVL(adj.acctd_amount,0),
trx.invoice_currency_code,
sob.currency_code,
adj.adjustment_id) -- DIST_FRT_ACCTD_AMT
, NVL(adj.tax_adjusted,0) -- DIST_TAX_AMT
, fct_acct_amt(NVL(adj.tax_adjusted,0),
NVL(adj.amount,0),
NVL(adj.acctd_amount,0),
trx.invoice_currency_code,
sob.currency_code,
adj.adjustment_id) -- DIST_TAX_ACCTD_AMT
-- Buc
, 0 -- tl_alloc_amt
, 0 -- tl_alloc_acctd_amt
, 0 -- tl_chrg_alloc_amt
, 0 -- tl_chrg_alloc_acctd_amt
, 0 -- tl_frt_alloc_amt
, 0 -- tl_frt_alloc_acctd_amt
, 0 -- tl_tax_alloc_amt
, 0 -- tl_tax_alloc_acctd_amt
-- ED Elmt
, 0 -- DIST_ed_AMT
, 0 -- DIST_ed_ACCTD_AMT
, 0 -- DIST_ed_chrg_AMT
, 0 -- DIST_ed_chrg_ACCTD_AMT
, 0 -- DIST_ed_frt_AMT
, 0 -- DIST_ed_frt_ACCTD_AMT
, 0 -- DIST_ed_tax_AMT
, 0 -- DIST_ed_tax_ACCTD_AMT
--
, 0 -- tl_ed_alloc_amt
, 0 -- tl_ed_alloc_acctd_amt
, 0 -- tl_ed_chrg_alloc_amt
, 0 -- tl_ed_chrg_alloc_acctd_amt
, 0 -- tl_ed_frt_alloc_amt
, 0 -- tl_ed_frt_alloc_acctd_amt
, 0 -- tl_ed_tax_alloc_amt
, 0 -- tl_ed_tax_alloc_acctd_amt
-- UNED
, 0 -- DIST_uned_AMT
, 0 -- DIST_uned_ACCTD_AMT
, 0 -- DIST_uned_chrg_AMT
, 0 -- DIST_uned_chrg_ACCTD_AMT
, 0 -- DIST_uned_frt_AMT
, 0 -- DIST_uned_frt_ACCTD_AMT
, 0 -- DIST_uned_tax_AMT
, 0 -- DIST_uned_tax_ACCTD_AMT
--
, 0 -- tl_uned_alloc_amt
, 0 -- tl_uned_alloc_acctd_amt
, 0 -- tl_uned_chrg_alloc_amt
, 0 -- tl_uned_chrg_alloc_acctd_amt
, 0 -- tl_uned_frt_alloc_amt
, 0 -- tl_uned_frt_alloc_acctd_amt
, 0 -- tl_uned_tax_alloc_amt
, 0 -- tl_uned_tax_alloc_acctd_amt
--
, 'INVOICE' -- source_type
, 'ADJ' -- source_table
, adj.adjustment_id -- source_id
, 'INVOICE' -- line_type
--
, NULL -- group_id
, '00' -- source_data_key1
, '00' -- source_data_key2
, '00' -- source_data_key3
, '00' -- source_data_key4
, '00' -- source_data_key5
, 'D' -- gp_level
--
, adj.set_of_books_id -- set_of_books_id
, 'P' -- sob_type
, USERENV('SESSIONID') -- se_gt_id
, NULL -- tax_link_id
, NULL -- tax_inc_flag
, NULL -- ref_line_id
FROM ar_adjustments_all adj,
ar_system_parameters_all ars,
-- For performance this sql is not nec as for legacy data
-- we are proposing no tied by to original line
-- in new transaction the ref_line_id will be present
-- (SELECT MAX(line_id) line_id,
-- source_id source_id
-- FROM ar_distributions_all
-- WHERE source_table = 'ADJ'
-- GROUP BY source_id) ard,
(SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
MAX(to_currency) invoice_currency_code
FROM ra_ar_gt
WHERE source_table = 'CTLGD'
GROUP BY ref_customer_trx_id,
to_currency) trx,
gl_sets_of_books sob
WHERE adj.customer_trx_id = trx.ref_customer_trx_id
AND adj.type = 'INVOICE'
AND adj.status = 'A'
AND adj.postable = 'Y'
AND adj.set_of_books_id = sob.set_of_books_id
AND adj.org_id = ars.org_id
AND ars.accounting_method = 'CASH';
INSERT INTO RA_AR_GT
( GT_ID ,
AMT ,
ACCTD_AMT ,
ACCOUNT_CLASS ,
CCID_SECONDARY ,
REF_CUST_TRX_LINE_GL_DIST_ID,
REF_CUSTOMER_TRX_LINE_ID ,
REF_CUSTOMER_TRX_ID ,
TO_CURRENCY ,
BASE_CURRENCY ,
-- ADJ and APP Elmt
DIST_AMT ,
DIST_ACCTD_AMT ,
DIST_CHRG_AMT ,
DIST_CHRG_ACCTD_AMT ,
DIST_FRT_AMT ,
DIST_FRT_ACCTD_AMT ,
DIST_TAX_AMT ,
DIST_TAX_ACCTD_AMT ,
-- Buc
tl_alloc_amt ,
tl_alloc_acctd_amt ,
tl_chrg_alloc_amt ,
tl_chrg_alloc_acctd_amt,
tl_frt_alloc_amt ,
tl_frt_alloc_acctd_amt,
tl_tax_alloc_amt ,
tl_tax_alloc_acctd_amt,
-- ED Elmt
DIST_ed_AMT,
DIST_ed_ACCTD_AMT,
DIST_ed_chrg_AMT,
DIST_ed_chrg_ACCTD_AMT,
DIST_ed_frt_AMT ,
DIST_ed_frt_ACCTD_AMT,
DIST_ed_tax_AMT ,
DIST_ed_tax_ACCTD_AMT,
--
tl_ed_alloc_amt ,
tl_ed_alloc_acctd_amt ,
tl_ed_chrg_alloc_amt ,
tl_ed_chrg_alloc_acctd_amt,
tl_ed_frt_alloc_amt ,
tl_ed_frt_alloc_acctd_amt,
tl_ed_tax_alloc_amt ,
tl_ed_tax_alloc_acctd_amt,
-- UNED
DIST_uned_AMT ,
DIST_uned_ACCTD_AMT ,
DIST_uned_chrg_AMT ,
DIST_uned_chrg_ACCTD_AMT ,
DIST_uned_frt_AMT ,
DIST_uned_frt_ACCTD_AMT ,
DIST_uned_tax_AMT ,
DIST_uned_tax_ACCTD_AMT ,
--
tl_uned_alloc_amt ,
tl_uned_alloc_acctd_amt ,
tl_uned_chrg_alloc_amt ,
tl_uned_chrg_alloc_acctd_amt,
tl_uned_frt_alloc_amt ,
tl_uned_frt_alloc_acctd_amt,
tl_uned_tax_alloc_amt ,
tl_uned_tax_alloc_acctd_amt,
--
source_type ,
source_table ,
source_id ,
line_type,
--
group_id,
source_data_key1 ,
source_data_key2 ,
source_data_key3 ,
source_data_key4 ,
source_data_key5 ,
gp_level,
--
set_of_books_id,
sob_type,
se_gt_id,
--{Taxable Amount
tax_link_id,
tax_inc_flag,
--}
ref_line_id
)
SELECT
p_gt_id -- GT_ID
, NVL(adj.amount,0) -- AMT
, NVL(adj.acctd_amount,0) -- ACCTD_AMT
, 'INVOICE' -- ACCOUNT_CLASS
, adj.code_combination_id -- CCID_SECONDARY
, -10 -- REF_CUST_TRX_LINE_GL_DIST_ID
, -10 -- REF_CUSTOMER_TRX_LINE_ID
, adj.customer_trx_id -- REF_CUSTOMER_TRX_ID
, trx.invoice_currency_code -- TO_CURRENCY
, NULL -- BASE_CURRENCY
-- ADJ and APP Elmt
, NVL(adj.line_adjusted,0) -- DIST_AMT
, fct_acct_amt(NVL(adj.line_adjusted,0),
NVL(adj.amount,0),
NVL(adj.acctd_amount,0),
trx.invoice_currency_code,
sob.currency_code,
adj.adjustment_id) -- DIST_ACCTD_AMT
--
, NVL(adj.receivables_charges_adjusted,0) -- DIST_CHRG_AMT
, fct_acct_amt(NVL(adj.receivables_charges_adjusted,0),
NVL(adj.amount,0),
NVL(adj.acctd_amount,0),
trx.invoice_currency_code,
sob.currency_code,
adj.adjustment_id) -- DIST_CHRG_ACCTD_AMT
, NVL(adj.freight_adjusted,0) -- DIST_FRT_AMT
, fct_acct_amt(NVL(adj.freight_adjusted,0),
NVL(adj.amount,0),
NVL(adj.acctd_amount,0),
trx.invoice_currency_code,
sob.currency_code,
adj.adjustment_id) -- DIST_FRT_ACCTD_AMT
, NVL(adj.tax_adjusted,0) -- DIST_TAX_AMT
, fct_acct_amt(NVL(adj.tax_adjusted,0),
NVL(adj.amount,0),
NVL(adj.acctd_amount,0),
trx.invoice_currency_code,
sob.currency_code,
adj.adjustment_id) -- DIST_TAX_ACCTD_AMT
-- Buc
, 0 -- tl_alloc_amt
, 0 -- tl_alloc_acctd_amt
, 0 -- tl_chrg_alloc_amt
, 0 -- tl_chrg_alloc_acctd_amt
, 0 -- tl_frt_alloc_amt
, 0 -- tl_frt_alloc_acctd_amt
, 0 -- tl_tax_alloc_amt
, 0 -- tl_tax_alloc_acctd_amt
-- ED Elmt
, 0 -- DIST_ed_AMT
, 0 -- DIST_ed_ACCTD_AMT
, 0 -- DIST_ed_chrg_AMT
, 0 -- DIST_ed_chrg_ACCTD_AMT
, 0 -- DIST_ed_frt_AMT
, 0 -- DIST_ed_frt_ACCTD_AMT
, 0 -- DIST_ed_tax_AMT
, 0 -- DIST_ed_tax_ACCTD_AMT
--
, 0 -- tl_ed_alloc_amt
, 0 -- tl_ed_alloc_acctd_amt
, 0 -- tl_ed_chrg_alloc_amt
, 0 -- tl_ed_chrg_alloc_acctd_amt
, 0 -- tl_ed_frt_alloc_amt
, 0 -- tl_ed_frt_alloc_acctd_amt
, 0 -- tl_ed_tax_alloc_amt
, 0 -- tl_ed_tax_alloc_acctd_amt
-- UNED
, 0 -- DIST_uned_AMT
, 0 -- DIST_uned_ACCTD_AMT
, 0 -- DIST_uned_chrg_AMT
, 0 -- DIST_uned_chrg_ACCTD_AMT
, 0 -- DIST_uned_frt_AMT
, 0 -- DIST_uned_frt_ACCTD_AMT
, 0 -- DIST_uned_tax_AMT
, 0 -- DIST_uned_tax_ACCTD_AMT
--
, 0 -- tl_uned_alloc_amt
, 0 -- tl_uned_alloc_acctd_amt
, 0 -- tl_uned_chrg_alloc_amt
, 0 -- tl_uned_chrg_alloc_acctd_amt
, 0 -- tl_uned_frt_alloc_amt
, 0 -- tl_uned_frt_alloc_acctd_amt
, 0 -- tl_uned_tax_alloc_amt
, 0 -- tl_uned_tax_alloc_acctd_amt
--
, 'INVOICE' -- source_type
, 'ADJ' -- source_table
, adj.adjustment_id -- source_id
, 'INVOICE' -- line_type
--
, NULL -- group_id
, '00' -- source_data_key1
, '00' -- source_data_key2
, '00' -- source_data_key3
, '00' -- source_data_key4
, '00' -- source_data_key5
, 'D' -- gp_level
--
, adj.set_of_books_id -- set_of_books_id
, 'P' -- sob_type
, USERENV('SESSIONID') -- se_gt_id
, NULL -- tax_link_id
, NULL -- tax_inc_flag
, NULL -- ref_line_id
FROM ar_adjustments adj,
ar_system_parameters_all ars,
(SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
MAX(to_currency) invoice_currency_code
FROM ra_ar_gt
WHERE gt_id = p_gt_id
GROUP BY ref_customer_trx_id, to_currency) trx,
gl_sets_of_books sob
WHERE adj.customer_trx_id = p_trx_id
AND adj.customer_trx_id = trx.ref_customer_trx_id
AND adj.type = 'INVOICE'
AND adj.status = 'A'
AND adj.postable = 'Y'
AND adj.upgrade_method = '11I'
AND adj.set_of_books_id = sob.set_of_books_id
AND adj.org_id = ars.org_id
AND ars.accounting_method = 'CASH';
PROCEDURE update_base
(p_gt_id IN NUMBER DEFAULT NULL)
IS
BEGIN
log('update_base +');
INSERT INTO ar_base_dist_amts_gt
( gt_id,
gp_level,
ref_customer_trx_id ,
ref_customer_trx_line_id,
base_dist_amt ,
base_dist_acctd_amt ,
base_dist_chrg_amt ,
base_dist_chrg_acctd_amt ,
base_dist_frt_amt ,
base_dist_frt_acctd_amt ,
base_dist_tax_amt ,
base_dist_tax_acctd_amt ,
base_ed_dist_amt ,
base_ed_dist_acctd_amt ,
base_ed_dist_chrg_amt ,
base_ed_dist_chrg_acctd_amt,
base_ed_dist_frt_amt ,
base_ed_dist_frt_acctd_amt ,
base_ed_dist_tax_amt ,
base_ed_dist_tax_acctd_amt ,
base_uned_dist_amt,
base_uned_dist_acctd_amt,
base_uned_dist_chrg_amt,
base_uned_dist_chrg_acctd_amt,
base_uned_dist_frt_amt,
base_uned_dist_frt_acctd_amt,
base_uned_dist_tax_amt,
base_uned_dist_tax_acctd_amt,
set_of_books_id,
sob_type,
source_table,
source_type
)
SELECT DISTINCT
a.gt_id,
a.gp_level,
a.ref_customer_trx_id ,
a.ref_customer_trx_line_id,
s.sum_dist_amt,
s.sum_dist_acctd_amt,
s.sum_dist_chrg_amt,
s.sum_dist_chrg_acctd_amt,
s.sum_dist_frt_amt,
s.sum_dist_frt_acctd_amt,
s.sum_dist_tax_amt,
s.sum_dist_tax_acctd_amt,
--
-- DECODE(a.source_table,'CTLGD',s.sum_dist_ed_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_ed_acctd_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_ed_chrg_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_ed_chrg_acctd_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_ed_frt_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_ed_frt_acctd_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_ed_tax_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_ed_tax_acctd_amt,0),
-- --
-- DECODE(a.source_table,'CTLGD',s.sum_dist_uned_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_uned_acctd_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_uned_chrg_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_uned_chrg_acctd_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_uned_frt_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_uned_frt_acctd_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_uned_tax_amt,0),
-- DECODE(a.source_table,'CTLGD',s.sum_dist_uned_tax_acctd_amt,0),
s.sum_dist_ed_amt,
s.sum_dist_ed_acctd_amt,
s.sum_dist_ed_chrg_amt,
s.sum_dist_ed_chrg_acctd_amt,
s.sum_dist_ed_frt_amt,
s.sum_dist_ed_frt_acctd_amt,
s.sum_dist_ed_tax_amt,
s.sum_dist_ed_tax_acctd_amt,
--
s.sum_dist_uned_amt,
s.sum_dist_uned_acctd_amt,
s.sum_dist_uned_chrg_amt,
s.sum_dist_uned_chrg_acctd_amt,
s.sum_dist_uned_frt_amt,
s.sum_dist_uned_frt_acctd_amt,
s.sum_dist_uned_tax_amt,
s.sum_dist_uned_tax_acctd_amt,
a.set_of_books_id,
a.sob_type,
a.source_table,
a.source_type
FROM (SELECT
SUM(NVL(b.DIST_AMT,0)) sum_dist_amt ,
SUM(NVL(b.DIST_ACCTD_AMT,0)) sum_dist_acctd_amt,
SUM(NVL(b.DIST_CHRG_AMT,0)) sum_dist_chrg_amt,
SUM(NVL(b.DIST_CHRG_ACCTD_AMT,0)) sum_dist_chrg_acctd_amt,
SUM(NVL(b.DIST_FRT_AMT,0)) sum_dist_frt_amt,
SUM(NVL(b.DIST_FRT_ACCTD_AMT,0)) sum_dist_frt_acctd_amt,
SUM(NVL(b.DIST_TAX_AMT,0)) sum_dist_tax_amt,
SUM(NVL(b.DIST_TAX_ACCTD_AMT,0)) sum_dist_tax_acctd_amt,
--
SUM(NVL(b.DIST_ed_AMT,0)) sum_dist_ed_amt,
SUM(NVL(b.DIST_ed_ACCTD_AMT,0)) sum_dist_ed_acctd_amt,
SUM(NVL(b.DIST_ed_chrg_AMT,0)) sum_dist_ed_chrg_amt,
SUM(NVL(b.DIST_ed_chrg_ACCTD_AMT,0)) sum_dist_ed_chrg_acctd_amt,
SUM(NVL(b.DIST_ed_frt_AMT,0)) sum_dist_ed_frt_amt,
SUM(NVL(b.DIST_ed_frt_ACCTD_AMT,0)) sum_dist_ed_frt_acctd_amt,
SUM(NVL(b.DIST_ed_tax_AMT,0)) sum_dist_ed_tax_amt,
SUM(NVL(b.DIST_ed_tax_ACCTD_AMT,0)) sum_dist_ed_tax_acctd_amt,
--
SUM(NVL(b.DIST_uned_AMT,0)) sum_dist_uned_amt,
SUM(NVL(b.DIST_uned_ACCTD_AMT,0)) sum_dist_uned_acctd_amt,
SUM(NVL(b.DIST_uned_chrg_AMT,0)) sum_dist_uned_chrg_amt,
SUM(NVL(b.DIST_uned_chrg_ACCTD_AMT,0)) sum_dist_uned_chrg_acctd_amt,
SUM(NVL(b.DIST_uned_frt_AMT,0)) sum_dist_uned_frt_amt,
SUM(NVL(b.DIST_uned_frt_ACCTD_AMT,0)) sum_dist_uned_frt_acctd_amt,
SUM(NVL(b.DIST_uned_tax_AMT,0)) sum_dist_uned_tax_amt,
SUM(NVL(b.DIST_uned_tax_ACCTD_AMT,0)) sum_dist_uned_tax_acctd_amt,
b.ref_customer_trx_id ref_customer_trx_id,
b.gt_id gt_id
FROM ra_ar_gt b
GROUP BY b.ref_customer_trx_id,
b.gt_id ) s,
ra_ar_gt a
WHERE a.ref_customer_trx_id = s.ref_customer_trx_id
AND a.gt_id = s.gt_id
AND a.gt_id = NVL(p_gt_id,a.gt_id);
log('update_base -');
log('EXCEPTION OTHERS: update_base :'||SQLERRM);
END update_base;
SELECT app.receivable_application_id
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1'
FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11IMFAR'
AND adj.status = 'A'
AND adj.postable = 'Y')
MINUS -- This is to avoid corrupted data. In the case the same invoice has MF and none MF adjustment
-- theorically impossible
SELECT app.receivable_application_id
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1'
FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11I'
AND adj.status = 'A'
AND adj.postable = 'Y');
SELECT app.receivable_application_id
FROM xla_events_gt evt,
ar_receivable_applications_all app,
ar_system_parameters_all ars
WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND app.org_id = ars.org_id
AND ars.accounting_method = 'CASH'
AND EXISTS (SELECT '1'
FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11I'
AND adj.status = 'A'
AND adj.postable = 'Y');
SELECT app.*
FROM xla_events_gt evt,
ar_receivable_applications_all app,
ar_system_parameters_all ars
WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE',
'CM_CREATE' ,'CM_UPDATE' )
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND app.org_id = ars.org_id
AND ars.accounting_method = 'CASH'
AND NOT EXISTS (SELECT '1'
FROM psa_trx_types_all psa,
ra_customer_trx_all inv
WHERE inv.customer_trx_id = app.applied_customer_trx_id
AND inv.cust_trx_type_id = psa.psa_trx_type_id);
SELECT app.*
FROM xla_events_gt evt,
ar_receivable_applications_all app,
ar_system_parameters_all ars
WHERE evt.event_type_code IN ('CM_CREATE','CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND app.org_id = ars.org_id
AND ars.accounting_method = 'CASH'
AND NOT EXISTS (SELECT '1'
FROM psa_trx_types_all psa,
ra_customer_trx_all inv
WHERE inv.customer_trx_id = app.customer_trx_id
AND inv.cust_trx_type_id = psa.psa_trx_type_id);
DELETE FROM ra_ar_gt;
update_base;
SELECT app.*
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE',
'CM_CREATE' ,'CM_UPDATE' )
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11IMFAR'
AND adj.status = 'A'
AND adj.postable = 'Y')
MINUS
SELECT app.*
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE',
'CM_CREATE' ,'CM_UPDATE' )
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11I'
AND adj.status = 'A'
AND adj.postable = 'Y');
SELECT app.*
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ('CM_CREATE','CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.customer_trx_id
AND adj.upgrade_method = '11IMFAR'
AND adj.status = 'A'
AND adj.postable = 'Y')
MINUS
SELECT app.*
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ('CM_CREATE','CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.customer_trx_id
AND adj.upgrade_method = '11I'
AND adj.status = 'A'
AND adj.postable = 'Y');
DELETE FROM ra_ar_gt;
update_base;
UPDATE ar_receivable_applications_all ra
SET ra.upgrade_method = 'R12_11IMFAR_POST'
WHERE ra.receivable_application_id IN (
SELECT app.receivable_application_id
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1'
FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11IMFAR'
AND adj.status = 'A'
AND adj.postable = 'Y')
MINUS
SELECT app.receivable_application_id
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
AND EXISTS (SELECT '1'
FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11I'
AND adj.status = 'A'
AND adj.postable = 'Y')
);
UPDATE ar_receivable_applications_all ra
SET ra.upgrade_method = 'R12_11ICASH_POST'
WHERE ra.receivable_application_id IN (
SELECT app.receivable_application_id
FROM xla_events_gt evt,
ar_receivable_applications_all app
WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
'CM_CREATE' ,'CM_UPDATE')
AND evt.event_id = app.event_id
AND app.status = 'APP'
AND app.upgrade_method IS NULL
-- AND app.cash_receipt_id = cr.cash_receipt_id(+)
AND EXISTS (SELECT '1'
FROM ar_adjustments_all adj
WHERE adj.customer_trx_id = app.applied_customer_trx_id
AND adj.upgrade_method = '11I'
AND adj.status = 'A'
AND adj.postable = 'Y'));