Monday, April 16, 2018
Web ADI Error - Setup for "Do not close this window until processing completes" In Firefox or Internet Explorer (IE)
Thursday, April 5, 2018
Queries to Get the SLA Details
--1) Fields
code_combination_id,Period_name,ledger_id,status
SELECT code_combination_id,
Period_name,
ledger_id,
status
FROM gl_je_lines
WHERE je_header_id = 127354 AND Je_line_num = 13;
-- 2) Fields Batch
ID,Reference(type:PO,AR,AP)
SELECT je_batch_id, reference_1
FROM gl_import_references
WHERE je_header_id = 127354 AND Je_line_num = 13;
--3) Fields
header_id,code_combination_id,ledger_id
SELECT ae_header_id,
code_combination_id,
ledger_id,
Accounting_class_code,
description
FROM xla_ae_lines xal
WHERE gl_sl_link_id IN
(SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id = 127354
AND Je_line_num = 13
AND ledger_id = 125);
--4) Fields
Source_distribution_type,Accounting_line_code,event_class_code,event_type_code,ae_line_num
SELECT Source_distribution_type,
Accounting_line_code,
event_class_code,
event_type_code,
ae_line_num
FROM xla_distribution_links
WHERE ae_header_id IN
(SELECT ae_header_id
FROM xla_ae_lines
WHERE gl_sl_link_id IN
(SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id = 127354 AND Je_line_num = 13));
Queries to Oracle ETAX Details
Following are the main
E-Business tax tables that will contain the transaction information that will
have the tax details after tax is calculated.
a. ZX_LINES: This table will
have the tax lines for associated with PO/Release schedules.
TRX_ID: Transaction ID. This
is linked to the
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction
Line ID. This is linked to the
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
b. ZX_REC_NREC_DIST: This
table will have the tax distributions for associated with PO/Release
distributions.
TRX_ID: Transaction ID. This
is linked to the
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction
Line ID. This is linked to the
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
TRX_LINE_DIST_ID:
Transaction Line Distribution ID. This is linked to the
PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
RECOVERABLE_FLAG:
Recoverable Flag. If the distribution is recoverable then the flag will be set
to Y and there will be values in the RECOVERY_TYPE_CODE and RECOVERY_RATE_CODE.
c. PO_REQ_DISTRIBUTIONS_ALL:
This table will have the tax distributions for associated with Requisition
distribution.
RECOVERABLE_TAX: Recoverable
tax amount
NONRECOVERABLE_TAX: Non
Recoverable tax amount
d. ZX_LINES_DET_FACTORS: This
table holds all the information of the tax line transaction for both the
requisitions as well as the purchase orders/releases.
TRX_ID: Transaction ID. This
is linked to the
PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID
/
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction
Line ID. This is linked to the
PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID
/
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
-- SQL FOR PARTY FISCAL
CLASSIFICATION CODE --
SELECT DISTINCT HP.PARTY_ID,
HP.PARTY_NAME,
HPS.PARTY_SITE_ID,
HPS.PARTY_SITE_NAME,
ZP.PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE ZP,
HZ_PARTY_SITES HPS,
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS_ALL CA
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND HP.PARTY_ID = CA.PARTY_ID
AND HPS.PARTY_SITE_ID = ZP.PARTY_ID
AND HP.PARTY_NAME LIKE '%Advantage%Corp%';
-- BELOW QUERY RETRIEVES CUSTOMER ADDRESSES THAT DOESNT HAVE
ANY GEOGRAPHY REFERENCE --
SELECT HCA.ACCOUNT_NUMBER,
HCA.ACCOUNT_NAME,
HCS_SHIP.SITE_USE_CODE,
HL_SHIP.ADDRESS1 ADDRESS,
HL_SHIP.STATE STATE,
HL_SHIP.COUNTY COUNTY,
HL_SHIP.CITY CITY,
HL_SHIP.POSTAL_CODE
FROM HZ_CUST_SITE_USES_ALL HCS_SHIP,
HZ_CUST_ACCT_SITES_ALL HCA_SHIP,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS_SHIP,
HZ_LOCATIONS HL_SHIP
WHERE HCA.CUST_ACCOUNT_ID = HCA_SHIP.CUST_ACCOUNT_ID(+)
AND HCS_SHIP.CUST_ACCT_SITE_ID(+) = HCA_SHIP.CUST_ACCT_SITE_ID
-- AND HCA.ACCOUNT_NUMBER='10001'
AND HCA_SHIP.PARTY_SITE_ID = HPS_SHIP.PARTY_SITE_ID
AND HPS_SHIP.LOCATION_ID = HL_SHIP.LOCATION_ID
AND HCA.STATUS = 'A'
AND HCS_SHIP.STATUS = 'A'
AND HCA_SHIP.STATUS = 'A'
AND HL_SHIP.COUNTRY = 'US'
AND NOT EXISTS
(SELECT 1
FROM HZ_GEOGRAPHIES HG
WHERE HG.GEOGRAPHY_ELEMENT2_CODE = HL_SHIP.STATE
AND UPPER (HL_SHIP.COUNTY) =
UPPER (HG.GEOGRAPHY_ELEMENT3_CODE)
AND UPPER (HL_SHIP.CITY) =
UPPER (HG.GEOGRAPHY_ELEMENT4_CODE)
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE);
-- BELOW SQL QUERY RETRIEVES
LIST OF JURISDICTIONS' FOR WHICH TAX RATES HAS BEEN DEFINED --
SELECT TAX,
TAX_JURISDICTION_CODE,
TAX_REGIME_CODE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM ZX_JURISDICTIONS_B ZJ, HZ_GEOGRAPHIES HG
WHERE 1 = 1 AND ZJ.TAX_REGIME_CODE = 'US-SALES-TAX-101'
AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM
AND NVL (ZJ.EFFECTIVE_TO, '31-DEC-4999')
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
AND ZJ.ZONE_GEOGRAPHY_ID = HG.GEOGRAPHY_ID
AND ZJ.TAX = HG.GEOGRAPHY_TYPE
AND NOT EXISTS
(SELECT 1
FROM ZX_RATES_B ZR
WHERE 1 = 1 AND ZR.TAX_REGIME_CODE = 'US-SALES-TAX-101'
AND ZR.TAX_JURISDICTION_CODE =
ZJ.TAX_JURISDICTION_CODE)
ORDER BY TAX,
TAX_JURISDICTION_CODE,
GEOGRAPHY_ELEMENT2_CODE,
GEOGRAPHY_ELEMENT3_CODE,
GEOGRAPHY_ELEMENT4_CODE;
-- BELOW QUERY RETRIEVES LIST
OF GEOGRAPHY'S WITHOUT JURISDICTIONS--
SELECT *
FROM (SELECT GEOGRAPHY_TYPE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM HZ_GEOGRAPHIES HG
WHERE HG.GEOGRAPHY_TYPE = 'STATE'
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
AND GEOGRAPHY_ELEMENT1_CODE = 'US'
AND NOT EXISTS
(SELECT 1
FROM ZX_JURISDICTIONS_B ZJ
WHERE ZJ.ZONE_GEOGRAPHY_ID = HG.GEOGRAPHY_ID
AND ZJ.TAX_REGIME_CODE = 'US-SALES-TAX-101'
AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM
AND NVL (ZJ.EFFECTIVE_TO,
'31-DEC-4999')
AND ZJ.TAX = HG.GEOGRAPHY_TYPE)
UNION
SELECT GEOGRAPHY_TYPE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM HZ_GEOGRAPHIES HG
WHERE HG.GEOGRAPHY_TYPE = 'COUNTY'
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
AND GEOGRAPHY_ELEMENT1_CODE = 'US'
AND NOT EXISTS
(SELECT 1
FROM ZX_JURISDICTIONS_B ZJ
WHERE ZJ.ZONE_GEOGRAPHY_ID = HG.GEOGRAPHY_ID
AND ZJ.TAX_REGIME_CODE = 'US-SALES-TAX-101'
AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM
AND NVL (ZJ.EFFECTIVE_TO,
'31-DEC-4999')
AND ZJ.TAX = HG.GEOGRAPHY_TYPE)
UNION
SELECT GEOGRAPHY_TYPE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM HZ_GEOGRAPHIES HG
WHERE HG.GEOGRAPHY_TYPE = 'CITY'
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
AND GEOGRAPHY_ELEMENT1_CODE = 'US'
AND NOT EXISTS
(SELECT 1
FROM ZX_JURISDICTIONS_B ZJ
WHERE ZJ.ZONE_GEOGRAPHY_ID = HG.GEOGRAPHY_ID
AND ZJ.TAX_REGIME_CODE = 'US-SALES-TAX-101'
AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM
AND NVL (ZJ.EFFECTIVE_TO,
'31-DEC-4999')
AND ZJ.TAX = HG.GEOGRAPHY_TYPE))
ORDER BY GEOGRAPHY_TYPE,
STATE_CODE,
COUNTY_CODE,
CITY_CODE
Subscribe to:
Posts (Atom)