[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
FROM psa_mf_adj_dist_all
WHERE rowid >= p_start_rowid
AND rowid <= p_end_rowid;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
l_worker_id,
l_num_workers,
l_batch_size, 0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
l_batch_size,
TRUE);
INSERT INTO ar_distributions_all (
LINE_ID
, SOURCE_ID
, SOURCE_TABLE
, SOURCE_TYPE
, CODE_COMBINATION_ID
, AMOUNT_DR
, AMOUNT_CR
, ACCTD_AMOUNT_DR
, ACCTD_AMOUNT_CR
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORG_ID
, SOURCE_TABLE_SECONDARY
, SOURCE_ID_SECONDARY
, CURRENCY_CODE
, CURRENCY_CONVERSION_RATE
, CURRENCY_CONVERSION_TYPE
, CURRENCY_CONVERSION_DATE
, TAXABLE_ENTERED_DR
, TAXABLE_ENTERED_CR
, TAXABLE_ACCOUNTED_DR
, TAXABLE_ACCOUNTED_CR
, TAX_LINK_ID
, THIRD_PARTY_ID
, THIRD_PARTY_SUB_ID
, REVERSED_SOURCE_ID
, TAX_CODE_ID
, LOCATION_SEGMENT_ID
, SOURCE_TYPE_SECONDARY
, TAX_GROUP_CODE_ID
, REF_CUSTOMER_TRX_LINE_ID
, REF_CUST_TRX_LINE_GL_DIST_ID
, REF_ACCOUNT_CLASS
, ACTIVITY_BUCKET
, REF_LINE_ID
, FROM_AMOUNT_DR
, FROM_AMOUNT_CR
, FROM_ACCTD_AMOUNT_DR
, FROM_ACCTD_AMOUNT_CR
, REF_MF_DIST_FLAG
, REF_DIST_CCID)
SELECT
ar_distributions_s.nextval -- LINE_ID
, ard.SOURCE_ID
, ard.SOURCE_TABLE
, ard.SOURCE_TYPE
, ard.CODE_COMBINATION_ID
, ard.AMOUNT_CR -- Switch DR to CR
, ard.AMOUNT_DR -- Switch CR to DR
, ard.ACCTD_AMOUNT_CR -- Switch DR to CR
, ard.ACCTD_AMOUNT_DR -- Switch CR to DR
, SYSDATE -- CREATION_DATE
, 0 -- CREATED_BY
, 0 -- LAST_UPDATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, 0 -- LAST_UPDATE_LOGIN
, ard.ORG_ID
, 'UPMFAJREAR' -- SOURCE_TABLE_SECONDARY
, ard.SOURCE_ID_SECONDARY
, ard.CURRENCY_CODE
, ard.CURRENCY_CONVERSION_RATE
, ard.CURRENCY_CONVERSION_TYPE
, ard.CURRENCY_CONVERSION_DATE
, ard.TAXABLE_ENTERED_DR
, ard.TAXABLE_ENTERED_CR
, ard.TAXABLE_ACCOUNTED_DR
, ard.TAXABLE_ACCOUNTED_CR
, ard.TAX_LINK_ID
, ard.THIRD_PARTY_ID
, ard.THIRD_PARTY_SUB_ID
, ard.REVERSED_SOURCE_ID
, ard.TAX_CODE_ID
, ard.LOCATION_SEGMENT_ID
, 'PSA_MF_ADJ_DIST_ALL' -- SOURCE_TYPE_SECONDARY
, ard.TAX_GROUP_CODE_ID
, ard.REF_CUSTOMER_TRX_LINE_ID
, ard.REF_CUST_TRX_LINE_GL_DIST_ID
, ard.REF_ACCOUNT_CLASS
, ard.ACTIVITY_BUCKET
, ard.REF_LINE_ID
, ard.FROM_AMOUNT_DR
, ard.FROM_AMOUNT_CR
, ard.FROM_ACCTD_AMOUNT_DR
, ard.FROM_ACCTD_AMOUNT_CR
, ard.REF_MF_DIST_FLAG
, ard.REF_DIST_CCID
FROM ar_adjustments_all adj,
ar_distributions_all ard
WHERE adj.rowid >= l_start_rowid
AND adj.rowid <= l_end_rowid
AND ard.source_table = 'ADJ'
AND ard.source_id = adj.adjustment_id
AND EXISTS (SELECT NULL FROM psa_mf_adj_dist_all a
WHERE a.adjustment_id = adj.adjustment_id);
INSERT INTO ar_distributions_all (
LINE_ID
, SOURCE_ID
, SOURCE_TABLE
, SOURCE_TYPE
, CODE_COMBINATION_ID
, AMOUNT_DR
, AMOUNT_CR
, ACCTD_AMOUNT_DR
, ACCTD_AMOUNT_CR
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORG_ID
, SOURCE_TABLE_SECONDARY
, SOURCE_ID_SECONDARY
, CURRENCY_CODE
, CURRENCY_CONVERSION_RATE
, CURRENCY_CONVERSION_TYPE
, CURRENCY_CONVERSION_DATE
, TAXABLE_ENTERED_DR
, TAXABLE_ENTERED_CR
, TAXABLE_ACCOUNTED_DR
, TAXABLE_ACCOUNTED_CR
, TAX_LINK_ID
, THIRD_PARTY_ID
, THIRD_PARTY_SUB_ID
, REVERSED_SOURCE_ID
, TAX_CODE_ID
, LOCATION_SEGMENT_ID
, SOURCE_TYPE_SECONDARY
, TAX_GROUP_CODE_ID
, REF_CUSTOMER_TRX_LINE_ID
, REF_CUST_TRX_LINE_GL_DIST_ID
, REF_ACCOUNT_CLASS
, ACTIVITY_BUCKET
, REF_LINE_ID
, FROM_AMOUNT_DR
, FROM_AMOUNT_CR
, FROM_ACCTD_AMOUNT_DR
, FROM_ACCTD_AMOUNT_CR
, REF_MF_DIST_FLAG
, REF_DIST_CCID)
SELECT /*+ ordered rowid(adj) use_nl(psaadj,psatd,ctlgd,trx) INDEX(psaadj psa_mf_adj_dist_u1) INDEX(psatd psa_mf_trx_dist_u1)
INDEX(ctlgd ra_cust_trx_line_gl_dist_u1) */
ar_distributions_s.nextval -- LINE_ID
, psaadj.adjustment_id -- SOURCE_ID
, 'ADJ' -- SOURCE_TABLE
, CASE WHEN doub.side = 'D' THEN
DECODE(SIGN(psaadj.amount),
-1 , 'ADJ',
'REC' )
ELSE
DECODE(SIGN(psaadj.amount),
1, 'ADJ',
'REC' )
END -- SOURCE_TYPE
, CASE WHEN doub.side = 'D' THEN
DECODE(SIGN(psaadj.amount),
-1 , psaadj.mf_adjustment_ccid,
psatd.mf_receivables_ccid )
ELSE
DECODE(SIGN(psaadj.amount),
1, psaadj.mf_adjustment_ccid,
psatd.mf_receivables_ccid )
END -- CODE_COMBINATION_ID
, DECODE(doub.side,'D',ABS(psaadj.amount),NULL ) -- AMOUNT_DR
, DECODE(doub.side,'C',ABS(psaadj.amount),NULL ) -- AMOUNT_CR
, DECODE(doub.side,'D',ABS(psaadj.amount),NULL ) -- ACCTD_AMOUNT_DR
, DECODE(doub.side,'C',ABS(psaadj.amount),NULL ) -- ACCTD_AMOUNT_CR
, SYSDATE -- CREATION_DATE
, 0 -- CREATED_BY
, 0 -- LAST_UPDATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, 0 -- LAST_UPDATE_LOGIN
, trx.org_id -- ORG_ID
, 'UPMFAJMIAR' -- SOURCE_TABLE_SECONDARY
, NULL -- SOURCE_ID_SECONDARY
, trx.invoice_currency_code -- CURRENCY_CODE
, NULL -- CURRENCY_CONVERSION_RATE
, NULL -- CURRENCY_CONVERSION_TYPE
, NULL -- CURRENCY_CONVERSION_DATE
, NULL -- TAXABLE_ENTERED_DR
, NULL -- TAXABLE_ENTERED_CR
, NULL -- TAXABLE_ACCOUNTED_DR
, NULL -- TAXABLE_ACCOUNTED_CR
, NULL -- TAX_LINK_ID
, NULL -- THIRD_PARTY_ID
, NULL -- THIRD_PARTY_SUB_ID
, NULL -- REVERSED_SOURCE_ID
, NULL -- TAX_CODE_ID
, NULL -- LOCATION_SEGMENT_ID
, 'PSA_MF_ADJ_DIST_ALL' -- SOURCE_TYPE_SECONDARY
, NULL -- TAX_GROUP_CODE_ID
, ctlgd.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
, psatd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
, ctlgd.account_class -- REF_ACCOUNT_CLASS
, 'ADJ_'||
-- DECODE(doub.type,'CHARGES','CHRG',
DECODE(ctlgd.account_class,
'REV','LINE',
'TAX','TAX',
'FREIGHT','FRT',
'LINE') -- ACTIVITY_BUCKET
--)
, NULL -- REF_LINE_ID
, NULL -- FROM_AMOUNT_DR
, NULL -- FROM_AMOUNT_CR
, NULL -- FROM_ACCTD_AMOUNT_DR
, NULL -- FROM_ACCTD_AMOUNT_CR
, NULL -- REF_MF_DIST_FLAG
, NULL -- REF_DIST_CCID
FROM
ar_adjustments_all adj,
psa_mf_adj_dist_all psaadj,
(SELECT a.flag side,
b.adjustment_id adj_id,
b.customer_trx_id customer_trx_id,
b.type type
FROM
(SELECT 'D' AS flag FROM DUAL
UNION ALL
SELECT 'C' AS flag FROM DUAL) a,
ar_adjustments_all b) doub,
psa_mf_trx_dist_all psatd,
ra_customer_trx_all trx,
ra_cust_trx_line_gl_dist_all ctlgd
WHERE adj.rowid >= l_start_rowid
AND adj.rowid <= l_end_rowid
AND adj.adjustment_id = psaadj.adjustment_id
AND doub.adj_id = psaadj.adjustment_id
AND psaadj.cust_trx_line_gl_dist_id = psatd.cust_trx_line_gl_dist_id
AND psatd.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
AND doub.customer_trx_id = trx.customer_trx_id;
ad_parallel_updates_pkg.processed_rowid_range(
l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
l_batch_size,
FALSE);
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
l_worker_id,
l_num_workers,
l_batch_size, 0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
l_batch_size,
TRUE);
INSERT INTO ar_distributions_all
( LINE_ID
, SOURCE_ID
, SOURCE_TABLE
, SOURCE_TYPE
, CODE_COMBINATION_ID
, AMOUNT_DR
, AMOUNT_CR
, ACCTD_AMOUNT_DR
, ACCTD_AMOUNT_CR
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORG_ID
, SOURCE_TABLE_SECONDARY
, SOURCE_ID_SECONDARY
, CURRENCY_CODE
, CURRENCY_CONVERSION_RATE
, CURRENCY_CONVERSION_TYPE
, CURRENCY_CONVERSION_DATE
, TAXABLE_ENTERED_DR
, TAXABLE_ENTERED_CR
, TAXABLE_ACCOUNTED_DR
, TAXABLE_ACCOUNTED_CR
, TAX_LINK_ID
, THIRD_PARTY_ID
, THIRD_PARTY_SUB_ID
, REVERSED_SOURCE_ID
, TAX_CODE_ID
, LOCATION_SEGMENT_ID
, SOURCE_TYPE_SECONDARY
, TAX_GROUP_CODE_ID
, REF_CUSTOMER_TRX_LINE_ID
, REF_CUST_TRX_LINE_GL_DIST_ID
, REF_ACCOUNT_CLASS
, ACTIVITY_BUCKET
, REF_LINE_ID
, FROM_AMOUNT_DR
, FROM_AMOUNT_CR
, FROM_ACCTD_AMOUNT_DR
, FROM_ACCTD_AMOUNT_CR
, REF_MF_DIST_FLAG
, REF_DIST_CCID)
SELECT
ar_distributions_s.nextval -- LINE_ID
, ard.SOURCE_ID -- SOURCE_ID
, ard.source_table -- SOURCE_TABLE
, ard.SOURCE_TYPE -- SOURCE_TYPE
, DECODE(double.side,'CASH',
crh.account_code_combination_id,
ard.CODE_COMBINATION_ID) -- code_combination_id
, DECODE(double.side,'APP',
--
DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),NULL),
--
DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
NULL, ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)))) --AMOUNT_DR
--
, DECODE(double.side,'APP',
DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
NULL,ABS(NVL(ard.AMOUNT_DR,0)-NVL(ard.AMOUNT_CR,0))),
--
DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),NULL)) --AMOUNT_CR
--
, DECODE(double.side,'APP',
--
DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),NULL),
--
DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
NULL, ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)))) --ACCTD_AMOUNT_DR
--
, DECODE(double.side,'APP',
DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
NULL,ABS(NVL(ard.ACCTD_AMOUNT_DR,0)-NVL(ard.ACCTD_AMOUNT_CR,0))),
--
DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),NULL)) --ACCTD_AMOUNT_CR
--
, SYSDATE -- CREATION_DATE
, 0 -- CREATED_BY
, 0 -- LAST_UPDATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, 0 -- LAST_UPDATE_LOGIN
, ard.ORG_ID
, DECODE(double.side,'APP',
'UPMFRAREAR','UPMFCRREAR') -- SOURCE_TABLE_SECONDARY
, DECODE(double.side,'CASH'
, crh.cash_receipt_history_id
, ard.SOURCE_ID) -- SOURCE_ID_SECONDARY
, ard.CURRENCY_CODE
, ard.CURRENCY_CONVERSION_RATE
, ard.CURRENCY_CONVERSION_TYPE
, ard.CURRENCY_CONVERSION_DATE
, ard.TAXABLE_ENTERED_DR
, ard.TAXABLE_ENTERED_CR
, ard.TAXABLE_ACCOUNTED_DR
, ard.TAXABLE_ACCOUNTED_CR
, ard.TAX_LINK_ID
, ard.THIRD_PARTY_ID
, ard.THIRD_PARTY_SUB_ID
, ard.REVERSED_SOURCE_ID
, ard.TAX_CODE_ID
, ard.LOCATION_SEGMENT_ID
, 'PSA_MF_RCT_DIST_ALL' -- SOURCE_TYPE_SECONDARY
, ard.TAX_GROUP_CODE_ID
, ard.REF_CUSTOMER_TRX_LINE_ID
, ard.REF_CUST_TRX_LINE_GL_DIST_ID
, ard.REF_ACCOUNT_CLASS
, ard.ACTIVITY_BUCKET
, ard.REF_LINE_ID
--
, DECODE(double.side,'APP',
--
DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),NULL),
--
DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
NULL, ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)))) --FROM_AMOUNT_DR
--
, DECODE(double.side,'APP',
DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
NULL,ABS(NVL(ard.AMOUNT_DR,0)-NVL(ard.AMOUNT_CR,0))),
--
DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),NULL)) --FROM_AMOUNT_CR
--
, DECODE(double.side,'APP',
--
DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),NULL),
--
DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
NULL, ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)))) --FROM_ACCTD_AMOUNT_DR
--
, DECODE(double.side,'APP',
DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
NULL,ABS(NVL(ard.ACCTD_AMOUNT_DR,0)-NVL(ard.ACCTD_AMOUNT_CR,0))),
--
DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),NULL)) --FROM_ACCTD_AMOUNT_CR
--
, DECODE(double.side,'CASH','N','Y') -- REF_MF_DIST_FLAG
, ard.REF_DIST_CCID
FROM ar_receivable_applications_all app,
ar_distributions_all ard,
(SELECT 'CASH' side FROM DUAL UNION
SELECT 'APP' side FROM DUAL ) double,
ar_cash_receipt_history_all crh
WHERE app.rowid >= l_start_rowid
AND app.rowid <= l_end_rowid
AND app.receivable_application_id = ard.source_id
AND ard.source_table = 'RA'
AND app.cash_receipt_history_id = crh.cash_receipt_history_id(+)
AND DECODE(double.side,'CASH',
DECODE(crh.cash_receipt_history_id,
NULL,'N','Y'),
'APP' ,'Y') = 'Y'
AND EXISTS (SELECT NULL FROM psa_mf_rct_dist_all psa
WHERE psa.receivable_application_id = app.receivable_application_id);
INSERT INTO ar_distributions_all (
LINE_ID
, SOURCE_ID
, SOURCE_TABLE
, SOURCE_TYPE
, CODE_COMBINATION_ID
, AMOUNT_DR
, AMOUNT_CR
, ACCTD_AMOUNT_DR
, ACCTD_AMOUNT_CR
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORG_ID
, SOURCE_TABLE_SECONDARY
, SOURCE_ID_SECONDARY
, CURRENCY_CODE
, CURRENCY_CONVERSION_RATE
, CURRENCY_CONVERSION_TYPE
, CURRENCY_CONVERSION_DATE
, TAXABLE_ENTERED_DR
, TAXABLE_ENTERED_CR
, TAXABLE_ACCOUNTED_DR
, TAXABLE_ACCOUNTED_CR
, TAX_LINK_ID
, THIRD_PARTY_ID
, THIRD_PARTY_SUB_ID
, REVERSED_SOURCE_ID
, TAX_CODE_ID
, LOCATION_SEGMENT_ID
, SOURCE_TYPE_SECONDARY
, TAX_GROUP_CODE_ID
, REF_CUSTOMER_TRX_LINE_ID
, REF_CUST_TRX_LINE_GL_DIST_ID
, REF_ACCOUNT_CLASS
, ACTIVITY_BUCKET
, REF_LINE_ID
, FROM_AMOUNT_DR
, FROM_AMOUNT_CR
, FROM_ACCTD_AMOUNT_DR
, FROM_ACCTD_AMOUNT_CR
, REF_MF_DIST_FLAG
, REF_DIST_CCID)
SELECT /*+ ordered rowid(app) use_nl(a,psatd,ctlgd,trx,crh) INDEX(a psa_mf_rct_dist_u1) INDEX(psatd psa_mf_trx_dist_u1)
INDEX(ctlgd ra_cust_trx_line_gl_dist_u1) */
ar_distributions_s.nextval -- LINE_ID
, a.receivable_application_id --SOURCE_ID
, 'RA' -- SOURCE_TABLE
, b.source_type
, DECODE(doub.side,'D',
DECODE(SIGN( DECODE(b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.mf_cash_ccid,
'EDISC' ,a.discount_ccid,
'UNEDISC',a.ue_discount_ccid),
psatd.mf_receivables_ccid),
DECODE(SIGN(DECODE
( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)
), 1 , psatd.mf_receivables_ccid,
DECODE( b.source_type,
'REC' ,a.mf_cash_ccid,
'EDISC' ,a.discount_ccid,
'UNEDISC',a.ue_discount_ccid))) -- CODE_COMBINATION_ID
, TO_NUMBER(DECODE(doub.side,'C',
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL),
DECODE( b.source_type,
'REC' ,-1 * a.amount,
'EDISC' ,-1 * a.discount_amount,
'UNEDISC',-1 * a.ue_discount_amount)),
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL)))) -- AMOUNT_DR
, TO_NUMBER(DECODE(doub.side,'D',
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL, --a.discount_amount,
'UNEDISC',NULL),
DECODE( b.source_type,
'REC' ,-1 * a.amount,
'EDISC' ,-1 * a.discount_amount,
'UNEDISC',-1 * a.ue_discount_amount)),
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL)))) -- AMOUNT_CR
, TO_NUMBER(DECODE(doub.side,'C',
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL, --a.discount_amount,
'UNEDISC',NULL), --a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,-1 * a.amount,
'EDISC' ,-1 * a.discount_amount, --NULL,
'UNEDISC',-1 * a.ue_discount_amount)),
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount, --NULL,
'UNEDISC',a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL)))) -- ACCTD_AMOUNT_DR
, TO_NUMBER(DECODE(doub.side,'D',
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL, --a.discount_amount,
'UNEDISC',NULL),
DECODE( b.source_type,
'REC' ,-1 * a.amount,
'EDISC' ,-1 * a.discount_amount,
'UNEDISC',-1 * a.ue_discount_amount)),
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL)))) -- ACCTD_AMOUNT_CR
, SYSDATE -- CREATION_DATE
, 0 -- CREATED_BY
, 0 -- LAST_UPDATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, 0 -- LAST_UPDATE_LOGIN
, app.org_id -- ORG_ID
,DECODE(doub.side,'D',
DECODE(SIGN( DECODE(b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,DECODE(crh.status,NULL,'UPMFRAMIAR','UPMFCHMIAR'),
'EDISC' ,'UPMFRAMIAR' ,
'UNEDISC','UPMFRAMIAR' ),
DECODE( b.source_type,
'REC' ,'UPMFRAMIAR',
'EDISC' ,'UPMFRAMIAR',
'UNEDISC','UPMFRAMIAR')),
DECODE(SIGN( DECODE(b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,'UPMFRAMIAR',
'EDISC' ,'UPMFRAMIAR',
'UNEDISC','UPMFRAMIAR'),
DECODE( b.source_type,
'REC' , DECODE(crh.status,NULL,'UPMFRAMIAR','UPMFCHMIAR'),
'EDISC' ,'UPMFRAMIAR',
'UNEDISC','UPMFRAMIAR'))) -- SOURCE_TABLE_SECONDARY
,DECODE(doub.side,'D',
DECODE(SIGN( DECODE(b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,DECODE(crh.status,NULL,
a.receivable_application_id,
crh.cash_receipt_history_id),
'EDISC' ,a.receivable_application_id ,
'UNEDISC',a.receivable_application_id ),
DECODE( b.source_type,
'REC' , a.receivable_application_id,
'EDISC' ,a.receivable_application_id ,
'UNEDISC',a.receivable_application_id )),
DECODE(SIGN( DECODE(b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' , a.receivable_application_id,
'EDISC' ,a.receivable_application_id ,
'UNEDISC',a.receivable_application_id ),
DECODE( b.source_type,
'REC' , DECODE(crh.status,NULL,
a.receivable_application_id,
crh.cash_receipt_history_id),
'EDISC' ,a.receivable_application_id ,
'UNEDISC',a.receivable_application_id ))) -- SOURCE_ID_SECONDARY
, trx.invoice_currency_code -- CURRENCY_CODE
, NULL -- CURRENCY_CONVERSION_RATE
, NULL -- CURRENCY_CONVERSION_TYPE
, NULL -- CURRENCY_CONVERSION_DATE
, NULL -- TAXABLE_ENTERED_DR
, NULL -- TAXABLE_ENTERED_CR
, NULL -- TAXABLE_ACCOUNTED_DR
, NULL -- TAXABLE_ACCOUNTED_CR
, NULL -- TAX_LINK_ID
, NULL -- THIRD_PARTY_ID
, NULL -- THIRD_PARTY_SUB_ID
, NULL -- REVERSED_SOURCE_ID
, NULL -- TAX_CODE_ID
, NULL -- LOCATION_SEGMENT_ID
, 'PSA_MF_RCT_DIST_ALL' -- SOURCE_TYPE_SECONDARY
, NULL -- TAX_GROUP_CODE_ID
, ctlgd.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
, ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
, ctlgd.account_class -- REF_ACCOUNT_CLASS
, CASE
WHEN b.source_type = 'REC' THEN
DECODE(ctlgd.account_class, 'REV', 'APP_LINE',
'TAX', 'APP_TAX',
'FREIGHT', 'APP_FRT','APP_LINE')
WHEN b.source_type = 'EDISC' THEN
DECODE(ctlgd.account_class, 'REV', 'ED_LINE',
'TAX', 'ED_TAX',
'FREIGHT', 'ED_FRT','ED_LINE')
ELSE
DECODE(ctlgd.account_class, 'REV', 'UNED_LINE',
'TAX', 'UNED_TAX',
'FREIGHT', 'UNED_FRT','UNED_LINE')
END -- ACTIVITY_BUCKET
, NULL -- REF_LINE_ID
, TO_NUMBER(DECODE(doub.side,'C',
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL),
DECODE( b.source_type,
'REC' ,-1 * a.amount,
'EDISC' ,-1 * a.discount_amount,
'UNEDISC',-1 * a.ue_discount_amount)),
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL)))) --FROM_AMOUNT_DR
, TO_NUMBER(DECODE(doub.side,'D',
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL),
DECODE( b.source_type,
'REC' ,-1 * a.amount,
'EDISC' ,-1 * a.discount_amount,
'UNEDISC',-1 * a.ue_discount_amount)),
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL)))) -- FROM_AMOUNT_CR
, TO_NUMBER(DECODE(doub.side,'C',
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL),
DECODE( b.source_type,
'REC' ,-1 * a.amount,
'EDISC' ,-1 * a.discount_amount,
'UNEDISC',-1 * a.ue_discount_amount)),
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL)))) --FROM_ACCTD_AMOUNT_DR
, TO_NUMBER(DECODE(doub.side,'D',
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL),
DECODE( b.source_type,
'REC' ,-1 * a.amount,
'EDISC' ,-1 * a.discount_amount,
'UNEDISC',-1 * a.ue_discount_amount)),
DECODE(SIGN(
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount),
DECODE( b.source_type,
'REC' ,NULL,
'EDISC' ,NULL,
'UNEDISC',NULL)))) --FROM_ACCTD_AMOUNT_CR
,DECODE(doub.side,'D',
DECODE(SIGN( DECODE(b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,DECODE(crh.status,NULL,'Y','N'),
'EDISC' ,'Y' ,
'UNEDISC','Y' ),
DECODE( b.source_type,
'REC' ,'Y',
'EDISC' ,'Y',
'UNEDISC','Y')),
DECODE(SIGN( DECODE(b.source_type,
'REC' ,a.amount,
'EDISC' ,a.discount_amount,
'UNEDISC',a.ue_discount_amount)),1,
DECODE( b.source_type,
'REC' ,'Y',
'EDISC' ,'Y',
'UNEDISC','Y'),
DECODE( b.source_type,
'REC' , DECODE(crh.status,NULL,'Y','N'),
'EDISC' ,'Y',
'UNEDISC','Y'))) -- REF_MF_DIST_FLAG
, NULL -- REF_DIST_CCID
FROM
ar_receivable_applications_all app,
psa_mf_rct_dist_all a,
psa_mf_trx_dist_all psatd,
ra_cust_trx_line_gl_dist_all ctlgd,
(SELECT 'REC' source_type FROM DUAL UNION
SELECT 'EDISC' source_type FROM DUAL UNION
SELECT 'UNEDISC' source_type FROM DUAL ) b,
(SELECT 'D' side FROM DUAL UNION
SELECT 'C' side FROM DUAL ) doub,
ra_customer_trx_all trx,
ar_cash_receipt_history_all crh
WHERE app.rowid >= l_start_rowid
AND app.rowid <= l_end_rowid
AND app.receivable_application_id = a.receivable_application_id
AND a.cust_trx_line_gl_dist_id = psatd.cust_trx_line_gl_dist_id
AND psatd.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
AND app.applied_customer_trx_id = trx.customer_trx_id
AND app.cash_receipt_history_id = crh.cash_receipt_history_id(+)
AND NVL(DECODE(b.source_type, 'REC' ,a.AMOUNT,
'EDISC' ,a.DISCOUNT_AMOUNT,
'UNEDISC',a.UE_DISCOUNT_AMOUNT),0) <> 0;
UPDATE ar_receivable_applications_all app
SET upgrade_method = '11I_MFAR_UPG'
WHERE app.rowid >= l_start_rowid
AND app.rowid <= l_end_rowid
AND EXISTS (SELECT NULL FROM psa_mf_rct_dist_all psa
WHERE psa.receivable_application_id = app.receivable_application_id);
ad_parallel_updates_pkg.processed_rowid_range(
l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
l_batch_size,
FALSE);
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
l_worker_id,
l_num_workers,
l_batch_size, 0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
l_batch_size,
TRUE);
INSERT INTO ar_distributions_all (
LINE_ID
, SOURCE_ID
, SOURCE_TABLE
, SOURCE_TYPE
, CODE_COMBINATION_ID
, AMOUNT_DR
, AMOUNT_CR
, ACCTD_AMOUNT_DR
, ACCTD_AMOUNT_CR
, CREATION_DATE
, CREATED_BY
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORG_ID
, SOURCE_TABLE_SECONDARY
, SOURCE_ID_SECONDARY
, CURRENCY_CODE
, CURRENCY_CONVERSION_RATE
, CURRENCY_CONVERSION_TYPE
, CURRENCY_CONVERSION_DATE
, TAXABLE_ENTERED_DR
, TAXABLE_ENTERED_CR
, TAXABLE_ACCOUNTED_DR
, TAXABLE_ACCOUNTED_CR
, TAX_LINK_ID
, THIRD_PARTY_ID
, THIRD_PARTY_SUB_ID
, REVERSED_SOURCE_ID
, TAX_CODE_ID
, LOCATION_SEGMENT_ID
, SOURCE_TYPE_SECONDARY
, TAX_GROUP_CODE_ID
, REF_CUSTOMER_TRX_LINE_ID
, REF_CUST_TRX_LINE_GL_DIST_ID
, REF_ACCOUNT_CLASS
, ACTIVITY_BUCKET
, REF_LINE_ID
, FROM_AMOUNT_DR
, FROM_AMOUNT_CR
, FROM_ACCTD_AMOUNT_DR
, FROM_ACCTD_AMOUNT_CR
, REF_MF_DIST_FLAG
, REF_DIST_CCID)
SELECT /*+ ordered rowid(mcd) use_nl(psamcd,cr) INDEX(psamcd psa_mf_misc_dist_u1) */
ar_distributions_s.nextval -- LINE_ID
, crhard.source_id -- SOURCE_ID
, 'CRH' -- SOURCE_TABLE
, crhard.source_type -- SOURCE_TYPE
, CASE WHEN doub.side = 'C' THEN
DECODE(sign ((nvl(amount_cr,0)-nvl(amount_dr,0))*mcd.amount*cr.amount*decode(crh.status, 'REVERSED' , -1, 1)),
1, DECODE( crh.status,
'REVERSED', psamcd.cash_ccid,
DECODE(crhard.source_type,
'CONFIRMATION', 'CONFIRMED',
'REMITTANCE', 'REMITTED',
'CASH','CLEARED'),
psamcd.cash_ccid,
psamcd.reversal_ccid),
crhard.code_combination_id)
ELSE
DECODE(sign ((nvl(amount_cr,0)-nvl(amount_dr,0))*mcd.amount*cr.amount*decode(crh.status, 'REVERSED' , -1, 1)),
-1, DECODE( crh.status,
'REVERSED', psamcd.cash_ccid,
DECODE(crhard.source_type,
'CONFIRMATION', 'CONFIRMED',
'REMITTANCE', 'REMITTED',
'CASH','CLEARED'),
psamcd.cash_ccid,
psamcd.reversal_ccid),
crhard.code_combination_id)
END -- CODE_COMBINATION_ID
, DECODE(doub.side,'D', DECODE(nvl(amount_dr, amount_cr), 0, 0, ABS(mcd.amount)) ,NULL ) --AMOUNT_DR
, DECODE(doub.side,'C', DECODE(nvl(amount_dr, amount_cr), 0, 0, ABS(mcd.amount)) ,NULL ) --AMOUNT_CR
, DECODE(doub.side,'D', DECODE(nvl(acctd_amount_dr, acctd_amount_cr), 0, 0, ABS(mcd.acctd_amount)) ,NULL ) --ACCTD_AMOUNT_DR
, DECODE(doub.side,'C', DECODE(nvl(acctd_amount_dr, acctd_amount_cr), 0, 0, ABS(mcd.acctd_amount)) ,NULL ) --ACCTD_AMOUNT_CR
, SYSDATE -- CREATION_DATE
, 0 -- CREATED_BY
, 0 -- LAST_UPDATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, 0 -- LAST_UPDATE_LOGIN
, mcd.org_id -- ORG_ID
, CASE
WHEN doub.side = 'C' THEN
DECODE(sign ((nvl(amount_cr,0)-nvl(amount_dr,0))*mcd.amount*cr.amount*decode(crh.status, 'REVERSED' , -1, 1)),
1, 'UPMFMCMIAR',
'UPMFMCREAR')
ELSE
DECODE(sign ((nvl(amount_cr,0)-nvl(amount_dr,0))*mcd.amount*cr.amount*decode(crh.status, 'REVERSED' , -1, 1)),
-1, 'UPMFMCMIAR',
'UPMFMCREAR')
END -- SOURCE_TABLE_SECONDARY
, NULL -- SOURCE_ID_SECONDARY
, cr.currency_code -- CURRENCY_CODE
, NULL -- CURRENCY_CONVERSION_RATE
, NULL -- CURRENCY_CONVERSION_TYPE
, NULL -- CURRENCY_CONVERSION_DATE
, NULL -- TAXABLE_ENTERED_DR
, NULL -- TAXABLE_ENTERED_CR
, NULL -- TAXABLE_ACCOUNTED_DR
, NULL -- TAXABLE_ACCOUNTED_CR
, NULL -- TAX_LINK_ID
, NULL -- THIRD_PARTY_ID
, NULL -- THIRD_PARTY_SUB_ID
, NULL -- REVERSED_SOURCE_ID
, NULL -- TAX_CODE_ID
, NULL -- LOCATION_SEGMENT_ID
, 'PSA_MF_MISC_DIST_ALL' -- SOURCE_TYPE_SECONDARY
, NULL -- TAX_GROUP_CODE_ID
, NULL -- REF_CUSTOMER_TRX_LINE_ID
, NULL -- REF_CUST_TRX_LINE_GL_DIST_ID
, NULL -- REF_ACCOUNT_CLASS
, NULL -- ACTIVITY_BUCKET
, NULL -- REF_LINE_ID
, DECODE(doub.side,'D', DECODE(nvl(amount_dr, amount_cr), 0, 0, ABS(mcd.amount)) ,NULL ) --AMOUNT_DR
, DECODE(doub.side,'C', DECODE(nvl(amount_dr, amount_cr), 0, 0, ABS(mcd.amount)) ,NULL ) --AMOUNT_CR
, DECODE(doub.side,'D', DECODE(nvl(acctd_amount_dr, acctd_amount_cr), 0, 0, ABS(mcd.acctd_amount)) ,NULL ) --ACCTD_AMOUNT_DR
, DECODE(doub.side,'C', DECODE(nvl(acctd_amount_dr, acctd_amount_cr), 0, 0, ABS(mcd.acctd_amount)) ,NULL ) --ACCTD_AMOUNT_CR
, NULL -- REF_MF_DIST_FLAG
, NULL -- REF_DIST_CCID
FROM ar_misc_cash_distributions_all mcd,
psa_mf_misc_dist_all psamcd,
ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ar_distributions_all crhard,
(SELECT 'D' side FROM DUAL UNION ALL
SELECT 'C' side FROM DUAL) doub
WHERE mcd.rowid >= l_start_rowid
AND mcd.rowid <= l_end_rowid
AND mcd.misc_cash_distribution_id = psamcd.misc_cash_distribution_id
AND mcd.cash_receipt_id = cr.cash_receipt_id
AND mcd.posting_control_id <> -3
AND crh.cash_receipt_id = mcd.cash_receipt_id
AND crh.posting_control_id <> -3
AND crh.status = psamcd.reference1
AND crh.cash_receipt_history_id = crhard.source_id
AND crhard.source_table = 'CRH';
ad_parallel_updates_pkg.processed_rowid_range(
l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
l_batch_size,
FALSE);
SELECT /*+ leading(pd,ctlgd,gud) rowid(pd) use_nl(ctlgd,ct,ent,ev,hdr,ln,lnk) use_hash(gud) swap_join_inputs(gud)
INDEX(ent xla_transaction_entities_N1)
INDEX(ev XLA_EVENTS_U2)
INDEX(hdr XLA_AE_HEADERS_N2)
INDEX (ln, XLA_AE_LINES_U1)
INDEX (lnk, XLA_DISTRIBUTION_LINKS_N1) */
ln.AE_HEADER_ID
, ln.AE_LINE_NUM
, ln.APPLICATION_ID
, ln.CODE_COMBINATION_ID
, ln.GL_TRANSFER_MODE_CODE
, ln.GL_SL_LINK_ID
, ln.ACCOUNTING_CLASS_CODE
, ln.PARTY_ID
, ln.PARTY_SITE_ID
, ln.PARTY_TYPE_CODE
, ln.ENTERED_DR
, ln.ENTERED_CR
, ln.ACCOUNTED_DR
, ln.ACCOUNTED_CR
, ln.DESCRIPTION
, ln.STATISTICAL_AMOUNT
, ln.CURRENCY_CODE
, ln.CURRENCY_CONVERSION_DATE
, ln.CURRENCY_CONVERSION_RATE
, ln.CURRENCY_CONVERSION_TYPE
, ln.USSGL_TRANSACTION_CODE
, ln.JGZZ_RECON_REF
, ln.CONTROL_BALANCE_FLAG
, ln.ANALYTICAL_BALANCE_FLAG
, ln.GL_SL_LINK_TABLE
, ln.DISPLAYED_LINE_NUMBER
, ln.UPG_BATCH_ID
, ln.UNROUNDED_ACCOUNTED_DR
, ln.UNROUNDED_ACCOUNTED_CR
, ln.GAIN_OR_LOSS_FLAG
, ln.UNROUNDED_ENTERED_DR
, ln.UNROUNDED_ENTERED_CR
, ln.SUBSTITUTED_CCID
, 'RECEIVABLE'
, ln.MPA_ACCRUAL_ENTRY_FLAG
, ln.ENCUMBRANCE_TYPE_ID
, ln.FUNDS_STATUS_CODE
, ln.MERGE_CODE_COMBINATION_ID
, ln.MERGE_PARTY_ID
, ln.MERGE_PARTY_SITE_ID
, ev.EVENT_ID
, ev.APPLICATION_ID
, ev.EVENT_TYPE_CODE
, decode(ev.event_type_code, 'INV_CREATE', 'INVOICE',
'INV_UPDATE', 'INVOICE',
'CM_CREATE' , 'CREDIT_MEMO',
'CM_UPDATE' , 'CREDIT_MEMO',
'DM_CREATE' , 'DEBIT_MEMO',
'DM_UPDATE' , 'DEBIT_MEMO',
'CB_CREATE' , 'CHARGEBACK',
'DEP_CREATE', 'DEPOSIT',
'DEP_UPDATE', 'DEPOSIT',
'GUAR_CREATE','GUARANTEE',
'GUAR_UPDATE','GUARANTEE',
'UNKNOWN') event_class_code
, ev.EVENT_DATE
, ev.ENTITY_ID
, ev.EVENT_STATUS_CODE
, ev.PROCESS_STATUS_CODE
, ev.REFERENCE_NUM_1
, ev.EVENT_NUMBER
, ctlgd.CUST_TRX_LINE_GL_DIST_ID
, ctlgd.CUSTOMER_TRX_LINE_ID
, ctlgd.CODE_COMBINATION_ID
, ctlgd.SET_OF_BOOKS_ID
, ctlgd.AMOUNT
, ctlgd.ACCTD_AMOUNT
, ctlgd.GL_DATE
, ctlgd.GL_POSTED_DATE
, ctlgd.ACCOUNT_CLASS
, ctlgd.posting_control_id
, pd.CUST_TRX_LINE_GL_DIST_ID
, pd.MF_RECEIVABLES_CCID
, pd.POSTING_CONTROL_ID
, MAX(ln.ae_line_num) OVER (PARTITION BY ln.ae_header_id) cnt_by_hdr
, pd.cust_trx_line_gl_dist_id
, ln.accounting_date
, ln.ledger_id
FROM ra_customer_trx_all ct
, ra_cust_trx_line_gl_dist_all ctlgd
, xla_upgrade_dates gud
, xla_transaction_entities_upg ent
, xla_events ev
, xla_ae_headers hdr
, xla_ae_lines ln
, xla_distribution_links lnk
, psa_mf_trx_dist_all pd
WHERE pd.ROWID >= l_start_rowid
AND pd.ROWID <= l_end_rowid
AND ct.customer_trx_id = ctlgd.customer_trx_id
AND ctlgd.cust_trx_line_gl_dist_id = pd.cust_trx_line_gl_dist_id
AND NVL(ct.ax_accounted_flag,'N') = 'N'
AND ctlgd.account_set_flag = 'N'
AND trunc(ctlgd.gl_date) BETWEEN gud.start_date AND gud.end_date
AND CTLGD.set_of_books_id = gud.ledger_id -- changed this from ct to ctlgd to enable better join to GUD
AND ent.ledger_id = ct.set_of_books_id
AND ent.application_id = 222
AND ent.entity_code = 'TRANSACTIONS'
AND ev.application_id = 222
AND hdr.application_id = 222
AND ln.application_id = 222
AND lnk.application_id = 222
AND ent.entity_id = ev.entity_id
AND ent.ledger_id = ct.set_of_books_id
AND ev.upg_batch_id = l_batch_id
AND ev.event_id = hdr.event_id
AND hdr.ledger_id = ent.ledger_id
AND hdr.event_id = ev.event_id
AND hdr.ae_header_id = ln.ae_header_id
AND hdr.ae_header_id = lnk.ae_header_id
AND ln.ae_line_num = lnk.ae_line_num
AND lnk.event_id = ev.event_id
AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
AND ev.reference_num_1 = ctlgd.posting_control_id
AND NVL(TRUNC(ctlgd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
UNION
SELECT /*+ leading(pd,ct,ctlgd,gud) rowid(pd) use_nl(ctlgd,ct,ent,ev,hdr,ln,lnk) use_hash(gud) swap_join_inputs(gud)
INDEX(ent xla_transaction_entities_N1)
INDEX(ev XLA_EVENTS_U2)
INDEX(hdr XLA_AE_HEADERS_N2)
INDEX (ln, XLA_AE_LINES_U1)
INDEX (lnk, XLA_DISTRIBUTION_LINKS_N1) */
ln.AE_HEADER_ID
, ln.AE_LINE_NUM
, ln.APPLICATION_ID
, ln.CODE_COMBINATION_ID
, ln.GL_TRANSFER_MODE_CODE
, ln.GL_SL_LINK_ID
, ln.ACCOUNTING_CLASS_CODE
, ln.PARTY_ID
, ln.PARTY_SITE_ID
, ln.PARTY_TYPE_CODE
, ln.ENTERED_DR
, ln.ENTERED_CR
, ln.ACCOUNTED_DR
, ln.ACCOUNTED_CR
, ln.DESCRIPTION
, ln.STATISTICAL_AMOUNT
, ln.CURRENCY_CODE
, ln.CURRENCY_CONVERSION_DATE
, ln.CURRENCY_CONVERSION_RATE
, ln.CURRENCY_CONVERSION_TYPE
, ln.USSGL_TRANSACTION_CODE
, ln.JGZZ_RECON_REF
, ln.CONTROL_BALANCE_FLAG
, ln.ANALYTICAL_BALANCE_FLAG
, ln.GL_SL_LINK_TABLE
, ln.DISPLAYED_LINE_NUMBER
, ln.UPG_BATCH_ID
, ln.UNROUNDED_ACCOUNTED_DR
, ln.UNROUNDED_ACCOUNTED_CR
, ln.GAIN_OR_LOSS_FLAG
, ln.UNROUNDED_ENTERED_DR
, ln.UNROUNDED_ENTERED_CR
, ln.SUBSTITUTED_CCID
, ln.BUSINESS_CLASS_CODE
, ln.MPA_ACCRUAL_ENTRY_FLAG
, ln.ENCUMBRANCE_TYPE_ID
, ln.FUNDS_STATUS_CODE
, ln.MERGE_CODE_COMBINATION_ID
, ln.MERGE_PARTY_ID
, ln.MERGE_PARTY_SITE_ID
, ev.EVENT_ID
, ev.APPLICATION_ID
, ev.EVENT_TYPE_CODE
, decode(ev.event_type_code, 'INV_CREATE', 'INVOICE',
'INV_UPDATE', 'INVOICE',
'CM_CREATE' , 'CREDIT_MEMO',
'CM_UPDATE' , 'CREDIT_MEMO',
'DM_CREATE' , 'DEBIT_MEMO',
'DM_UPDATE' , 'DEBIT_MEMO',
'CB_CREATE' , 'CHARGEBACK',
'DEP_CREATE', 'DEPOSIT',
'DEP_UPDATE', 'DEPOSIT',
'GUAR_CREATE','GUARANTEE',
'GUAR_UPDATE','GUARANTEE',
'UNKNOWN') event_class_code
, ev.EVENT_DATE
, ev.ENTITY_ID
, ev.EVENT_STATUS_CODE
, ev.PROCESS_STATUS_CODE
, ev.REFERENCE_NUM_1
, ev.EVENT_NUMBER
, ctlgd.CUST_TRX_LINE_GL_DIST_ID
, ctlgd.CUSTOMER_TRX_LINE_ID
, ctlgd.CODE_COMBINATION_ID
, ctlgd.SET_OF_BOOKS_ID
, ctlgd.AMOUNT
, ctlgd.ACCTD_AMOUNT
, ctlgd.GL_DATE
, ctlgd.GL_POSTED_DATE
, ctlgd.ACCOUNT_CLASS
, ctlgd.posting_control_id
, ctlgd.CUST_TRX_LINE_GL_DIST_ID
, NULL
, ctlgd.POSTING_CONTROL_ID
, 999999999 cnt_by_hdr
, ctlgd.cust_trx_line_gl_dist_id
, ln.accounting_date
, ln.ledger_id
FROM ra_customer_trx_all ct
, ra_cust_trx_line_gl_dist_all ctlgd
, xla_upgrade_dates gud
, xla_transaction_entities_upg ent
, xla_events ev
, xla_ae_headers hdr
, xla_ae_lines ln
, xla_distribution_links lnk
,(SELECT /*+ rowid(pdist) use_nl(dist) no_merge */ dist.customer_trx_id
FROM ra_cust_trx_line_gl_dist_all dist,
psa_mf_trx_dist_all pdist,
xla_upgrade_dates xud
WHERE pdist.cust_trx_line_gl_dist_id = dist.cust_trx_line_gl_dist_id
AND trunc(dist.gl_date) between xud.start_date and xud.end_date
AND pdist.ROWID >= l_start_rowid
AND pdist.ROWID <= l_end_rowid
AND NOT EXISTS
( SELECT /*+ordered */ 'x'
FROM xla_transaction_entities_upg xte,
xla_ae_headers xah,
xla_ae_lines xal
WHERE nvl(xte.source_id_int_1,-99) = dist.customer_trx_id
AND xte.ledger_id = dist.set_of_books_id
AND xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.entity_id = xah.entity_id
AND xah.application_id = 222
AND xte.ledger_id = xah.ledger_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.application_id = 222
AND xal.accounting_class_code = 'RECEIVABLE'
AND xal.ae_line_num > 999999999
AND xal.accounting_date between xud.start_date and xud.end_date
)
GROUP BY dist.customer_trx_id
) pd
WHERE ct.customer_trx_id = pd.customer_trx_id
AND ct.customer_trx_id = ctlgd.customer_trx_id
AND ctlgd.account_class = 'REC'
AND ctlgd.account_set_flag = 'N'
AND NVL(ct.ax_accounted_flag,'N') = 'N'
AND trunc(ctlgd.gl_date) BETWEEN gud.start_date AND gud.end_date
AND CTLGD.set_of_books_id = gud.ledger_id -- changed this from ct to ctlgd to enable better join to GUD
AND ent.ledger_id = ct.set_of_books_id
AND ent.application_id = 222
AND ent.entity_code = 'TRANSACTIONS'
AND ev.upg_batch_id = l_batch_id
AND ev.application_id = 222
AND hdr.application_id = 222
AND ln.application_id = 222
AND lnk.application_id = 222
AND ent.entity_id = ev.entity_id
AND ent.ledger_id = ct.set_of_books_id
AND ev.event_id = hdr.event_id
AND hdr.ledger_id = ent.ledger_id
AND hdr.event_id = ev.event_id
AND hdr.ae_header_id = ln.ae_header_id
AND hdr.ae_header_id = lnk.ae_header_id
AND ln.ae_line_num = lnk.ae_line_num
AND lnk.event_id = ev.event_id
AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
AND ev.reference_num_1 = ctlgd.posting_control_id
AND NVL(TRUNC(ctlgd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
l_worker_id,
l_num_workers,
l_batch_size, 0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
l_batch_size,
TRUE);
INSERT INTO xla_ae_lines
( AE_HEADER_ID ,
AE_LINE_NUM ,
APPLICATION_ID ,
CODE_COMBINATION_ID ,
GL_TRANSFER_MODE_CODE ,
GL_SL_LINK_ID ,
ACCOUNTING_CLASS_CODE ,
PARTY_ID ,
PARTY_SITE_ID ,
PARTY_TYPE_CODE ,
ENTERED_DR ,
ENTERED_CR ,
ACCOUNTED_DR ,
ACCOUNTED_CR ,
DESCRIPTION ,
STATISTICAL_AMOUNT ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_DATE ,
CURRENCY_CONVERSION_RATE ,
CURRENCY_CONVERSION_TYPE ,
USSGL_TRANSACTION_CODE ,
JGZZ_RECON_REF ,
CONTROL_BALANCE_FLAG ,
ANALYTICAL_BALANCE_FLAG ,
GL_SL_LINK_TABLE ,
DISPLAYED_LINE_NUMBER ,
UPG_BATCH_ID ,
UNROUNDED_ACCOUNTED_DR ,
UNROUNDED_ACCOUNTED_CR ,
GAIN_OR_LOSS_FLAG ,
UNROUNDED_ENTERED_DR ,
UNROUNDED_ENTERED_CR ,
BUSINESS_CLASS_CODE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
accounting_date ,
ledger_id ) VALUES
(LAE_HEADER_ID(i),
LAE_LINE_NUM(i),
LAPPLICATION_ID(i),
LCODE_COMBINATION_ID(i),
LGL_TRANSFER_MODE_CODE(i),
LGL_SL_LINK_ID(i),
LACCOUNTING_CLASS_CODE(i),
LPARTY_ID(i),
LPARTY_SITE_ID(i),
LPARTY_TYPE_CODE(i),
LENTERED_DR(i),
LENTERED_CR(i),
LACCOUNTED_DR(i),
LACCOUNTED_CR(i),
LDESCRIPTION(i),
LSTATISTICAL_AMOUNT(i),
LCURRENCY_CODE(i),
LCURRENCY_CONVERSION_DATE(i),
LCURRENCY_CONVERSION_RATE(i),
LCURRENCY_CONVERSION_TYPE(i),
LUSSGL_TRANSACTION_CODE(i),
LJGZZ_RECON_REF(i),
LCONTROL_BALANCE_FLAG(i),
LANALYTICAL_BALANCE_FLAG(i),
LGL_SL_LINK_TABLE(i),
LDISPLAYED_LINE_NUMBER(i),
LUPG_BATCH_ID(i),
LUNROUNDED_ACCOUNTED_DR(i),
LUNROUNDED_ACCOUNTED_CR(i),
LGAIN_OR_LOSS_FLAG(i),
LUNROUNDED_ENTERED_DR(i),
LUNROUNDED_ENTERED_CR(i),
LBUSINESS_CLASS_CODE(i),
l_sys_date,
0,
l_sys_date,
0 ,
laccounting_date(i),
lledger_id(i));
INSERT INTO XLA_DISTRIBUTION_LINKS
(APPLICATION_ID,
EVENT_ID,
AE_HEADER_ID,
AE_LINE_NUM,
ACCOUNTING_LINE_CODE,
ACCOUNTING_LINE_TYPE_CODE,
REF_AE_HEADER_ID,
SOURCE_DISTRIBUTION_TYPE,
SOURCE_DISTRIBUTION_ID_NUM_1,
SOURCE_DISTRIBUTION_ID_NUM_2,
SOURCE_DISTRIBUTION_ID_NUM_3,
SOURCE_DISTRIBUTION_ID_NUM_4,
SOURCE_DISTRIBUTION_ID_NUM_5,
UNROUNDED_ENTERED_DR,
UNROUNDED_ENTERED_CR,
UNROUNDED_ACCOUNTED_DR,
UNROUNDED_ACCOUNTED_CR,
MERGE_DUPLICATE_CODE,
TAX_LINE_REF_ID,
TAX_SUMMARY_LINE_REF_ID,
TAX_REC_NREC_DIST_REF_ID,
STATISTICAL_AMOUNT,
TEMP_LINE_NUM,
EVENT_TYPE_CODE,
EVENT_CLASS_CODE,
REF_EVENT_ID,
UPG_BATCH_ID)
VALUES
(DAPPLICATION_ID(i),
DEVENT_ID(i),
DAE_HEADER_ID(i),
DAE_LINE_NUM(i),
DACCOUNTING_LINE_CODE(i),
DACCOUNTING_LINE_TYPE_CODE(i),
DREF_AE_HEADER_ID(i), --reference header id
DSOURCE_DISTRIBUTION_TYPE(i),
DSOURCE_DISTRIBUTION_ID_NUM_1(i),
'',
'',
'',
'',
DUNROUNDED_ENTERED_DR(i),
DUNROUNDED_ENTERED_CR(i),
DUNROUNDED_ACCOUNTED_DR(i),
DUNROUNDED_ACCOUNTED_CR(i),
DMERGE_DUPLICATE_CODE(i),
DTAX_LINE_REF_ID(i),
'',
'',
'',
DTEMP_LINE_NUM(i),
DEVENT_TYPE_CODE(i),
DEVENT_CLASS_CODE(i),
DREF_EVENT_ID(i),
DUPG_BATCH_ID(i));
ad_parallel_updates_pkg.processed_rowid_range(
l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
l_batch_size,
FALSE);