[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ZX_DET_FACT.EVENT_CLASS_CODE ,
ZX_DET_FACT.APPLICATION_ID ,
ZX_DET_FACT.ENTITY_CODE ,
ZX_DET_FACT.TRX_ID ,
ZX_PRE_REC_OPT.TAX_PROVIDER_ID ,
ZX_PRE_REC_OPT.TAX_REGIME_CODE ,
ZX_DET_FACT.TRX_DATE ,
ZX_DET_FACT.RECEIVABLES_TRX_TYPE_ID
INTO
l_event_class_code,
l_application_id,
l_entity_code,
l_trx_id,
l_tax_provider_id,
l_tax_regime_code,
g_trx_date,
g_trx_type_id
FROM
ZX_LINES_DET_FACTORS ZX_DET_FACT ,
ZX_USER_PROC_INPUT_V ZX_PRE_REC_OPT
WHERE
ZX_DET_FACT.INTERNAL_ORGANIZATION_ID = ZX_PRE_REC_OPT.INTERNAL_ORGANIZATION_ID
AND ZX_DET_FACT.APPLICATION_ID = ZX_PRE_REC_OPT.APPLICATION_ID
AND ZX_DET_FACT.EVENT_CLASS_CODE = ZX_PRE_REC_OPT.EVENT_CLASS_CODE
AND ZX_DET_FACT.ENTITY_CODE = ZX_PRE_REC_OPT.ENTITY_CODE
AND ZX_DET_FACT.TRX_ID = ZX_PRE_REC_OPT.TRX_ID
AND ROWNUM = 1;
SELECT 1
INTO l_exists_in_hdrs_gt
FROM ZX_PRVDR_HDR_EXTNS_GT
WHERE event_class_code = l_event_class_code
AND application_id = l_application_id
AND entity_code = l_entity_code
AND trx_id = l_trx_id
AND provider_id = l_tax_provider_id
AND tax_regime_code = l_tax_regime_code
AND rownum = 1;
INSERT INTO
ZX_PRVDR_HDR_EXTNS_GT
(
EVENT_CLASS_CODE,
APPLICATION_ID,
ENTITY_CODE,
TRX_ID ,
PROVIDER_ID ,
TAX_REGIME_CODE,
HEADER_EXT_VARCHAR_ATTRIBUTE1,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES(
l_Event_Class_Code,
l_Application_Id,
l_Entity_Code,
l_Trx_Id,
l_Tax_Provider_Id ,
l_Tax_Regime_Code,
l_header_ext_attr1,
SYSDATE,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id ,
fnd_global.conc_login_id);
g_string :='Not able to insert in to ZX_PRVDR_HDR_EXTNS_GT table ';
SELECT
ZX_LINE_DET_FACT.INTERNAL_ORGANIZATION_ID ,
ZX_LINE_DET_FACT.APPLICATION_ID ,
ZX_LINE_DET_FACT.ENTITY_CODE ,
ZX_LINE_DET_FACT.EVENT_CLASS_CODE ,
ZX_LINE_DET_FACT.TRX_ID ,
ZUPI.TAX_PROVIDER_ID ,
ZUPI.TAX_REGIME_CODE ,
ZX_LINE_DET_FACT.TRX_LEVEL_TYPE ,
ZX_LINE_DET_FACT.TRX_LINE_ID ,
ZX_LINE_DET_FACT.PRODUCT_ID ,
ZX_LINE_DET_FACT.PRODUCT_ORG_ID ,
ZX_LINE_DET_FACT.SHIP_To_PARTY_TAX_PROF_ID ,
ZX_LINE_DET_FACT.SHIP_FROM_PARTY_TAX_PROF_ID ,
ZX_LINE_DET_FACT.EXEMPT_CERTIFICATE_NUMBER ,
ZX_LINE_DET_FACT.EXEMPT_REASON_CODE ,
ZX_LINE_DET_FACT.EXEMPTION_CONTROL_FLAG ,
ZX_LINE_DET_FACT.SHIP_TO_SITE_TAX_PROF_ID ,
ZX_LINE_DET_FACT.SHIP_TO_LOCATION_ID ,
ZX_LINE_DET_FACT.SHIP_TO_CUST_ACCT_SITE_USE_ID ,
ZX_LINE_DET_FACT.BILL_TO_CUST_ACCT_SITE_USE_ID ,
ZX_LINE_DET_FACT.BILL_TO_SITE_TAX_PROF_ID ,
ZX_LINE_DET_FACT.BILL_TO_PARTY_TAX_PROF_ID ,
ZX_LINE_DET_FACT.BILL_TO_LOCATION_ID ,
ZX_LINE_DET_FACT.TRADING_HQ_SITE_TAX_PROF_ID ,
ZX_LINE_DET_FACT.TRADING_HQ_PARTY_TAX_PROF_ID ,
ZX_LINE_DET_FACT.BILL_THIRD_PTY_ACCT_ID ,
ZX_LINE_DET_FACT.LINE_LEVEL_ACTION ,
ZX_LINE_DET_FACT.ADJUSTED_DOC_TRX_ID ,
ZX_LINE_DET_FACT.LINE_AMT ,
ZX_LINE_DET_FACT.ADJUSTED_DOC_APPLICATION_ID ,
ZX_LINE_DET_FACT.ADJUSTED_DOC_ENTITY_CODE ,
ZX_LINE_DET_FACT.ADJUSTED_DOC_EVENT_CLASS_CODE ,
ZX_LINE_DET_FACT.ADJUSTED_DOC_LINE_ID ,
ZX_LINE_DET_FACT.RECEIVABLES_TRX_TYPE_ID ,
ZX_LINE_DET_FACT.ADJUSTED_DOC_TRX_LEVEL_TYPE ,
ZX_LINE_DET_FACT.SHIP_THIRD_PTY_ACCT_SITE_ID ,
ZX_LINE_DET_FACT.BILL_THIRD_PTY_ACCT_SITE_ID
FROM
ZX_LINES_DET_FACTORS ZX_LINE_DET_FACT ,
ZX_USER_PROC_INPUT_V ZUPI ,
ZX_TRX_PRE_PROC_OPTIONS_GT ZTPPO
WHERE
ZX_LINE_DET_FACT.INTERNAL_ORGANIZATION_ID = ZUPI.INTERNAL_ORGANIZATION_ID
AND ZX_LINE_DET_FACT.APPLICATION_ID = ZUPI.APPLICATION_ID
AND ZX_LINE_DET_FACT.EVENT_CLASS_CODE = ZUPI.EVENT_CLASS_CODE
AND ZX_LINE_DET_FACT.ENTITY_CODE = ZUPI.ENTITY_CODE
AND ZX_LINE_DET_FACT.TRX_ID = ZUPI.TRX_ID
AND ZX_LINE_DET_FACT.INTERNAL_ORGANIZATION_ID = ZTPPO.INTERNAL_ORGANIZATION_ID
AND ZX_LINE_DET_FACT.APPLICATION_ID = ZTPPO.APPLICATION_ID
AND ZX_LINE_DET_FACT.EVENT_CLASS_CODE = ZTPPO.EVENT_CLASS_CODE
AND ZX_LINE_DET_FACT.ENTITY_CODE = ZTPPO.ENTITY_CODE
AND ZX_LINE_DET_FACT.TRX_ID = ZTPPO.TRX_ID
AND ((ZX_LINE_DET_FACT.EVENT_ID = ZTPPO.EVENT_ID)
OR (ZX_LINE_DET_FACT.LINE_LEVEL_ACTION = 'DELETE'));
SELECT
Internal_Organization_Id ,
Application_Id,
Entity_Code,
Event_Class_Code,
Trx_Id,
Tax_Provider_Id,
Tax_Regime_Code,
Trx_Line_Type,
Trx_Line_Id,
Product_Id,
Product_Org_Id,
Ship_To_Party_Tax_Profile_Id,
Ship_From_Party_Tax_Profile_Id,
Exempt_Certificate_Number,
Exempt_Reason_Code,
Exemption_Control_Flag,
Ship_To_Site_Tax_Prof_Id,
Ship_To_Location_Id,
Ship_To_Cust_Acct_Site_Use_Id,
Bill_To_Cust_Acct_Site_Use_Id,
Bill_To_Site_Tax_Prof_Id,
Bill_To_Party_Tax_Prof_Id,
Bill_To_Location_Id,
Trading_Hq_Site_Tax_Prof_Id,
Trading_Hq_Party_Tax_Prof_Id,
Bill_Third_Pty_Acct_Id,
Line_Level_Action,
Adjusted_Doc_Trx_Id,
Line_Amt,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_line_id,
Receivables_Trx_Type_Id,
Adjusted_Doc_Trx_Level_Type,
Ship_Third_Pty_Acct_Site_Id,
Bill_Third_Pty_Acct_Site_Id
FROM
ZX_USR_PROC_NEG_LINE_V zxproc
WHERE
zxproc.trx_line_id = g_trx_line_id;
SELECT party_id INTO l_ship_from_party_id
FROM ZX_PARTY_TAX_PROFILE
WHERE party_tax_profile_id = ship_from_tx_id_tab(i);
is necessary as we want to bulk insert all values into ZX_PRVDR_LINE_EXTNS_GT
through nested tables only.Doing this in a separate loop only for clarity*/
derive_product_code;
/*This portion of the code performs a bulk insert into the ZX_PRVDR_LINE_EXTNS_GT
through all the pl/sql tables populated above*/
IF g_line_negation THEN
null;
INSERT INTO
ZX_PRVDR_LINE_EXTNS_GT
(
EVENT_CLASS_CODE,
APPLICATION_ID ,
ENTITY_CODE ,
TRX_ID ,
TRX_LINE_ID ,
TRX_LEVEL_TYPE ,
PROVIDER_ID ,
TAX_REGIME_CODE ,
-- LINE_EXT_VARCHAR_ATTRIBUTE1,
LINE_EXT_VARCHAR_ATTRIBUTE2,
LINE_EXT_VARCHAR_ATTRIBUTE3,
LINE_EXT_VARCHAR_ATTRIBUTE4,
LINE_EXT_VARCHAR_ATTRIBUTE5,
LINE_EXT_VARCHAR_ATTRIBUTE6,
LINE_EXT_VARCHAR_ATTRIBUTE7,
LINE_EXT_VARCHAR_ATTRIBUTE8,
LINE_EXT_VARCHAR_ATTRIBUTE9,
LINE_EXT_VARCHAR_ATTRIBUTE10,
LINE_EXT_VARCHAR_ATTRIBUTE11,
LINE_EXT_VARCHAR_ATTRIBUTE12,
LINE_EXT_VARCHAR_ATTRIBUTE13,
LINE_EXT_VARCHAR_ATTRIBUTE14,
LINE_EXT_VARCHAR_ATTRIBUTE15,
LINE_EXT_VARCHAR_ATTRIBUTE16,
LINE_EXT_VARCHAR_ATTRIBUTE17,
LINE_EXT_VARCHAR_ATTRIBUTE18,
LINE_EXT_VARCHAR_ATTRIBUTE19,
LINE_EXT_VARCHAR_ATTRIBUTE20,
LINE_EXT_VARCHAR_ATTRIBUTE21,
LINE_EXT_VARCHAR_ATTRIBUTE22,
LINE_EXT_VARCHAR_ATTRIBUTE23,
LINE_EXT_VARCHAR_ATTRIBUTE24,
LINE_EXT_NUMBER_ATTRIBUTE1,
LINE_EXT_NUMBER_ATTRIBUTE2,
LINE_EXT_NUMBER_ATTRIBUTE3,
LINE_EXT_NUMBER_ATTRIBUTE4,
LINE_EXT_NUMBER_ATTRIBUTE5,
LINE_EXT_NUMBER_ATTRIBUTE6,
LINE_EXT_DATE_ATTRIBUTE1,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
values
(
event_class_code_tab(j), --EVENT_CLASS_CODE
application_id_tab(j), --APPLICATION_ID
entity_code_tab(j), --ENTITY_CODE
trx_id_tab(j), --TRX_ID
trx_line_id_tab(j), --TRX_LINE_ID
trx_line_type_tab(j), --TRX_LEVEL_TYPE
tax_provider_id_tab(j), --PROVIDER_ID
tax_regime_code_tab(j), --TAX_REGIME_CODE
--arp_tax_type_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE1
arp_product_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE2
use_step_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE3
arp_state_exempt_reason_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE4
arp_county_exempt_reason_tab(j),--LINE_EXT_VARCHAR_ATTRIBUTE5
arp_city_exempt_reason_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE6
step_proc_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE7
arp_audit_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE8
arp_ship_to_add_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE9
arp_ship_from_add_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE10
arp_poa_add_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE11
arp_customer_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE12
arp_customer_name_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE13
arp_company_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE14
arp_division_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE15
arp_vnd_ctrl_exmpt_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE16
arp_use_nexpro_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE17
arp_service_ind_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE18
crit_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE19
arp_poo_add_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE20
calculation_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE21
state_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE22
county_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE23
city_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE24
arp_state_exempt_percent_tab(j),--LINE_EXT_NUMBER_ATTRIBUTE1
arp_county_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE2
arp_city_exempt_pct_tab(j) , --LINE_EXT_NUMBER_ATTRIBUTE3
sec_county_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE4
sec_city_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE5
arp_tax_sel_param_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE6
arp_transaction_date_tab(j), --LINE_EXT_DATE_ATTRIBUTE1
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
);
g_string :='Not able to insert in to ZX_PRVDR_LINE_EXTNS_GT ';
ELSIF p_api_name in ('UPDATE_DET_FACTORS_HDR','UPDATE_LINE_DET_FACTORS') THEN -- Line negation
IF p_event_class_code in ('INVOICE','DEBIT_MEMO') THEN
x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
ELSIF p_api_name in ('GLOBAL_DOCUMENT_UPDATE') THEN
x_view_name := 'TAX_LINES_CREATE_V_A';
SELECT NVL(TAX_EXMPT_SOURCE_TAX, TAX_ACCOUNT_SOURCE_TAX) --Bug 8724051
INTO l_tax_account_source_tax
FROM ZX_SCO_TAXES_B_V
WHERE tax_regime_code = p_tax_regime_code AND
tax = tax_identifier_tab(i) AND
( g_trx_date >= effective_from AND
(g_trx_date <= effective_to OR effective_to IS NULL));
to approprirately insert into the relevant nested tables */
BEGIN
SELECT TAX_EXEMPTION_ID,
NVL(EXEMPT_RATE_MODIFIER ,0) * 100,
EXEMPT_REASON_CODE,
EXEMPT_CERTIFICATE_NUMBER
INTO x_exempt_record.exemption_id,
x_exempt_record.percent_exempt,
x_exempt_record.exempt_reason_code,
x_exempt_record.exempt_certificate_number
FROM ZX_LINES
WHERE application_id = adj_doc_appl_id_tab(p_position)
AND entity_code = adj_doc_entity_code_tab(p_position)
AND event_class_code = adj_evnt_cls_code_tab(p_position)
AND trx_id = adjusted_doc_trx_id_tab(p_position)
AND trx_line_id = adj_doc_line_id_tab(p_position)
AND trx_level_type = adj_doc_trx_level_type_tab(p_position)
AND tax_regime_code = p_tax_regime_code
AND tax = tax_identifier_tab(i);
SELECT TAX_EXEMPTION_ID,
NVL(EXEMPT_RATE_MODIFIER ,0) * 100,
EXEMPT_REASON_CODE,
EXEMPT_CERTIFICATE_NUMBER
INTO x_exempt_record.exemption_id,
x_exempt_record.percent_exempt,
x_exempt_record.exempt_reason_code,
x_exempt_record.exempt_certificate_number
FROM ZX_LINES
WHERE application_id = adj_doc_appl_id_tab(p_position)
AND entity_code = adj_doc_entity_code_tab(p_position)
AND event_class_code = adj_evnt_cls_code_tab(p_position)
AND trx_id = adjusted_doc_trx_id_tab(p_position)
AND trx_line_id = adj_doc_line_id_tab(p_position)
AND trx_level_type = adj_doc_trx_level_type_tab(p_position)
AND tax_regime_code = p_tax_regime_code
AND tax = 'LOCATION';
SELECT rate_modifier
INTO x_exempt_record.percent_exempt
FROM ZX_EXEMPTIONS EXMP
WHERE tax_exemption_id = x_exempt_record.exemption_id;
into which the derived values are ultimately getting inserted .
*/
IF NOT exemptions_info_tab.EXISTS(NVL(x_exempt_record.exemption_id, -99)) THEN
IF x_exempt_record.exemption_id IS NOT NULL THEN
ARP_TAX_VIEW_TAXWARE.GET_EXEMPTIONS(
X_EXEMPT_RECORD.EXEMPTION_ID, --This is the input parameter for this call
exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct,
exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason,
exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no,
exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct,
exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,
exemptions_info_tab(x_exempt_record.exemption_id).county_cert_no,
exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct ,
exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason,
exemptions_info_tab(x_exempt_record.exemption_id).city_cert_no,
exemptions_info_tab(x_exempt_record.exemption_id).sec_county_exempt_percent,
exemptions_info_tab(x_exempt_record.exemption_id).sec_city_exempt_percent,
exemptions_info_tab(x_exempt_record.exemption_id).use_step,
exemptions_info_tab(x_exempt_record.exemption_id).Step_Proc_Flag,
exemptions_info_tab(x_exempt_record.exemption_id).Crit_Flag
);
| This is a start up procedure that deletes any existing data from the nested
| tables
|
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| CALLED FROM
|
|
| MODIFICATION HISTORY
| 08/13/2004 Arnab Sengupta Created.
|
+==========================================================================*/
PROCEDURE Initialize_Nested_Tables
IS
l_api_name CONSTANT VARCHAR2(80) := 'INITIALIZE_NESTED_TABLES';
internal_org_id_tab.DELETE;
application_id_tab.DELETE;
entity_code_tab.DELETE;
event_class_code_tab.DELETE;
trx_id_tab.DELETE;
tax_provider_id_tab.DELETE;
tax_regime_code_tab.DELETE;
trx_line_type_tab.DELETE;
trx_line_id_tab.DELETE;
product_id_tab.DELETE;
Product_Org_Id_tab.DELETE;
ship_to_tx_id_tab.DELETE;
ship_from_tx_id_tab.DELETE;
cert_num_tab.DELETE;
exmpt_rsn_code_tab.DELETE;
exemption_control_flag_tab.DELETE;
ship_to_site_tax_prof_tab.DELETE;
ship_to_loc_id_tab.DELETE;
exmpt_control_flg_tab.DELETE;
arp_tax_type_tab.DELETE;
arp_product_code_tab.DELETE;
use_step_tab.DELETE;
arp_audit_flag_tab.DELETE;
arp_ship_to_add_tab.DELETE;
arp_ship_from_add_tab.DELETE;
arp_poa_add_code_tab.DELETE;
arp_customer_code_tab.DELETE;
arp_customer_name_tab.DELETE;
arp_company_code_tab.DELETE;
arp_division_code_tab.DELETE;
arp_vnd_ctrl_exmpt_tab.DELETE;
arp_use_nexpro_tab.DELETE;
arp_service_ind_tab.DELETE;
arp_tax_sel_param_tab.DELETE;
arp_transaction_date_tab.DELETE;
ship_to_address_id_tab.DELETE;
ship_to_party_id_tab.DELETE;
arp_state_exempt_reason_tab.DELETE;
arp_county_exempt_reason_tab.DELETE;
arp_city_exempt_reason_tab.DELETE;
step_proc_flag_tab.DELETE;
arp_state_exempt_percent_tab.DELETE;
arp_county_exempt_pct_tab.DELETE;
arp_city_exempt_pct_tab.DELETE;
ship_to_site_use_tab.DELETE;
bill_to_site_use_tab.DELETE;
bill_to_site_tax_prof_tab.DELETE;
bill_to_party_tax_id_tab.DELETE;
bill_to_location_id_tab.DELETE;
trad_hq_site_tax_prof_id_tab.DELETE;
trad_hq_party_tax_prof_id_tab.DELETE;
bill_third_pty_acct_id_tab.DELETE;
line_level_action_tab.DELETE;
adjusted_doc_trx_id_tab.DELETE;
line_amount_tab.DELETE;
exemptions_info_tab.DELETE;
trx_type_id_tab.DELETE;
state_cert_no_tab.DELETE;
county_cert_no_tab.DELETE;
city_cert_no_tab.DELETE;
crit_flag_tab.DELETE;
sec_county_exempt_pct_tab.DELETE;
sec_city_exempt_pct_tab.DELETE;
adj_doc_appl_id_tab.DELETE;
adj_doc_entity_code_tab.DELETE;
adj_evnt_cls_code_tab.DELETE;
adj_doc_line_id_tab.DELETE;
adj_doc_trx_level_type_tab.DELETE;
ship_third_pty_site_tab.DELETE;
bill_third_pty_site_tab.DELETE;
SELECT org_id
, primary_salesrep_id
INTO g_org_id
, g_sales_repid
FROM ra_customer_trx_all
WHERE customer_trx_id = adjusted_doc_trx_id_tab(i);
SELECT org_id,salesrep_id
INTO g_org_id,g_sales_repid
FROM oe_order_lines_all
WHERE header_id = trx_id_tab(i)
AND line_id = trx_line_id_tab(i);
SELECT org_id
, primary_salesrep_id
INTO g_org_id
, g_sales_repid
FROM ra_customer_trx_all
WHERE customer_trx_id = trx_id_tab(i);
SELECT nvl(substrb(act.attribute15, 1, 1), 'Y')
INTO arp_audit_flag_tab(i)
FROM ar_receivables_trx act
WHERE act.receivables_trx_id IN
(SELECT adj.receivables_trx_id
FROM ar_adjustments adj
WHERE adj.adjustment_id = trx_id_tab(i))
AND act.org_id = internal_org_id_tab(i);
SELECT org_id
INTO g_org_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = trx_id_tab(i)
AND customer_trx_line_id = trx_line_id_tab(i);
SELECT segment1
INTO arp_product_code_tab(i)
FROM mtl_system_items
WHERE inventory_item_id = l_product_id
AND organization_id = l_master_org_id;
SELECT cust_site_uses.cust_acct_site_id
INTO ship_to_address_id_tab(i)
FROM HZ_CUST_SITE_USES_ALL cust_site_uses
WHERE cust_site_uses.site_use_id = ship_to_site_use_tab(i);
SELECT cust_acct_site_id
INTO ship_to_address_id_tab(i)
FROM HZ_CUST_SITE_USES_ALL
WHERE site_use_id = bill_to_site_use_tab(i);
SELECT decode(nvl(loc.sales_tax_inside_city_limits,'1'),'0','0','1') || loc.sales_tax_geocode
INTO arp_ship_to_add_tab(i)
FROM hz_locations loc
WHERE loc.location_id = nvl(ship_to_loc_id_tab(i), bill_to_location_id_tab(i));
SELECT state, substr(postal_code,1,5)
INTO l_state_code, l_postal_code
FROM HZ_LOCATIONS
WHERE location_id = NVL(ship_to_loc_id_tab(i), bill_to_location_id_tab(i));
SELECT SHIP_FROM_LOCATION_ID
INTO l_ship_from_location_id
FROM ZX_LINES_DET_FACTORS
WHERE APPLICATION_ID = application_id_tab(i)
AND ENTITY_CODE = entity_code_tab(i)
AND EVENT_CLASS_CODE = event_class_code_tab(i)
AND TRX_ID = trx_id_tab(i)
AND TRX_LINE_ID = trx_line_id_tab(i);
SELECT party_id
INTO l_ship_from_party_id
FROM ZX_PARTY_TAX_PROFILE
WHERE party_tax_profile_id = ship_from_tx_id_tab(i);
SELECT lc.loc_information13
INTO arp_ship_from_add_tab(i)
FROM hr_locations_all lc, hr_organization_units hr
WHERE hr.organization_id = l_ship_from_party_id
AND hr.location_id = lc.location_id;
SELECT lc.loc_information13
INTO arp_ship_from_add_tab(i)
FROM hr_locations_all lc
WHERE lc.location_id = l_ship_from_location_id;
SELECT zxprdopt.SALES_TAX_GEOCODE
INTO arp_poa_add_code_tab(i)
FROM ZX_PRODUCT_OPTIONS_ALL zxprdopt
WHERE zxprdopt.application_id = application_id_tab(i)
AND zxprdopt.org_id = internal_org_id_tab(i)
AND (zxprdopt.event_class_mapping_id IS NULL
OR zxprdopt.event_class_mapping_id = (SELECT EVENT_CLASS_MAPPING_ID
FROM ZX_EVNT_CLS_MAPPINGS
WHERE EVENT_CLASS_CODE = event_class_code_tab(i)
AND APPLICATION_ID = application_id_tab(i)
AND ENTITY_CODE = entity_code_tab(i)));
select sales_tax_geocode
into arp_poo_add_code_tab(i)
from ra_salesreps
where salesrep_id = g_sales_repid;
SELECT account_number
INTO arp_customer_code_tab(i)
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = bill_third_pty_acct_id_tab(i);
SELECT pty.party_number
INTO arp_customer_code_tab(i)
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
AND ptp.party_id = pty.party_id;
SELECT zpli.bill_to_party_number
INTO arp_customer_code_tab(i)
FROM ZX_PTNR_LOCATION_INFO_GT zpli
, ZX_EVNT_CLS_MAPPINGS zecm
WHERE zpli.EVENT_CLASS_MAPPING_ID = zecm.EVENT_CLASS_MAPPING_ID
AND zecm.EVENT_CLASS_CODE = event_class_code_tab(i)
AND zecm.APPLICATION_ID = application_id_tab(i)
AND zecm.ENTITY_CODE = entity_code_tab(i)
AND zpli.TRX_ID = trx_id_tab(i)
AND zpli.TRX_LINE_ID = trx_line_id_tab(i);
SELECT pty.party_number
INTO arp_customer_code_tab(i)
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
AND ptp.party_id = pty.party_id;
SELECT pty.party_name
INTO arp_customer_name_tab(i)
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
AND ptp.party_id = pty.party_id;
SELECT zpli.BILL_TO_PARTY_NAME
INTO arp_customer_name_tab(i)
FROM ZX_PTNR_LOCATION_INFO_GT zpli
, ZX_EVNT_CLS_MAPPINGS zecm
WHERE zpli.EVENT_CLASS_MAPPING_ID = zecm.EVENT_CLASS_MAPPING_ID
AND zecm.EVENT_CLASS_CODE = event_class_code_tab(i)
AND zecm.APPLICATION_ID = application_id_tab(i)
AND zecm.ENTITY_CODE = entity_code_tab(i)
AND zpli.TRX_ID = trx_id_tab(i)
AND zpli.TRX_LINE_ID = trx_line_id_tab(i);
SELECT pty.party_name
INTO arp_customer_name_tab(i)
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
AND ptp.party_id = pty.party_id;
SELECT trx_line_gl_date
INTO arp_transaction_date_tab(i)
FROM zx_lines_det_factors
WHERE internal_organization_id = internal_org_id_tab(i)
AND application_id = application_id_tab(i)
AND Entity_Code = entity_code_tab(i)
AND Event_Class_Code = event_class_code_tab(i)
AND trx_id = trx_id_tab(i)
AND trx_line_id = trx_line_id_tab(i);
select attribute1
into arp_vnd_ctrl_exmpt_tab(i)
from ra_cust_trx_types
where cust_trx_type_id = trx_type_id_tab(i)
and org_id = internal_org_id_tab(i);
select EVNT_CLS_MAPPING_ID,
TRX_ID,
TAX_REGIME_CODE
from ZX_TRX_PRE_PROC_OPTIONS_GT;