[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT xle_etb.establishment_id
FROM zx_party_tax_profile ptp,
xle_etb_profiles xle_etb
WHERE ptp.party_id = xle_etb.party_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND xle_etb.legal_entity_id = c_legal_entity_id
AND xle_etb.main_establishment_flag = 'Y';
UPDATE zx_rep_trx_detail_t temp
SET (temp.TAXABLE_AMT,temp.TAXABLE_AMT_FUNCL_CURR)
=
(SELECT
SUM(nvl(lnk.UNROUNDED_ENTERED_DR,0)- nvl(lnk.UNROUNDED_ENTERED_CR,0)
) taxable_amt,
SUM(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0)- nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)
) taxable_act_amt
FROM zx_rep_trx_detail_t dtl,
ap_prepay_app_dists pre_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE dtl.detail_tax_line_id = temp.detail_tax_line_id
AND pre_dist.prepay_app_distribution_id = dtl.taxable_item_source_id
AND pre_dist.prepay_dist_lookup_code = 'PREPAY APPL'
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_PREPAY'
AND lnk.source_distribution_id_num_1 = pre_dist.prepay_app_dist_id
AND lnk.event_id = pre_dist.accounting_event_id
AND lnk.ae_line_num = ael.ae_line_num
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.ledger_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
AND aeh.balance_type_code = 'A'
AND aeh.application_id = lnk.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY','EXCHANGE_RATE_VARIANCE')
AND ael.application_id = aeh.application_id
)
WHERE temp.request_id = p_request_id
AND temp.trx_line_class = 'PREPAY_APPLICATION'
AND temp.extract_source_ledger = 'AP'
AND temp.entity_code = 'AP_INVOICES'
AND temp.application_id = 200
AND NOT EXISTS (select 1
from AP_INVOICE_DISTRIBUTIONS_ALL dist1
where dist1.invoice_distribution_id = temp.taxable_item_source_id
AND NVL(dist1.historical_flag,'N') = 'Y');
'Updated Taxable Amount for prepayment Applications for '||to_char(SQL%ROWCOUNT)||' records.');
UPDATE zx_rep_trx_detail_t dtl
SET (dtl.TAXABLE_AMT,dtl.TAXABLE_AMT_FUNCL_CURR)
= (SELECT dist.amount, nvl(dist.base_amount,dist.amount)
FROM AP_INVOICE_DISTRIBUTIONS_ALL dist
WHERE dist.invoice_distribution_id = dtl.taxable_item_source_id)
WHERE dtl.request_id = p_request_id
AND dtl.trx_line_class = 'PREPAY_APPLICATION'
AND dtl.extract_source_ledger = 'AP'
AND dtl.entity_code = 'AP_INVOICES'
AND dtl.application_id = 200
AND NVL(dtl.historical_flag,'N') = 'Y'
AND EXISTS (select 1
from AP_INVOICE_DISTRIBUTIONS_ALL dist1
where dist1.invoice_distribution_id = dtl.taxable_item_source_id
AND NVL(dist1.historical_flag,'N') = 'Y');
UPDATE zx_rep_trx_detail_t dtl
SET (dtl.TAXABLE_AMT,dtl.TAXABLE_AMT_FUNCL_CURR)
= (SELECT sum(mc.amount), sum(nvl(mc.base_amount,mc.amount))
FROM AP_INVOICE_DISTRIBUTIONS_ALL dist,
AP_MC_INVOICE_DISTS mc
WHERE dtl.taxable_item_source_id = dist.invoice_distribution_id
AND dist.invoice_distribution_id = mc.invoice_distribution_id
AND mc.set_of_books_id = p_trl_global_variables_rec.reporting_ledger_id)
WHERE dtl.request_id = p_request_id
AND dtl.trx_line_class = 'PREPAY_APPLICATION'
AND dtl.extract_source_ledger = 'AP'
AND dtl.entity_code = 'AP_INVOICES'
AND dtl.application_id = 200
AND NVL(dtl.historical_flag,'N') = 'Y'
AND EXISTS (select 1
from AP_INVOICE_DISTRIBUTIONS_ALL dist1
where dist1.invoice_distribution_id = dtl.taxable_item_source_id
AND NVL(dist1.historical_flag,'N') = 'Y');
'Updated Taxable Amount for Upgraded prepayment Applications for '||to_char(SQL%ROWCOUNT)||' records.');
INSERT INTO zx_rep_trx_jx_ext_t
(detail_tax_line_ext_id,
detail_tax_line_id,
attribute1,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
(SELECT zx_rep_trx_jx_ext_t_s.nextval,
dtl.detail_tax_line_id,
'Yes', --fl.meaning,
dtl.created_by,
dtl.creation_date,
dtl.last_updated_by,
dtl.last_update_date,
dtl.last_update_login,
p_request_id
FROM zx_rep_trx_detail_t dtl
WHERE EXISTS (select distinct ah.invoice_id
FROM ap_holds_all ah
WHERE ah.invoice_id = dtl.trx_id
AND ah.release_lookup_code IS NULL )
AND dtl.request_id = p_request_id);
'Insertion for Hold , ext.attribute1 : '||to_char(SQL%ROWCOUNT) );
SELECT
dtl.detail_tax_line_id,
dtl.trx_id,
dtl.trx_currency_code,
fcv.name,
ab.batch_name,
acc.TAX_ACCOUNT_CCID
BULK COLLECT INTO
l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_trx_currency_code_tbl,
l_trx_currency_desc_tbl,
l_batch_name_tbl,
l_acc_ccid_tbl
FROM
zx_rep_trx_detail_t dtl,
fnd_currencies_vl fcv,
ap_invoices_all ai,
ap_batches_all ab,
zx_rates_b rates,
zx_accounts acc
WHERE
dtl.request_id = p_request_id
AND dtl.trx_currency_code = fcv.currency_code
AND dtl.trx_id = ai.invoice_id
AND ai.batch_id = ab.batch_id(+)
AND dtl.tax_rate_id = rates.tax_rate_id(+)
AND acc.TAX_ACCOUNT_ENTITY_ID(+) = rates.tax_rate_id;
'Before insertion into zx_rep_trx_jx_ext_t for report '||p_report_name );
USING ( SELECT 1 FROM dual ) T
ON ( ext.detail_tax_line_id = l_detail_tax_line_id_tbl(i))
WHEN MATCHED THEN UPDATE SET ext.ATTRIBUTE9 = l_trx_currency_desc_tbl(i),
ext.attribute8 = l_batch_name_tbl(i),
ext.attribute5 = l_match_tbl(i)
WHEN NOT MATCHED THEN
INSERT (
detail_tax_line_ext_id,
detail_tax_line_id,
attribute9,
attribute8,
attribute5,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id
)
VALUES ( ZX_MIGRATE_UTIL.get_next_seqid('ZX_REP_TRX_JX_EXT_T_S'),
l_detail_tax_line_id_tbl(i),
l_trx_currency_desc_tbl(i),
l_batch_name_tbl(i),
l_match_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id
);
'Update for ERV/IPV , ext.numeric1 : ' );
DELETE FROM zx_rep_actg_ext_t acct1
WHERE acct1.request_id = p_request_id
AND acct1.actg_ext_line_id <> (SELECT MIN(actg_ext_line_id)
FROM zx_rep_actg_ext_t acct2
WHERE acct2.actg_header_id= acct1.actg_header_id
and acct2.actg_event_id = acct1.actg_event_id
AND acct2.actg_source_id = acct1.actg_source_id
AND acct2.detail_tax_line_id = acct1.detail_tax_line_id
AND acct2.request_id = acct1.request_id
GROUP BY acct2.actg_header_id, acct2.actg_event_id,
acct2.actg_source_id,acct2.detail_tax_line_id
HAVING COUNT( DISTINCT acct2.actg_ext_line_id) >=2);
'Deleted duplicate rows from zx_rep_actg_ext_t: '||to_char(SQL%ROWCOUNT) );
update zx_rep_actg_ext_t act
set act.period_name = (select apd.period_name
from ap_invoice_distributions_all apd,
zx_rep_trx_detail_t dtl
where dtl.trx_id = apd.invoice_id
and dtl.TAXABLE_ITEM_SOURCE_ID = apd.invoice_distribution_id
and act.detail_tax_line_id = dtl.detail_tax_line_id
and dtl.request_id = p_request_id)
where act.request_id = p_request_id
and act.period_name is null;
'Number of rows updated: '||to_char(SQL%ROWCOUNT));
UPDATE zx_rep_trx_jx_ext_t ext
SET ext.numeric1 =
(SELECT sum(nvl(lnk.UNROUNDED_accounted_DR,0)-nvl(lnk.UNROUNDED_accounted_CR,0))
FROM zx_rep_trx_detail_t dtl,
zx_rep_actg_ext_t act_ext,
ap_invoice_distributions_all ap_dist,
po_distributions_all pod,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_distribution_links lnk
WHERE dtl.ref_doc_application_id = 201
and dtl.request_id = p_request_id
and act_ext.detail_tax_line_id = dtl.detail_tax_line_id
and dtl.detail_tax_line_id = ext.detail_tax_line_id
and ap_dist.line_type_lookup_code in('IPV','ERV')
and ap_dist.invoice_id = dtl.trx_id
and ap_dist.invoice_line_number = dtl.trx_line_id
and ap_dist.related_id = dtl.taxable_item_source_id
and ap_dist.invoice_distribution_id <> ap_dist.related_id
and aeh.application_id = 200
and aeh.ledger_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
and aeh.event_id = ap_dist.accounting_event_id
and ael.ae_header_id = aeh.ae_header_id
and ael.application_id = aeh.application_id
and lnk.application_id = aeh.application_id
and lnk.ae_header_id = aeh.ae_header_id
and lnk.event_id = aeh.event_id
and lnk.source_distribution_type = 'AP_INV_DIST'
and lnk.source_distribution_id_num_1 = ap_dist.invoice_distribution_id
and lnk.ae_line_num = ael.ae_line_num
and ap_dist.po_distribution_id = pod.po_distribution_id
and aeh.balance_type_code = 'A'
and ((pod.accrue_on_receipt_flag = 'Y' and ael.accounting_class_code in ('EXCHANGE_RATE_VARIANCE','IPV'))
OR (NVL(pod.accrue_on_receipt_flag, 'N') = 'N' and ael.accounting_class_code in ('ITEM EXPENSE') ) )
)
where ext.request_id = p_request_id;
'Number of rows updated in table zx_rep_trx_jx_ext_t: '||to_char(SQL%ROWCOUNT) );
UPDATE zx_rep_trx_detail_t dtl
SET dtl.TAXABLE_AMT = 0,
dtl.TAXABLE_AMT_FUNCL_CURR = 0
WHERE dtl.request_id = p_request_id
AND NOT EXISTS
(SELECT /*+ FIRST_ROWS(1) */ 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL item,
AP_INVOICE_DISTRIBUTIONS_ALL tax
WHERE tax.DETAIL_TAX_DIST_ID = DTL.ACTG_SOURCE_ID
AND item.PERIOD_NAME = tax.PERIOD_NAME
AND item.INVOICE_ID = tax.INVOICE_ID
and item.ORG_ID = tax.ORG_ID
and item.INVOICE_DISTRIBUTION_ID = tax.CHARGE_APPLICABLE_TO_DIST_ID
AND item.INVOICE_DISTRIBUTION_ID = DTL.TAXABLE_ITEM_SOURCE_ID
AND ROWNUM=1
)
AND EXISTS
(SELECT 1
FROM ZX_REC_NREC_DIST
WHERE REC_NREC_TAX_DIST_ID = DTL.ACTG_SOURCE_ID
);
'Number of rows updated to Amt as 0 in table zx_rep_trx_detail_t: '||to_char(SQL%ROWCOUNT));
DELETE FROM zx_rep_actg_ext_t acct1
WHERE acct1.request_id = p_request_id
AND acct1.actg_ext_line_id <> (SELECT MIN(actg_ext_line_id)
FROM zx_rep_actg_ext_t acct2
WHERE acct2.actg_header_id= acct1.actg_header_id
and acct2.actg_event_id = acct1.actg_event_id
AND acct2.actg_source_id = acct1.actg_source_id
AND acct2.detail_tax_line_id = acct1.detail_tax_line_id
AND acct2.request_id = acct1.request_id
GROUP BY acct2.actg_header_id, acct2.actg_event_id,
acct2.actg_source_id,acct2.detail_tax_line_id
HAVING COUNT( DISTINCT acct2.actg_ext_line_id) >=2);
'Deleted duplicate rows from zx_rep_actg_ext_t: '||to_char(SQL%ROWCOUNT) );
'Update zx_rep_trx_detail_t.taxable_amt_funcl_curr for IPV issue' );
UPDATE zx_rep_trx_detail_t dtl1
SET dtl1.taxable_amt_funcl_curr = nvl(dtl1.taxable_amt_funcl_curr,0)
+ NVL(( SELECT sum(nvl(lnk.UNROUNDED_accounted_DR,0)-nvl(lnk.UNROUNDED_accounted_CR,0)) *
decode(sign(nvl(dtl1.taxable_amt_funcl_curr,0)),0,1,sign(nvl(dtl1.taxable_amt_funcl_curr,0)))
FROM zx_rep_trx_detail_t dtl2,
zx_rep_actg_ext_t act_ext,
AP_INVOICE_DISTRIBUTIONS_ALL AP_DIST,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE ap_dist.invoice_id=dtl2.trx_id
and ap_dist.invoice_line_number=dtl2.trx_line_id
and ap_dist.related_id=dtl2.taxable_item_source_id
and ap_dist.invoice_distribution_id <> ap_dist.related_id
and ap_dist.line_type_lookup_code in ('IPV','ERV')
and dtl2.detail_tax_line_id=act_ext.detail_tax_line_id
and lnk.application_id=200
and lnk.source_distribution_type='AP_INV_DIST'
and lnk.source_distribution_id_num_1=ap_dist.invoice_distribution_id
and lnk.event_id=ap_dist.accounting_event_id
and lnk.ae_header_id=act_ext.actg_header_id
and lnk.event_id=act_ext.actg_event_id
and lnk.ae_line_num=ael.ae_line_num
and aeh.ae_header_id=ael.ae_header_id
and aeh.ledger_id=p_trl_global_variables_rec.reporting_ledger_id
and aeh.ae_header_id=lnk.ae_header_id
and aeh.application_id=lnk.application_id
and ael.application_id=aeh.application_id
and ael.accounting_class_code not in ('LIABILITY','NRTAX','RTAX')
and dtl2.request_id = p_request_id
and dtl2.detail_tax_line_id = dtl1.detail_tax_line_id
),0),
dtl1.taxable_amt = nvl(dtl1.taxable_amt,0)
+ NVL(( SELECT sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)-nvl(lnk.UNROUNDED_ENTERED_CR,0)) *
decode(sign(nvl(dtl1.taxable_amt,0)),0,1,sign(nvl(dtl1.taxable_amt,0)))
FROM zx_rep_trx_detail_t dtl2,
zx_rep_actg_ext_t act_ext,
AP_INVOICE_DISTRIBUTIONS_ALL AP_DIST,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE ap_dist.invoice_id=dtl2.trx_id
and ap_dist.invoice_line_number=dtl2.trx_line_id
and ap_dist.related_id=dtl2.taxable_item_source_id
and ap_dist.invoice_distribution_id <> ap_dist.related_id
and ap_dist.line_type_lookup_code in ('IPV','ERV')
and dtl2.detail_tax_line_id=act_ext.detail_tax_line_id
and lnk.application_id=200
and lnk.source_distribution_type='AP_INV_DIST'
and lnk.source_distribution_id_num_1=ap_dist.invoice_distribution_id
and lnk.event_id=ap_dist.accounting_event_id
and lnk.ae_header_id=act_ext.actg_header_id
and lnk.event_id=act_ext.actg_event_id
and lnk.ae_line_num=ael.ae_line_num
and aeh.ae_header_id=ael.ae_header_id
and aeh.ledger_id=p_trl_global_variables_rec.reporting_ledger_id
and aeh.ae_header_id=lnk.ae_header_id
and aeh.application_id=lnk.application_id
and ael.application_id=aeh.application_id
and ael.accounting_class_code not in ('LIABILITY','NRTAX','RTAX')
and dtl2.request_id = p_request_id
and dtl2.detail_tax_line_id = dtl1.detail_tax_line_id
),0)
where dtl1.request_id = p_request_id
AND dtl1.application_id = 200
and dtl1.ref_doc_application_id = 201;
'Number of rows updated : '||to_char(SQL%ROWCOUNT) );
'Update zx_rep_trx_detail_t.taxable_amt_funcl_curr with AP Dist base_amount' );
UPDATE zx_rep_trx_detail_t dtl1
SET dtl1.taxable_amt_funcl_curr =
decode(sign(nvl(dtl1.taxable_amt_funcl_curr,0)),0,1,sign(nvl(dtl1.taxable_amt_funcl_curr,0)))
*
abs((select SUM(NVL(ap_dist.base_amount, ap_dist.amount))
from ap_invoice_distributions_all ap_dist
where ap_dist.invoice_distribution_id = dtl1.taxable_item_source_id)
+ NVL((select SUM(NVL(ap_dist1.base_amount, ap_dist1.amount))
from ap_invoice_distributions_all ap_dist1
where ap_dist1.invoice_id=dtl1.trx_id
and ap_dist1.invoice_line_number=dtl1.trx_line_id
and ap_dist1.related_id=dtl1.taxable_item_source_id
and AP_DIST1.INVOICE_DISTRIBUTION_ID <> AP_DIST1.RELATED_ID
and ap_dist1.line_type_lookup_code in ('IPV','ERV')),0)),
dtl1.taxable_amt =
decode(sign(nvl(dtl1.taxable_amt,0)),0,1,sign(nvl(dtl1.taxable_amt,0)))
*
abs((select SUM(ap_dist.amount)
from ap_invoice_distributions_all ap_dist
where ap_dist.invoice_distribution_id = dtl1.taxable_item_source_id)
+ NVL((select SUM(ap_dist1.amount)
from ap_invoice_distributions_all ap_dist1
where ap_dist1.invoice_id=dtl1.trx_id
and ap_dist1.invoice_line_number=dtl1.trx_line_id
and ap_dist1.related_id=dtl1.taxable_item_source_id
and ap_dist1.invoice_distribution_id <> ap_dist1.related_id
and ap_dist1.line_type_lookup_code in ('IPV','ERV')),0))
WHERE dtl1.ledger_id=p_trl_global_variables_rec.ledger_id
AND dtl1.request_id = p_request_id
AND dtl1.application_id = 200
AND dtl1.ref_doc_application_id = 201;
'Update zx_rep_trx_detail_t.taxable_amt_funcl_curr with AP Dist base_amount' );
UPDATE ZX_REP_TRX_DETAIL_T DTL
SET DTL.TAX_RECOVERY_RATE =
( SELECT AP_DIST.REC_NREC_RATE
FROM AP_INVOICE_DISTRIBUTIONS_ALL AP_DIST
WHERE AP_DIST.INVOICE_ID=DTL.TRX_ID
AND AP_DIST.DETAIL_TAX_DIST_ID =DTL.ACTG_SOURCE_ID
AND AP_DIST.LINE_TYPE_LOOKUP_CODE IN ('REC_TAX','NONREC_TAX')
--AND AP_DIST.HISTORICAL_FLAG = 'Y' -- Commented the condition for Bug#12878223
AND AP_DIST.ORG_ID = DTL.INTERNAL_ORGANIZATION_ID
)
WHERE DTL.REQUEST_ID = P_REQUEST_ID
AND DTL.HISTORICAL_FLAG = 'Y'
AND NVL(DTL.OFFSET_FLAG,'N') = 'N'
AND DTL.OFFSET_TAX_RATE_CODE IS NULL
AND DTL.APPLICATION_ID = 200;
'Number of rows updated For Recovery Rate: '||to_char(SQL%ROWCOUNT) );
UPDATE ZX_REP_TRX_DETAIL_T DTL
SET DTL.TAX_RECOVERY_RATE = (SELECT (ZX_DIST.rec_nrec_tax_amt/ZX_DIST.trx_line_dist_tax_amt)*100
FROM zx_rec_nrec_dist ZX_DIST
WHERE DTL.EXTRACT_SOURCE_LEDGER = 'AP'
AND ZX_DIST.rec_nrec_tax_dist_id = DTL.actg_source_id
AND abs(ZX_DIST.trx_line_dist_tax_amt) > abs(ZX_DIST.rec_nrec_tax_amt)
AND ZX_DIST.rec_nrec_tax_amt <> 0
)
WHERE DTL.REQUEST_ID = P_REQUEST_ID
AND DTL.HISTORICAL_FLAG = 'Y'
AND EXISTS(SELECT 1 FROM zx_rec_nrec_dist ZX_DIST1
WHERE ZX_DIST1.rec_nrec_tax_dist_id = DTL.actg_source_id
AND abs(ZX_DIST1.trx_line_dist_tax_amt) > abs(ZX_DIST1.rec_nrec_tax_amt)
AND ZX_DIST1.rec_nrec_tax_amt <> 0
);
'Number of rows updated For Recovery Rate - 2: '||to_char(SQL%ROWCOUNT) );
SELECT dtl.detail_tax_line_id,
dtl.trx_id,
dtl.billing_tp_country,
dtl.internal_organization_id --Bug 5251425
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_country_code_tbl,
l_internal_organization_id_tbl --Bug 5251425
FROM zx_reporting_types_b rep_type,
zx_reporting_codes_b rep_code,
zx_report_codes_assoc rep_ass,
zx_party_tax_profile ptp,
xle_etb_profiles xle_pf ,
zx_rep_trx_detail_t dtl
WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
AND rep_type.reporting_type_code = 'MEMBER STATE'
AND rep_code.reporting_code_id = rep_ass.reporting_code_id
AND rep_ass.entity_code = 'ZX_PARTY_TAX_PROFILE'
AND rep_ass.entity_id = ptp.party_tax_profile_id
AND ptp.party_id = xle_pf.party_id
AND ptp.Party_Type_Code = 'LEGAL_ESTABLISHMENT'
AND xle_pf.establishment_id = l_establishment_id
AND xle_pf.establishment_id = dtl.establishment_id
AND rep_code.reporting_code_char_value <> dtl.billing_tp_country
AND dtl.request_id = p_request_id
AND EXISTS
(
SELECT 1
FROM zx_reporting_codes_b code
WHERE code.reporting_type_id = rep_code.reporting_type_id
and code.reporting_code_char_value = dtl.billing_tp_country
);
INSERT INTO zx_rep_trx_jx_ext_t
(detail_tax_line_ext_id,
detail_tax_line_id,
attribute1,
attribute2,
attribute3,
attribute6,--Bug 5251425
attribute7,--Bug 5251425
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_org_vat_num_tbl(i),
l_territory_short_name_tbl(i),
l_alternate_territory_name_tbl(i),
l_created_by_tbl(i),--Bug 5251425
l_ou_desc_tbl(i),--Bug 5251425
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t for report '||p_report_name );
-- Delete Unwanted lines from Detail ITF
DELETE FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
DELETE FROM zx_rep_actg_ext_t
WHERE request_id = p_request_id
AND NVL(gl_transfer_flag,'N') <>'Y';
'No of Unposted transactions deleted from zx_rep_actg_ext_t: '|| sql%rowcount);
DELETE FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = p_request_id
AND NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
WHERE act.detail_tax_line_id = dtl.detail_tax_line_id) ;
'No of rows deleted from zx_rep_trx_detail_t: '|| sql%rowcount);
DELETE FROM zx_rep_actg_ext_t
WHERE request_id = p_request_id
AND NVL(gl_transfer_flag,'N') = 'Y';
'No of Unposted transactions deleted from zx_rep_actg_ext_t: '|| sql%rowcount);
DELETE FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = p_request_id
AND NVL(dtl.posted_flag,'N') = 'A'
AND NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
WHERE act.detail_tax_line_id = dtl.detail_tax_line_id);
'No of rows deleted from zx_rep_trx_detail_t: '|| sql%rowcount);
DELETE FROM zx_rep_actg_ext_t acct1
WHERE acct1.request_id = p_request_id
AND acct1.actg_ext_line_id <> (SELECT MIN(actg_ext_line_id)
FROM zx_rep_actg_ext_t acct2
WHERE acct2.actg_header_id= acct1.actg_header_id
and acct2.actg_event_id = acct1.actg_event_id
AND acct2.actg_source_id = acct1.actg_source_id
AND acct2.detail_tax_line_id = acct1.detail_tax_line_id
AND acct2.request_id = acct1.request_id
GROUP BY acct2.actg_header_id, acct2.actg_event_id,
acct2.actg_source_id,acct2.detail_tax_line_id
HAVING COUNT( DISTINCT acct2.actg_ext_line_id) >=2);
'For Primary Ledger Deleted duplicate rows from zx_rep_actg_ext_t: '||to_char(SQL%ROWCOUNT) );
SELECT dtl.detail_tax_line_id,
dtl.tax_rate_code
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_tax_rate_code_tbl
FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = p_request_id;
INSERT INTO zx_rep_trx_jx_ext_t
(detail_tax_line_ext_id,
detail_tax_line_id,
attribute1,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_adj_tax_code_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
SELECT ft.territory_short_name,
nvl(ft.alternate_territory_code, ft.territory_code)
INTO p_territory_short_name_tbl(i),
p_alternate_territory_name_tbl(i)
FROM fnd_territories_vl ft
WHERE ft.territory_code = p_country_code_tbl(i);
SELECT rep_code.reporting_code_char_value
INTO p_org_vat_num_tbl(i)
FROM zx_reporting_types_b rep_type,
zx_reporting_codes_b rep_code,
zx_report_codes_assoc rep_ass,
zx_party_tax_profile ptp,
xle_etb_profiles xle_pf
-- zx_rep_trx_detail_t dtl
WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
AND rep_type.reporting_type_code = 'FSO_REG_NUM'
AND rep_code.reporting_code_id = rep_ass.reporting_code_id
AND rep_ass.entity_code = 'ZX_PARTY_TAX_PROFILE'
AND rep_ass.entity_id = ptp.party_tax_profile_id
AND ptp.party_id = xle_pf.party_id
AND ptp.Party_Type_Code = 'LEGAL_ESTABLISHMENT'
AND xle_pf.establishment_id = p_establishment_id;
SELECT rep_code.reporting_code_char_value
INTO p_adj_tax_code_tbl(i)
FROM zx_reporting_types_b rep_type,
zx_reporting_codes_b rep_code,
zx_report_codes_assoc rep_ass,
zx_rates_b zx_rate
WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
AND rep_type.reporting_type_code = 'ZX_ADJ_TAX_CLASSIF_CODE'
AND rep_code.reporting_code_id = rep_ass.reporting_code_id
AND rep_ass.entity_code = 'ZX_RATES'
AND rep_ass.entity_id = zx_rate.tax_rate_id
AND zx_rate.tax_rate_code = p_tax_rate_code_tbl(i);
SELECT fu.user_name
INTO p_created_by_tbl(i)
FROM ap_invoices_all ai,
fnd_user fu
WHERE ai.invoice_id = p_trx_id_tbl(i)
AND fu.user_id = ai.created_by ;
SELECT hou.NAME
INTO p_ou_desc_tbl(i)
FROM hr_operating_units hou
WHERE hou.organization_id = p_internal_organization_id_tbl(i);
SELECT
ln.meaning
INTO l_nls_no
FROM
fnd_lookups ln, ap_lookup_codes la
WHERE
ln.lookup_type = 'YES_NO'
AND ln.lookup_code = 'N'
AND la.lookup_type = 'NLS REPORT PARAMETER'
AND la.lookup_code = 'ALL';
SELECT Decode(p_ACC_CCID_TBL(i), act.ACTG_LINE_CCID,NULL,l_nls_no )
INTO p_match_tbl(i)
FROM ZX_REP_ACTG_EXT_T act
WHERE act.detail_tax_line_id = p_detail_tax_line_id_tbl(i);
SELECT SUM(nvl(amount_applied,0)) ,sum(nvl(tax_applied,0))
INTO l_amount_recvd_tbl(p_trx_id_tbl(i)),l_tax_amount_rcvd_tbl(p_trx_id_tbl(i))
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE applied_customer_trx_id = p_trx_id_tbl(i)
AND org_id = p_org_id_tbl(i)
AND status = 'APP'
AND application_type = 'CASH';
' TRL to filter data. Otherwise the extracted data will be deleted from TRL temp tables: ');
UPDATE zx_rep_trx_detail_t dtl
set dtl.tax_line_id = (select min (tax_line_id)
from zx_lines lines
where lines.application_id = 222
and lines.trx_id = dtl.ADJUSTED_DOC_TRX_ID
and lines.trx_line_id = dtl.APPLIED_TO_TRX_LINE_ID
and lines.tax_rate_id = dtl.tax_rate_id
and nvl(lines.hq_estb_reg_number,fnd_api.g_miss_char) =
P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
)
WHERE dtl.request_id = P_REQUEST_ID
and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
and dtl.APPLICATION_ID =222
and dtl.APPLIED_FROM_ENTITY_CODE = 'APP';
UPDATE zx_rep_trx_detail_t dtl
set dtl.tax_line_id = (select min (tax_line_id)
from zx_lines lines
where lines.application_id = 222
and lines.trx_id = dtl.ADJUSTED_DOC_TRX_ID
and lines.trx_line_id = dtl.APPLIED_TO_TRX_LINE_ID
AND lines.tax_rate_id = dtl.tax_rate_id
AND nvl(lines.hq_estb_reg_number,fnd_api.g_miss_char) =
P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
)
WHERE dtl.request_id = P_REQUEST_ID
and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
and dtl.APPLICATION_ID =222
and dtl.EVENT_CLASS_CODE = 'ADJ';
UPDATE zx_rep_trx_detail_t dtl
set tax_line_id = (select min (tax_line_id)
from zx_lines lines
where lines.application_id = 222
and lines.trx_id = dtl.TRX_ID
and lines.trx_line_id = dtl.TRX_LINE_ID)
WHERE dtl.request_id = P_REQUEST_ID
and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
and dtl.APPLICATION_ID =222
and dtl.EVENT_CLASS_CODE ='ADJ';
SELECT dtl.detail_tax_line_id,
dtl.trx_id,
dtl.trx_line_id,
dtl.event_class_code,
--rep_code.reporting_code_name,
--assoc.reporting_code_char_value,
ZX_EXTRACT_PKG.get_vat_transaction_code_name(
dtl.tax_line_id,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2,
'NAME'),
ZX_EXTRACT_PKG.get_vat_transaction_code_name(
dtl.tax_line_id,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2,
'CODE'),
dtl.adjusted_doc_trx_id,
NVL(dtl.applied_from_trx_id,NULL),
NVL(dtl.applied_to_trx_line_id,NULL), -- Adjusted_doc_line_id
dtl.adjusted_doc_application_id,
dtl.adjusted_doc_event_class_code,
dtl.adjusted_doc_entity_code,
dtl.adjusted_doc_date,
decode(P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SITE_REPORTED,'SHIP TO', dtl.shipping_tp_country,
dtl.billing_tp_country),
dtl.internal_organization_id,
NVL(dtl.SHIPPING_TP_SITE_TAX_REG_NUM,NULL),
NVL(dtl.BILLING_TP_SITE_TAX_REG_NUM,NULL),
NVL(dtl.BILLING_TP_TAX_REG_NUM,NULL),
dtl.billing_tp_name,
dtl.shipping_tp_name,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
BULK COLLECT
INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_trx_line_id_tbl,
l_event_class_code_tbl,
l_reporting_code_tbl,
l_reporting_code_char_tbl,
l_adj_trx_id_tbl,
l_receivable_app_id_tbl,
l_adj_trx_line_id_tbl,
l_adj_application_id_tbl,
l_adj_event_class_code_tbl,
l_adj_entity_code_tbl,
l_adj_doc_date_tbl,
l_country_code_tbl,
l_org_id_tbl,
l_ship_to_site_tax_reg_num_tbl,
l_bill_to_site_tax_reg_num_tbl,
l_bill_to_tax_reg_num_tbl,
l_billing_tp_name_tbl,
l_shipping_tp_name_tbl,
l_out_of_period_adj_tbl,
l_func_curr_line_amt_tbl,
l_country_code_reg_num_tbl,
l_tax_reg_num_tbl,
l_adj_tax_invoice_tbl,
l_adj_gl_date_tbl,
l_adj_trx_date_tbl,
l_adj_trx_class_mng_tbl,
l_hq_estb_reg_num_tbl,
l_disc_class_mng_tbl,
l_disc_apply_date_tbl
FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = P_REQUEST_ID
and ZX_EXTRACT_PKG.get_vat_transaction_code_name(
dtl.tax_line_id,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1,
P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2,
'CODE') IS NOT NULL;
SELECT TAX_INVOICE_DATE,
TRX_LINE_GL_DATE,
TRX_DATE,
LINE_CLASS,
HQ_ESTB_PARTY_TAX_PROF_ID
INTO l_adj_tax_invoice_tbl(i),
l_adj_gl_date_tbl(i),
l_adj_trx_date_tbl(i),
l_adj_trx_class_tbl(i),
l_hq_estb_ptp_id_tbl(i)
FROM ZX_LINES_DET_FACTORS zx_det
WHERE zx_det.application_id = 222
AND zx_det.trx_id = l_adj_trx_id_tbl(i)
and zx_det.trx_line_id = Nvl(l_adj_trx_line_id_tbl(i),zx_det.trx_line_id)
AND ROWNUM = 1;
SELECT registration_number
INTO l_hq_estb_reg_num_tbl(i)
FROM zx_registrations
WHERE party_tax_profile_id = l_hq_estb_ptp_id_tbl(i)
AND registration_number = P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
AND rownum = 1 ;
SELECT sum(nvl(ACCTD_AMOUNT_CR,0) - nvl(ACCTD_AMOUNT_DR,0))
INTO l_func_curr_line_amt_tbl(i)
FROM ar_distributions_all
WHERE source_id = l_trx_id_tbl(i)
AND source_table = 'ADJ'
AND ref_customer_trx_line_id = l_adj_trx_line_id_tbl(i)
AND org_id = l_org_id_tbl(i);
SELECT sum(nvl(ACCTD_AMOUNT_CR,0) - nvl(ACCTD_AMOUNT_DR,0))
INTO l_func_curr_line_amt_tbl(i)
FROM ar_distributions_all
WHERE source_id = l_receivable_app_id_tbl(i)
AND source_table = 'RA'
AND source_type IN ('EDISC', 'UNEDISC')
AND ref_customer_trx_line_id = l_adj_trx_line_id_tbl(i)
AND org_id = l_org_id_tbl(i);
SELECT apply_date
INTO l_disc_apply_date_tbl(i)
FROM ar_receivable_applications_all
WHERE cash_receipt_id = l_trx_id_tbl(i)
AND status ='APP'
AND applied_customer_trx_id = l_adj_trx_id_tbl(i)
AND org_id = l_org_id_tbl(i) ;
SELECT sum(nvl(ACCTD_AMOUNT,0))
INTO l_func_curr_line_amt_tbl(i)
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = l_trx_id_tbl(i)
AND customer_trx_line_id = l_trx_line_id_tbl(i)
AND org_id = l_org_id_tbl(i);
SELECT rep_code.reporting_code_char_value
INTO l_country_code_reg_num_tbl(i)
FROM zx_reporting_types_b rep_type,
zx_reporting_codes_b rep_code
WHERE rep_type.reporting_type_id = rep_code.reporting_type_id
AND rep_type.reporting_type_code = 'MEMBER STATE'
AND rep_code.reporting_code_char_value
= (SubStr(Nvl(Nvl(l_ship_to_site_tax_reg_num_tbl(i),
l_bill_to_site_tax_reg_num_tbl(i)),
l_bill_to_tax_reg_num_tbl(i)),1,2));
' Inserting Data into ZX_REP_TRX_JX_EXT_T ' );
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
attribute1, --County_code
attribute2, --Reporting Code Name
attribute10, --Reporting Code
attribute3, --Adjusted doc GL Date
attribute4, --Adjusted doc Tax date
attribute5, --Out of period adjustments
attribute6, --Tax reg num
attribute7, -- adjusted doc trx type
attribute8, -- adjustment hq estb reg num
attribute9, -- Apply date for discounts
attribute11, -- Disc trx class
numeric1, --Functional currency line amount
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_country_code_reg_num_tbl(i),
l_reporting_code_tbl(i),
l_reporting_code_char_tbl(i),
l_adj_gl_date_tbl(i),
l_adj_tax_invoice_tbl(i),
l_out_of_period_adj_tbl(i),
l_tax_reg_num_tbl(i),
l_adj_trx_class_mng_tbl(i),
l_hq_estb_reg_num_tbl(i),
l_disc_apply_date_tbl(i),
l_disc_class_mng_tbl(i),
l_func_curr_line_amt_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
P_REQUEST_ID);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = dtl.detail_tax_line_id);
DELETE FROM zx_rep_actg_ext_t
WHERE request_id = p_request_id
AND NVL(gl_transfer_flag,'N') <>'Y';
'No of Unposted transactions deleted from zx_rep_actg_ext_t: '|| sql%rowcount);
DELETE FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = p_request_id
AND NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
WHERE act.detail_tax_line_id = dtl.detail_tax_line_id) ;
'No of rows deleted from zx_rep_trx_detail_t: '|| sql%rowcount);
DELETE FROM zx_rep_actg_ext_t
WHERE request_id = p_request_id
AND NVL(gl_transfer_flag,'N') = 'Y';
'No of posted transactions deleted from zx_rep_actg_ext_t: '|| sql%rowcount);
DELETE FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = p_request_id
AND NVL(dtl.posted_flag,'N') = 'Y'
AND NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
WHERE act.detail_tax_line_id = dtl.detail_tax_line_id);
'No of rows deleted from zx_rep_trx_detail_t: '|| sql%rowcount);
SELECT detail_tax_line_id,
event_class_mapping_id,
trx_id,
trx_line_id,
trx_level_type,
place_of_supply_type_code,
ship_to_location_id,
ship_from_location_id,
bill_to_location_id,
bill_from_location_id,
poa_location_id,
poo_location_id,
def_place_of_supply_type_code
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_event_class_mapping_id_tbl,
l_trx_id_tbl,
l_trx_line_id_tbl,
l_trx_level_type_tbl,
l_pos_type_code_tbl,
l_ship_to_location_id_tbl,
l_ship_from_location_id_tbl,
l_bill_to_location_id_tbl,
l_bill_from_location_id_tbl,
l_poa_location_id_tbl,
l_poo_location_id_tbl,
l_def_pos_type_code_tbl
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = P_REQUEST_ID;
SELECT
zxevntclsmap.event_class_mapping_id,
zxevntclsmap.ship_to_party_type,
zxevntclsmap.ship_from_party_type,
zxevntclsmap.poa_party_type,
zxevntclsmap.poo_party_type,
zxevntclsmap.bill_to_party_type,
zxevntclsmap.bill_from_party_type
INTO
prev_event_class_mapping_id,
zx_valid_init_params_pkg.source_rec.ship_to_party_type,
zx_valid_init_params_pkg.source_rec.ship_from_party_type,
zx_valid_init_params_pkg.source_rec.poa_party_type,
zx_valid_init_params_pkg.source_rec.poo_party_type,
zx_valid_init_params_pkg.source_rec.bill_to_party_type,
zx_valid_init_params_pkg.source_rec.bill_from_party_type
FROM ZX_EVNT_CLS_MAPPINGS zxevntclsmap
WHERE zxevntclsmap.event_class_mapping_id = l_event_class_mapping_id_tbl(i);
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
attribute1,
attribute2,
attribute3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_state_tbl(i),
l_county_tbl(i),
l_city_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
/* select gl.set_of_books_id
into l_set_of_books_id
from gl_sets_of_books gl, ar_system_parameters ar
where gl.set_of_books_id = ar.set_of_books_id;
DELETE FROM zx_rep_actg_ext_t
WHERE NVL(gl_transfer_flag,'N') <>'Y'
AND request_id = p_request_id;
DELETE FROM zx_rep_trx_detail_t dtl
WHERE NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
WHERE act.detail_tax_line_id = dtl.detail_tax_line_id)
AND dtl.request_id = p_request_id;
DELETE FROM zx_rep_actg_ext_t
WHERE NVL(gl_transfer_flag,'N') = 'Y'
AND request_id = p_request_id;
DELETE FROM zx_rep_trx_detail_t dtl
WHERE NOT EXISTS (SELECT 1 FROM zx_rep_actg_ext_t act
WHERE act.detail_tax_line_id = dtl.detail_tax_line_id)
AND dtl.request_id = p_request_id;
select gl.period_name
into l_period_from
from gl_period_statuses gl
--, ar_system_parameters ar
where gl.start_date = P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW
and gl.set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
--l_set_of_books_id
and gl.application_id = 222
and rownum = 1;
select gl.period_name
bulk collect into l_end_period_tbl
from gl_period_statuses gl
where gl.end_date >= trunc(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH)
and gl.set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
and gl.application_id = 222
order by gl.end_date ;
/* select gl.period_name
into l_period_to
from gl_period_statuses gl
--, ar_system_parameters ar
where gl.end_date >= P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH
and gl.set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID
---l_set_of_books_id
and gl.application_id = 222
and rownum = 1;
select to_char(chart_of_accounts_id)
into l_CHART_OF_ACCOUNTS_ID
from gl_ledgers where ledger_id = P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID;
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING'
);
SELECT to_number(SubStr(l_balancing_segment,8)) INTO L_SEG_NUM FROM dual;
SELECT
det.detail_tax_line_id,
det.trx_id,
act.ACTG_LINE_CCID,
det.ledger_id
BULK COLLECT INTO
l_detail_tax_line_id_tbl,
l_trx_id_tbl,
L_CCID_TBL,
L_LEDGER_ID_TBL
FROM
zx_rep_trx_detail_t det ,
ZX_REP_ACTG_EXT_T act
WHERE det.request_id = p_request_id
AND det.DETAIL_TAX_LINE_ID = ACT.DETAIL_TAX_LINE_ID(+);
INSERT INTO ZX_REP_TRX_JX_EXT_T
(
detail_tax_line_ext_id,
detail_tax_line_id,
numeric1,
attribute1,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id
)
VALUES
(
zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_gl_activity_tbl(i),
l_bal_seg_prompt_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id
);
'After insertion into zx_rep_trx_jx_ext_t ');
SELECT distinct dtl.detail_tax_line_id,
dtl.trx_id ,
dtl.internal_organization_id
BULK COLLECT INTO
l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_org_id_tbl
FROM zx_rep_trx_detail_t dtl ,
ar_receivable_applications_all cash
WHERE dtl.request_id = p_request_id
AND dtl.trx_id = cash.applied_customer_trx_id
AND cash.status = 'APP'
AND cash.application_type = 'CASH';
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
numeric1, --C_TAX_AMOUNT_RECEIVED
numeric2,--C_AMOUNT_RECEIVED
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_tax_received_tbl(i),
l_amount_received_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = dtl.detail_tax_line_id);