[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 , --Bug6393131
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.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,
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;
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_trx_line_type;
/*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_NUMBER_ATTRIBUTE1,
LINE_EXT_NUMBER_ATTRIBUTE2,
LINE_EXT_NUMBER_ATTRIBUTE3,
LINE_EXT_NUMBER_ATTRIBUTE4,
LINE_EXT_DATE_ATTRIBUTE1,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
values
(
event_class_code_tab(j),
application_id_tab(j),
entity_code_tab(j),
trx_id_tab(j),
trx_line_id_tab(j),
trx_line_type_tab(j),
tax_provider_id_tab(j),
tax_regime_code_tab(j),
arp_trx_line_type_tab(j),
arp_product_code_tab(j),
cert_num_tab(j),
arp_state_exempt_reason_tab(j),
arp_county_exempt_reason_tab(j),
arp_city_exempt_reason_tab(j),
arp_district_exempt_rs_tab(j),
arp_audit_flag_tab(j),
arp_ship_to_add_tab(j),
arp_ship_from_add_tab(j),
arp_poa_add_code_tab(j),
arp_customer_code_tab(j),
arp_customer_class_tab(j),
arp_company_code_tab(j),
arp_division_code_tab(j),
arp_state_exempt_percent_tab(j),
arp_county_exempt_pct_tab(j),
arp_city_exempt_pct_tab(j) ,
arp_district_exempt_pct_tab(j),
arp_transaction_date_tab(j),
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_V INVOICE';
x_view_name := 'TAX_LINES_DELETE_V_V';
ELSIF p_api_name in ('GLOBAL_DOCUMENT_UPDATE') THEN
x_view_name := 'TAX_LINES_CREATE_V_V';
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 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));
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 = l_tax_account_source_tax;
SELECT rate_modifier
INTO x_exempt_record.percent_exempt
FROM ZX_EXEMPTIONS EXMP
WHERE tax_exemption_id = x_exempt_record.exemption_id;
delete from zx_jurisdictions_gt;
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_VERTEX.GET_EXEMPTIONS(
X_EXEMPT_RECORD.EXEMPTION_ID, --This is the input parameter for this call
exemptions_info_tab(x_exempt_record.exemption_id).certificate_number,
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).county_exempt_pct,
exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,
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).district_exempt_pct,
exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_reason);
| 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_trx_line_type_tab.DELETE;
arp_product_code_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_class_tab.DELETE;
arp_company_code_tab.DELETE;
arp_division_code_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;
arp_district_exempt_rs_tab.DELETE;
arp_state_exempt_percent_tab.DELETE;
arp_county_exempt_pct_tab.DELETE;
arp_city_exempt_pct_tab.DELETE;
arp_district_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;
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 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 l_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
, decode(nvl(loc.sales_tax_inside_city_limits,'1'),'0','0','1')
INTO arp_ship_to_add_tab(i)
, l_in_out_flag -- Bug 5506031
FROM hz_locations loc
WHERE loc.location_id = nvl(ship_to_loc_id_tab(i), bill_to_location_id_tab(i));
SELECT count(*)
INTO l_jur_count
FROM zx_jurisdictions_gt a
WHERE a.tax_regime_code = tax_regime_code_tab(i)
AND a.tax = 'CITY'
AND substr(a.tax_jurisdiction_code, 4) BETWEEN '000000000' and '999999999'
AND a.precedence_level = (SELECT min(b.precedence_level)
FROM zx_jurisdictions_gt b
WHERE b.tax_regime_code = a.tax_regime_code
AND substr(b.tax_jurisdiction_code, 4) BETWEEN
'000000000' and '999999999'
AND b.tax = 'CITY');
SELECT substr(a.tax_jurisdiction_code, 4)
INTO l_jur_code
FROM zx_jurisdictions_gt a
WHERE a.tax_regime_code = tax_regime_code_tab(i)
AND a.tax = 'CITY'
AND substr(a.tax_jurisdiction_code, 4) BETWEEN '000000000' and '999999999'
AND a.precedence_level = (SELECT min(b.precedence_level)
FROM zx_jurisdictions_gt b
WHERE b.tax_regime_code = a.tax_regime_code
AND substr(b.tax_jurisdiction_code, 4)
BETWEEN '000000000' and '999999999'
AND b.tax = 'CITY');
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,lc.loc_information14
INTO l_sfr_geocode,l_sfr_in_out_flag
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,lc.loc_information14
INTO l_sfr_geocode,l_sfr_in_out_flag
FROM hr_locations_all lc
WHERE lc.location_id = l_ship_from_location_id;
SELECT lc.loc_information13,lc.loc_information14
INTO l_sfr_geocode,l_sfr_in_out_flag
FROM hr_locations_all lc
WHERE lc.location_id = l_ship_from_location_id;
SELECT inventory_item_id
, org_id
INTO l_inventory_item_id
, l_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 lc.loc_information13,lc.loc_information14
INTO l_sfr_geocode,l_sfr_in_out_flag
FROM hr_locations_all lc, hr_organization_units hr
WHERE hr.organization_id = l_master_org_id
AND hr.location_id = lc.location_id;
SELECT primary_salesrep_id
INTO l_sales_repid
FROM ra_customer_trx_all
WHERE customer_trx_id = adjusted_doc_trx_id_tab(i);
SELECT salesrep_id
INTO l_sales_repid
FROM oe_order_lines_all
WHERE header_id = trx_id_tab(i)
AND line_id = trx_line_id_tab(i);
SELECT primary_salesrep_id
INTO l_sales_repid
FROM ra_customer_trx_all
WHERE customer_trx_id = trx_id_tab(i);
SELECT sales_tax_geocode,sales_tax_inside_city_limits
INTO l_poa_geocode, l_poa_in_out_flag
FROM ra_salesreps
WHERE salesrep_id = l_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 party_id
INTO l_party_id
FROM zx_party_tax_profile
WHERE party_tax_profile_id = l_ptp_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 EVNT_CLS_MAPPING_ID,
TRX_ID,
TAX_REGIME_CODE
from ZX_TRX_PRE_PROC_OPTIONS_GT;