[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';
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
);
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;
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)
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);
'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);
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)
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);
'After insertion into zx_rep_trx_jx_ext_t ');
SELECT ft.territory_short_name,
ft.alternate_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);
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)
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);
'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;
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
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
)
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
);
'After insertion into zx_rep_trx_jx_ext_t ');
SELECT 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.trx_id = cash.applied_customer_trx_id
AND dtl.request_id = p_request_id ;
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)
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);
'After insertion into zx_rep_trx_jx_ext_t ');