Oracle EBS Purchase Order

Oracle EBS Purchase Order

Tables:-
PO_LINES_ALL
PO_HEADERS_ALL
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_ALL
ORG_ORGANIZATION_DEFINITIONS
HR_LOCATIONS
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
PER_ALL_PEOPLE_F
HR_OPERATING_UNITS
XLE_ENTITY_PROFILES
XLE_REGISTRATIONS

SQL Example:- PO_LINES

select distinct
hr.name "Operating Unit"
,ph.segment1 "Document Num"
,pol.line_num "Line Num"
,(SELECT line_type
 FROM  APPS.PO_LINE_TYPES
 WHERE line_type_id=pol.line_type_id) "Line Type"
,(SELECT segment1
 FROM APPS.MTL_SYSTEM_ITEMS_B
 WHERE inventory_item_id=pol.item_id and rownum=1) "Item"
,pol.item_description "Item Description"
,pol.unit_meas_lookup_code "Unit Of Measure"
,pol.quantity "Line Quantity"
,pol.unit_price "Unit Price"
,ood.organization_code "Ship To Organization Code"
,hl.location_code "Ship To Location"
,pll.need_by_date "Need By Date"
,pll.promised_date "Promise Date"
,pol.list_price_per_unit "List Price Per Unit"
,pol.note_to_vendor  "Note To Vendor"
,(SELECT routing_name FROM apps.rcv_routing_headers WHERE routing_header_id=pll.receiving_routing_id) "Receiving Routing"
,(select concatenated_segments from apps.gl_code_combinations_kfv a where a.code_combination_id=pda.code_combination_id) "Charge Account"
,(select concatenated_segments from apps.gl_code_combinations_kfv a where a.code_combination_id=pda.accrual_account_id) "PO Accural Account"
,(select concatenated_segments from apps.gl_code_combinations_kfv a where a.code_combination_id=pda.budget_account_id) "PO Budget Account"
,(select concatenated_segments from apps.gl_code_combinations_kfv a where a.code_combination_id=pda.variance_account_id) "PO Variance Account"
,pll.quantity "Ordered Quantity"
,pll.quantity_received  "Quantity Received"
,pll.quantity_billed  "Quantity Billed"
,pll.quantity_cancelled  "Quantity Cancelled"
,ph.rate  "RATE"
,DECODE(pol.closed_code,null,'OPEN',pol.closed_code) "Closure Status"
,ppf.full_name "Requestor"
,(select location_code from apps.hr_locations where location_id=pda.deliver_to_location_id) "Deliver To"
,pda.destination_subinventory  "Sub Inventory"
,hr.name "Requesition Org"
,pda.Accrued_flag "Accure At Receipt"
,(select mc.segment1 from MTL_CATEGORIES mc where category_id =pol.category_id ) "Category"
,pll.SHIPMENT_NUM SHIPMENT_NUM
,pda.DISTRIBUTION_NUM DISTRIBUTION_NUM
,pda.QUANTITY_ORDERED DISTRIBUTION_QUANTITY_ORDERED
,pda.quantity_delivered distri_quantity_delive
,pda.quantity_billed distri_quantity_billed
,pda.quantity_cancelled distri_quantity_cancelled
FROM
APPS.PO_LINES_ALL pol,
APPS.PO_HEADERS_ALL ph,
APPS.PO_LINE_LOCATIONS_ALL pll,
APPS.PO_DISTRIBUTIONS_ALL pda,
APPS.ORG_ORGANIZATION_DEFINITIONS ood,
APPS.HR_LOCATIONS hl,
APPS.AP_SUPPLIERS ap,
APPS.AP_SUPPLIER_SITES_ALL aps,
APPS.PER_ALL_PEOPLE_F ppf,
APPS.HR_OPERATING_UNITS hr,
APPS.XLE_ENTITY_PROFILES lep,
APPS.XLE_REGISTRATIONS reg
WHERE 1=1
AND pol.po_header_id=pll.po_header_id
 AND   ph.po_header_id=pol.po_header_id
 AND pol.po_line_id=pll.po_line_id
 AND pll.ship_to_organization_id=ood.organization_id
 AND pll.ship_to_location_id=hl.location_id
 AND pol.org_id=hr.organization_id
 AND ph.po_header_id=pda.po_header_id
 and pol.po_line_id=pda.po_line_id
 AND ph.org_id=pda.org_id
 AND ph.vendor_site_id=aps.vendor_site_id(+)
 AND ap.vendor_id =aps.vendor_id
  AND pda.deliver_to_person_id=ppf.person_id(+)
 AND (ph.authorization_status in('APPROVED','INCOMPLETE','REQUIRES REAPPROVAL','PRE-APPROVED','IN PROCESS','REJECTED') OR (ph.authorization_status is null))
 AND ((pol.closed_code  ='OPEN') OR (pol.closed_code is null))
 AND ((ph.closed_code  ='OPEN') OR (ph.closed_code is null))
  AND lep.legal_entity_id = reg.source_id
  AND hr.organization_id=ood.operating_unit
  AND reg.source_table = 'XLE_ENTITY_PROFILES'
  AND lep.legal_entity_id = hr.default_legal_context_id
  AND lep.legal_entity_id = ood.legal_entity;

Oracle EBS Purchase Order

Oracle EBS Purchase Order

Tables:-
PO_HEADERS_ALL
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
HR_LOCATIONS
AP_TERMS
PER_ALL_PEOPLE_F
HR_OPERATING_UNITS
PO_DISTRIBUTIONS_ALL
ORG_ORGANIZATION_DEFINITIONS
XLE_ENTITY_PROFILES
XLE_REGISTRATIONS

SQL Example:-

SELECT DISTINCT
hr.name OU_NAME
,DECODE(ph.type_lookup_code,'STANDARD','Standard Purchase Order','PLANNED','Planned Purchase Order','BLANKET','Blanket Purchase Agreement','CONTRACT','Contract Purchase Agreement') "Document Type Code"
,ph.segment1 "Document Num"
,ph.creation_date "Creation Date"
,ph.currency_code "Currency Code"
,papf.full_name "Buyer Name"
,ap.vendor_name "Vendor Name"
,ap.segment1 "Vendor Num"
,aps.vendor_site_code "Vendor Site Code"
,ph.comments "Description/Comments"
,(SELECT hl1.location_code
FROM apps.hr_locations hl1
WHERE hl1.location_id=ph.bill_to_location_id) "Bill To Location"
,hl.location_code "Ship To Location"
,atr.name "Payment Terms"
,ph.freight_terms_lookup_code "Freight Terms"
,DECODE(ph.authorization_status,null,'INCOMPLETE',ph.authorization_status) Status
,ph.approved_date "Approved Date"
,ph.fob_lookup_code Fob
,ph.note_to_vendor "Note To Vendor"
,(SELECT user_conversion_type FROM APPS.GL_DAILY_CONVERSION_TYPES WHERE conversion_type=ph.rate_type) "Rate Type"
,ph.rate_date "Rate Date"
,ph.ship_via_lookup_code "Freight Carrier"
,ph.note_to_receiver "Receiver Note"
,ap.vendor_type_lookup_code "Supplier Type"
,(SELECT name FROM APPS.PA_PROJECTS_ALL WHERE project_id=pd.project_id) "FA Project"
,(SELECT task_name
FROM APPS.PA_TASKS
WHERE task_id=pd.task_id) "FA Task"
from
APPS.PO_HEADERS_ALL ph,
APPS.AP_SUPPLIERS ap,
APPS.AP_SUPPLIER_SITES_ALL aps,
APPS.HR_LOCATIONS hl,
APPS.AP_TERMS atr,
APPS.PER_ALL_PEOPLE_F papf,
APPS.HR_OPERATING_UNITS hr,
APPS.PO_DISTRIBUTIONS_ALL pd,
APPS.ORG_ORGANIZATION_DEFINITIONS ood,
APPS.XLE_ENTITY_PROFILES lep,
APPS.XLE_REGISTRATIONS reg
WHERE 1=1
AND ph.vendor_site_id=aps.vendor_site_id(+)
AND ap.vendor_id =aps.vendor_id 
AND ph.org_id=hr.organization_id
AND hl.location_id(+)=ph.ship_to_location_id
AND papf.person_id=ph.agent_id
AND ph.terms_id=atr.term_id
AND ph.po_header_id=pd.po_header_id
AND ph.org_id=pd.org_id
AND (ph.authorization_status in('APPROVED','INCOMPLETE','REQUIRES REAPPROVAL','PRE-APPROVED','IN PROCESS','REJECTED') OR (ph.authorization_status is null))
AND ((pH.closed_code  ='OPEN') OR (ph.closed_code is null))
AND lep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND lep.legal_entity_id = hr.default_legal_context_id
AND pd.destination_organization_id=ood.organization_id
AND lep.legal_entity_id = ood.legal_entity
AND hr.organization_id=ood.operating_unit;

Oracle EBS AP Open Invoice

Oracle EBS AP Open Invoice

Tables:-
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_TERMS_TL
AP_PAYMENT_SCHEDULES_ALL

SQL Example:-
AP Open Invoice - Header

SELECT
g.name OPERATING_UNIT
,b.invoice_num INVOICE_NUM
,b.invoice_type_lookup_code INVOICE_TYPE
,b.invoice_date INVOICE_DATE
,b.gl_date GL_DATE
,e.vendor_name VENDOR_NAME
,f.vendor_site_code VENDOR_SITE_CODE
,h.name PAY_TERMS_NAME
,b.terms_date PAY_TERMS_DATE
,b.payment_method_code PAYMENT_METHOD
,b.invoice_amount INVOICE_AMOUNT
,b.invoice_currency_code INVOICE_CURRENCY_CODE
,b.exchange_rate EXCHANGE_RATE
,(SELECT gdct.user_conversion_type FROM APPS.gl_daily_conversion_types gdct
WHERE gdct.conversion_type=b.exchange_rate_type AND rownum=1) " EXCHANGE_RATE_TYPE"
,b.exchange_date EXCHANGE_DATE
,b.payment_currency_code PAYMENT_CURRENCY_CODE
,b.description DESCRIPTION
,a.concatenated_segments LIABILITY_ACCOUNT
,b.attribute_category ATTRIBUTE_CATEGORY
,(select SUM(AMOUNT) FROM APPS.AP_INVOICE_LINES_ALL ail WHERE ail.invoice_id=b.invoice_id
AND ail.line_type_lookup_code IN ('AWT')) "WHT_AMOUNT"
,b.DOCUMENT_SUB_TYPE INVOICE_SUB_TYPE
,APS.DUE_DATE "Invoice Due Date"
FROM
APPS.AP_INVOICES_ALL b
,apps.ap_invoice_lines_all    ail
,APPS.AP_SUPPLIERS e
,APPS.AP_SUPPLIER_SITES_ALL f
,APPS.HR_OPERATING_UNITS g
,APPS.AP_TERMS_TL h
,APPS.AP_PAYMENT_SCHEDULES_ALL aps
,APPS.GL_CODE_COMBINATIONS_KFV a
,APPS.xle_entity_profiles     xep
WHERE 1=1
AND a.code_combination_id = b.accts_pay_code_combination_id
 AND b.vendor_id = e.vendor_id
 AND b.org_id = g.organization_id
 AND e.vendor_id = f.vendor_id
 AND b.vendor_site_id(+) = f.vendor_site_id
 AND b.terms_id = h.term_id
 AND h.enabled_flag   ='Y'
 AND h.language  ='US'
 AND b.payment_status_flag in ('N','P')
AND APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(b.invoice_id, b.invoice_amount, b.payment_status_flag, b.invoice_type_lookup_code)!='CANCELLED'
 AND APPS.AP_INVOICES_PKG.GET_POSTING_STATUS(b.invoice_id)in ('Y','P','S')
AND b.INVOICE_ID=aps.INVOICE_ID(+)
AND ail.invoice_id =b.invoice_id(+)
AND g.default_legal_context_id = xep.legal_entity_id
AND b.legal_entity_id=xep.legal_entity_id
AND g.name='&&';

SQL Example:-
AP Open Invoice - Line

SELECT
a.name line_ou_name
,b.invoice_num line_invoice_num
,c.line_number
,c.line_type_lookup_code "Line Type"
,c.amount "Distribution Line Amount"
,d.concatenated_segments distribution_account
,c.description Description
,e.tax_status_code "TAX CLASSIFICATION CODE"
,e.tax_regime_code "TAX REGIME CODE"
,e.tax TAX
,e.tax_jurisdiction_code JURDICTION
,e.tax_status_code tax_status
,e.tax_rate_code "RATE NAME"
,e.tax_rate RATE
,b.attribute_category "Global Data Elements"
,c.tax_classification_code LINE_TAX_CODE
,g.vendor_name
,ail.PO_NUMBER "PO Number"
,ail.PO_RELEASE_NUMBER "PO Release"
,ail.PO_LINE_NUMBER "PO Line"
,ail.PO_SHIPMENT_NUMBER "PO Shipment"
,ail.MATCHING_BASIS "Match Basis"
,ail.PO_DISTRIBUTION_NUMBER "PO Distribution"
,ail.RECEIPT_NUMBER "Receipt Number"
,ail.RECEIPT_LINE_NUMBER "Receipt Line Number"
,ail.QUANTITY_INVOICED "Quantity Invoiced"
,ail.UNIT_MEAS_LOOKUP_CODE UOM
,ail.UNIT_PRICE "Unit Price"
FROM
APPS.HR_OPERATING_UNITS a
 ,APPS.AP_INVOICES_ALL b
 ,APPS.ap_invoice_lines_all c
 ,APPS.AP_INVOICE_LINES_V ail
 ,APPS.GL_CODE_COMBINATIONS_KFV d
 ,APPS.zx_lines_summary e
 ,APPS.AP_SUPPLIERS g
 ,APPS.GL_CODE_COMBINATIONS_KFV h
 WHERE 1=1
 AND b.invoice_id = c.invoice_id
 AND b.org_id = a.organization_id
 AND b.accts_pay_code_combination_id=d.code_combination_id(+)
 AND c.default_dist_ccid = d.code_combination_id(+)
 AND c.invoice_id =e.trx_id(+)
 AND b.vendor_id = g.vendor_id AND b.payment_status_flag in ('N','P')
 AND b.invoice_num=e.TRX_NUMBER(+)
 AND b.ORG_ID=e.INTERNAL_ORGANIZATION_ID(+)
 AND h.code_combination_id = b.accts_pay_code_combination_id
 AND APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(b.invoice_id, b.invoice_amount, b.payment_status_flag, b.invoice_type_lookup_code)!='CANCELLED'
 AND APPS.AP_INVOICES_PKG.GET_POSTING_STATUS(b.invoice_id)in ('Y','P','S')
 AND c.org_id=ail.org_id
 AND c.invoice_id=ail.invoice_id
 AND c.line_number=ail.line_number
 AND e.CANCEL_FLAG is null
AND a.name='&&';

Oracle EBS EBTAX-Document Classifications

Oracle EBS EBTAX-Document Classifications

Tables:-
ZX_FC_TYPES_VL
ZX_FC_CODES_DENORM_B

SQL Example:-

SELECT
a.CLASSIFICATION_TYPE_CODE FC_TYpe_Code
,a.CLASSIFICATION_TYPE_NAME FC_Type_Name
 ,a.CLASSIFICATION_TYPE_LEVEL_CODE Levels
 ,a.DELIMITER
 ,a.EFFECTIVE_FROM Type_EFF_FROM
 ,a.EFFECTIVE_TO Type_EFF_TO
 ,b.CLASSIFICATION_CODE FC_Code
 ,b.CLASSIFICATION_NAME FC_Name
 ,c.geography_name
 ,b.EFFECTIVE_FROM Code_EFF_FROM
 ,b.EFFECTIVE_TO Code_EFF_To
 FROM APPS.ZX_FC_TYPES_VL a ,
 APPS.ZX_FC_CODES_DENORM_B b,
 APPS.HZ_GEOGRAPHIES c,
 APPS.XLE_ENTITY_PROFILES d,
 APPS.HR_OPERATING_UNITS e
 WHERE 1=1 AND a.classification_type_code=a.classification_type_code
  and b.language='US'
  AND b.country_code = c.geography_code
 AND  c.geography_type   = 'COUNTRY'
 and c.geography_id =d.geography_id
 AND d.legal_entity_id=e.DEFAULT_LEGAL_CONTEXT_ID
 AND a.classification_type_code = 'DOCUMENT_SUBTYPE';

Oracle EBS EBTAX-Tax Statuses

Oracle EBS EBTAX-Tax Statuses

Tables:-
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS
ZX_REGIMES_USAGES
ZX_MCO_EO_STATUS_V
ZX_REGIMES_VL

SQL Example:-

SELECT
 (SELECT ter.territory_short_name
  FROM fnd_territories_vl ter
  WHERE tax_regim.country_code = ter.territory_code(+)
  ) "Country Name"
,tax_stat.tax_regime_code
,tax_stat.tax
,tax_stat.tax_status_code
,tax_stat.tax_status_name
,tax_stat.content_owner_name
,tax_stat.effective_FROM
,tax_stat.effective_to
,tax_stat.default_status_flag
,tax_stat.default_flg_effective_FROM
,tax_stat.default_flg_effective_to
,tax_stat.allow_exemptions_flag
,tax_stat.allow_rate_override_flag
FROM APPS.ZX_PARTY_TAX_PROFILE PTP,
  APPS.ZX_SUBSCRIPTION_DETAILS ZSD,
  APPS.HR_OPERATING_UNITS HOU,
   APPS.ZX_REGIMES_USAGES ZRU,
  APPS.ZX_MCO_EO_STATUS_V TAX_STAT,
  APPS.ZX_REGIMES_VL TAX_REGIM
          WHERE 1=1 AND tax_stat.tax_regime_code  =tax_regim.tax_regime_code
AND tax_regim.tax_regime_code   =zru.tax_regime_code
AND ptp.party_type_code         = 'OU'
AND ptp.party_id                = hou.organization_id
AND zru.first_pty_org_id        = ptp.party_tax_profile_id
AND zru.first_pty_org_id        = zsd.first_pty_org_id
AND zsd.tax_regime_code         = ZRU.TAX_REGIME_CODE
AND zsd.parent_first_pty_org_id = -99
AND SYSDATE BETWEEN zsd.effective_FROM AND NVL(zsd.effective_to,SYSDATE)
        AND hou.name= '&&';

Oracle EBS EBTAX-EBTAX Tax Rules

Oracle EBS EBTAX-EBTAX Tax Rules

Tables:-
ZX_RULES_VL
ZX_PROCESS_RESULTS
ZX_CONDITION_GROUPS_VL
ZX_DET_FACTOR_TEMPL_VL
ZX_CONDITIONS
ZX_DETERMINING_FACTORS_VL
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS
ZX_REGIMES_USAGES
ZX_REGIMES_VL
ZX_TAXES_VL

SQL Example:-

SELECT
DECODE(RuleTLEO.CONTENT_OWNER_ID,-99,'Global Configuration Owner',hou.name) "Configuration Owner"
,RuleTLEO.TAX_REGIME_CODE "Tax Regime Code"
,RuleTLEO.TAX "Tax"
,lkp.meaning  "Rule Type"
,RuleTLEO.DETERMINING_FACTOR_CQ_CODE  "Dflt Determine Tax Rate Rule"
,RuleTLEO.TAX_RULE_CODE "Rule Code"
,RuleTLEO.TAX_RULE_NAME "Rule Name"
,RuleTLEO.PRIORITY  "Rule Order"
,RuleTLEO.EFFECTIVE_FROM 
,RuleTLEO.EFFECTIVE_TO
,RuleTLEO.ENABLED_FLAG  "Enabled"
,b.TAX_STATUS_CODE  "Tax Status Code"
,(SELECT lkp2.MEANING
  FROM FND_LOOKUPS lkp2
  WHERE RuleTLEO.RECOVERY_TYPE_CODE = lkp2.LOOKUP_CODE
  AND lkp2.LOOKUP_TYPE              = 'ZX_RECOVERY_TYPES'
  AND SYSDATE BETWEEN NVL(lkp2.START_DATE_ACTIVE , SYSDATE) AND NVL(lkp2.END_DATE_ACTIVE, SYSDATE)
  AND lkp2.ENABLED_FLAG = 'Y'
  ) "Recovery Type"
  ,DECODE(RuleTLEO.TAX_EVENT_CLASS_CODE, NULL, DECODE(RuleTLEO.APPLICATION_ID,NULL,NULL,'EVENT_CLASS'),'TAX_EVENT_CLASS') "Event Class Category"
  ,DECODE(RuleTLEO.GEOGRAPHY_ID, NULL, 'No','Yes')  "Geography Specific Rule"
  ,RuleTLEO.DET_FACTOR_TEMPL_CODE "Determining Factor Set Code"
  ,d.DET_FACTOR_TEMPL_NAME  "Determining Factor Set Name"
  ,b.CONDITION_GROUP_CODE "Condition Set Code"
  ,c.CONDITION_GROUP_NAME "Name"
  ,b.priority "Order"
  ,DECODE(b.RESULT_TYPE_CODE, 'ADDRESS_TYPE',
  (SELECT MEANING
  FROM FND_LOOKUPS
  WHERE b.ALPHANUMERIC_RESULT = LOOKUP_CODE
  AND LOOKUP_TYPE             = 'ZX_PLACE_OF_SUPPLY_TYPE'
  AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE)
  AND NVL(ENABLED_FLAG, 'N') = 'Y'
  ), 'YES_NO',
  (SELECT MEANING
  FROM FND_LOOKUPS
  WHERE b.ALPHANUMERIC_RESULT = LOOKUP_CODE
  AND LOOKUP_TYPE             = 'ZX_YES_NO'
  AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE)
  AND NVL(ENABLED_FLAG, 'N') = 'Y'
  ), 'APPLICABILITY',
  (SELECT MEANING
  FROM FND_LOOKUPS
  WHERE b.ALPHANUMERIC_RESULT = LOOKUP_CODE
  AND LOOKUP_TYPE             = 'ZX_APPLICABILITY_TYPES'
  AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE)
  AND NVL(ENABLED_FLAG, 'N') = 'Y'
  ), b.ALPHANUMERIC_RESULT) "Result"
  ,b.enabled_flag "Condition Set Enable"
  ,(SELECT lkp4.meaning
  FROM FND_LOOKUPS lkp4
  WHERE lkp4.lookup_type ='ZX_DETERMINING_FACTOR_CLASS'
  AND lkp4.lookup_code   =e.DETERMINING_FACTOR_CLASS_CODE
  AND SYSDATE BETWEEN lkp4.START_DATE_ACTIVE AND NVL(lkp4.END_DATE_ACTIVE, SYSDATE)
  AND NVL(lkp4.ENABLED_FLAG, 'N') = 'Y'
  ) "Determining Factor Class"
  ,CASE
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'USER_DEFINED_GEOGRAPHY'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'GEOGRAPHY'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PLACE_OF_SUPPLY_TYPE', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PARTY_FISCAL_CLASS'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'PARTY_SITE_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_GEO_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PARTY'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'LEGAL_PARTY_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_LEGAL_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'REGISTRATION'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_REGISTRATION_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'ACCOUNTING_FLEXFIELD'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_ACCOUNT_FLEX_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PRODUCT_GENERIC_CLASSIFICATION'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'TRX_GENERIC_CLASSIFICATION'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'DOCUMENT'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'TRX_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PROD_TRX_CATEG', e.DETERMINING_FACTOR_CQ_CODE)
    ELSE NULL
  END "Class Qualifier"
  ,f.DETERMINING_FACTOR_NAME  "Determining Factor Name"
  ,e.OPERATOR_CODE  "Operator"
  ,e.VALUE_LOW  "From Range"
  ,e.VALUE_HIGH "To Range"
  ,e.IGNORE_FLAG  "Ignore Condition"
  ,CASE WHEN RuleTLEO.service_type_code='DET_APPLICABLE_TAXES'
THEN DECODE(TaxTLEO.APPLICABLE_BY_DEFAULT_FLAG, 'Y', 'Applicable','Not Applicable')
WHEN RuleTLEO.service_type_code='CALCULATE_TAX_AMOUNTS'
THEN TaxTLEO.DEF_TAX_CALC_FORMULA
WHEN RuleTLEO.service_type_code='DET_PLACE_OF_SUPPLY'
THEN
(SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'ZX_PLACE_OF_SUPPLY_TYPE' AND LOOKUP_CODE =TaxTLEO.DEF_PLACE_OF_SUPPLY_TYPE_CODE)
WHEN RuleTLEO.service_type_code='DET_TAX_RATE'
THEN
(SELECT distinct TAX_RATE_CODE FROM ZX_MCO_LV_RATES_V R
 WHERE R.TAX_REGIME_CODE =RuleTLEO.TAX_REGIME_CODE AND R.TAX = RuleTLEO.TAX AND R.CONTENT_OWNER_ID =RuleTLEO.CONTENT_OWNER_ID AND Nvl(R.active_flag,'N') = 'Y'
 AND NVL(R.DEFAULT_RATE_FLAG,'N') = 'Y'
 AND R.TAX_STATUS_CODE =(SELECT distinct st.TAX_STATUS_CODE FROM ZX_MCO_LV_STATUS_V st  where st.TAX_REGIME_CODE =RuleTLEO.TAX_REGIME_CODE AND st.TAX = RuleTLEO.TAX and st.DEFAULT_STATUS_FLAG = 'Y' and st.CONTENT_OWNER_ID=RuleTLEO.CONTENT_OWNER_ID)
 )
WHEN RuleTLEO.service_type_code='DET_TAX_REGISTRATION'
THEN
(SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'ZX_REGISTRATION_CQ' AND LOOKUP_CODE =TaxTLEO.DEF_REGISTR_PARTY_TYPE_CODE)
WHEN RuleTLEO.service_type_code='DET_TAX_STATUS'
THEN
(SELECT distinct st.TAX_STATUS_CODE FROM ZX_MCO_LV_STATUS_V st  where st.TAX_REGIME_CODE =RuleTLEO.TAX_REGIME_CODE AND st.TAX = RuleTLEO.TAX and st.DEFAULT_STATUS_FLAG = 'Y' and st.CONTENT_OWNER_ID=RuleTLEO.CONTENT_OWNER_ID)
WHEN RuleTLEO.service_type_code='DET_TAXABLE_BASIS'
THEN TaxTLEO.DEF_TAXABLE_BASIS_FORMULA
END default_val
FROM APPS.ZX_RULES_VL RuleTLEO,
  APPS.ZX_PROCESS_RESULTS b,
  APPS.ZX_CONDITION_GROUPS_VL c,
  APPS.ZX_DET_FACTOR_TEMPL_VL d,
  APPS.ZX_CONDITIONS e,
  APPS.ZX_DETERMINING_FACTORS_VL f,
  APPS.ZX_PARTY_TAX_PROFILE PTP,
  APPS.ZX_SUBSCRIPTION_DETAILS ZSD,
  APPS.HR_OPERATING_UNITS HOU,
  APPS.ZX_REGIMES_USAGES ZRU,
  APPS.ZX_REGIMES_VL RegimeTLEO,
  APPS.ZX_TAXES_VL TaxTLEO,
  APPS.FND_LOOKUPS lkp WHERE 1=1 AND RuleTLEO.TAX_RULE_ID            = b.TAX_RULE_ID
AND b.CONDITION_GROUP_ID            = c.CONDITION_GROUP_ID
AND c.CONDITION_GROUP_CODE          = e.CONDITION_GROUP_CODE
AND RuleTLEO.DET_FACTOR_TEMPL_CODE  = d.DET_FACTOR_TEMPL_CODE
AND e.DETERMINING_FACTOR_CLASS_CODE = f.DETERMINING_FACTOR_CLASS_CODE
AND e.DETERMINING_FACTOR_CODE       = f.DETERMINING_FACTOR_CODE
AND RuleTLEO. TAX_REGIME_CODE       = RegimeTLEO. TAX_REGIME_CODE
AND RuleTLEO. TAX                   = TaxTLEO.TAX
AND RuleTLEO.TAX_REGIME_CODE        = TaxTLEO.TAX_REGIME_CODE
AND zru.tax_regime_id               = RegimeTLEO.tax_regime_id
AND RegimeTLEO.tax_regime_code      = TaxTLEO.tax_regime_code
AND ptp.party_type_code             = 'OU'
AND ptp.party_id                    = hou.organization_id
AND zru.first_pty_org_id            = ptp.party_tax_profile_id
AND (RuleTLEO.CONTENT_OWNER_id=ptp.party_tax_profile_id OR RuleTLEO.CONTENT_OWNER_id=-99)
AND zru.first_pty_org_id            = zsd.first_pty_org_id
AND zsd.tax_regime_code             = ZRU.TAX_REGIME_CODE
AND zsd.parent_first_pty_org_id     = -99
AND SYSDATE BETWEEN zsd.effective_FROM AND NVL(zsd.effective_to,SYSDATE)
AND RuleTLEO.service_type_code=lkp.LOOKUP_CODE
 AND  lkp.LOOKUP_TYPE    = 'ZX_SERVICE_TYPE_CODES'
AND lkp.LOOKUP_CODE NOT IN ('DET_TAX_REGIMES', 'TAX_LINES_DETERMINATION','DET_APPLICABLE_REGIMES','DET_OFFSET_TAX','DET_TAX_LINES','ROUNDING','DET_APPLICABLE_RECOVERY_TYPES', 'EXCEPTION', 'EXEMPTIONS')
AND SYSDATE BETWEEN lkp.START_DATE_ACTIVE AND NVL(lkp.END_DATE_ACTIVE, SYSDATE)
AND NVL(lkp.ENABLED_FLAG, 'N') = 'Y'
  AND hou.name= '&&';

Oracle EBS EBTAX-EBTAX Tax Rates

Oracle EBS EBTAX-EBTAX Tax Rates

Tables:-
ZX_RATES_VL
ZX_ACCOUNTS
ZX_RATES_B
AP_ZX_RATES_B

SQL Example:-

SELECT
 ratetleo.tax_regime_code
 ,ratetleo.tax
 ,ratetleo.tax_status_code
,ratetleo.tax_rate_name
,ratetleo.tax_rate_code
,ratetleo.tax_jurisdiction_code
,(SELECT DISTINCT a.content_owner_name
  FROM APPS.ZX_MCO_EO_RATES_V a
  WHERE a.tax_rate_id=ratetleo.tax_rate_id
  ) "Configuration Owner"
  ,ratetleo.rate_type_code
  ,ratetleo.percentage_rate
  ,ratetleo.effective_FROM
  ,ratetleo.effective_to
  ,ratetleo.active_flag
  ,ratetleo.vat_transaction_type_code "Tax Transaction Type"
  ,ratetleo.description "Tax Rate Description"
  ,ratetleo.default_rec_rate_code "Default Recovery Rate Code"
  ,ratetleo.recovery_type_code 
  ,ratetleo.recovery_rule_code
  ,ratetleo.def_rec_settlement_option_code
  ,ratetleo.offset_tax_rate_code
  ,ratetleo.offset_tax
  ,ratetleo.offset_status_code
  ,ratetleo.default_rate_flag
  ,ratetleo.default_flg_effective_FROM
  ,ratetleo.default_flg_effective_to
  ,ratetleo.inclusive_tax_flag
  ,ratetleo.tax_inclusive_override_flag
  ,ratetleo.allow_exemptions_flag
  ,ratetleo.allow_exceptions_flag
  ,ratetleo.allow_adhoc_tax_rate_flag
  ,ratetleo.adj_for_adhoc_amt_code
  ,ratetleo.taxable_basis_formula_code
  ,ratetleo.attribute1  "Tax Exempt"
  ,ratetleo.attribute9  "Auto Invoice-Duplicate Rates"
  ,ratetleo.attribute11 "Item Model"
  ,ratetleo.attribute10 "Tax Denomination"
  ,ap_ext.ATTRIBUTE1 TAX_CLASS
  ,ap_ext.ATTRIBUTE2 XCHANGE_PAY_WTH
  ,ap_ext.ATTRIBUTE3 TXN_TYPE
  ,ar_ext.ATTRIBUTE1 Tax_Class
  ,ar_ext.ATTRIBUTE2 Transaction_Type
  ,ar_ext.ATTRIBUTE3 Perception_Rate
  ,ar_ext.ATTRIBUTE4 IGV_Type
  ,ar_ext.ATTRIBUTE5 ISC_Type
  ,ar_ext.ATTRIBUTE6 Other_Concepts
  ,ar_ext.ATTRIBUTE7 Value_Type
  ,ar_ext.ATTRIBUTE8 Perception_Regimen
  FROM
  APPS.ZX_RATES_VL ratetleo,
  APPS.ZX_ACCOUNTS B,
  APPS.HR_OPERATING_UNITS HOU,
   APPS.ZX_RATES_B ar_ext,
  APPS.AP_ZX_RATES_B ap_ext
  WHERE 1=1 AND b.internal_organization_id = hou.organization_id
AND b.tax_account_entity_code  = 'RATES'
AND b.tax_account_entity_id    = ratetleo.tax_rate_id
AND ratetleo.active_flag = 'Y'
AND SYSDATE BETWEEN ratetleo.effective_FROM AND NVL(ratetleo.effective_to, SYSDATE)
AND RateTLEO.tax_rate_id=ap_ext.tax_rate_id(+)
AND RateTLEO.tax_rate_id=ar_ext.tax_rate_id(+) ; 

Oracle EBS EBTAX-Party Tax Profiles

Oracle EBS EBTAX-Party Tax Profiles

Tables:-
ZX_REGIMES_USAGES
ZX_SUBSCRIPTION_OPTIONS
ZX_FIRST_PARTY_ORGS_ALL_V
ZX_PARTY_TAX_PROFILE
ZX_REGIMES_VL
FND_TERRITORIES_VL

SQL Example:-

SELECT
xle.name  "Legal Entity"
,hou.name "Operating Unit"
,(SELECT lkp.meaning
  FROM APPS.fnd_lookups lkp
  WHERE lkp.lookup_type = 'ZX_PTP_PARTY_TYPE'
  AND lkp.lookup_code   = pty.party_type_code
  AND SYSDATE BETWEEN lkp.start_date_active AND NVL(lkp.end_date_active, SYSDATE)
  AND NVL(lkp.enabled_flag, 'N') = 'Y'
  ) "Party Type"
          ,pty.party_name
          ,pty.address_line_1
  || ' '
  || pty.address_line_2
  || ' '
  || pty.address_line_3
  || ' '
  || pty.town_or_city
  || ' '
  || pty.region_1
  || ' '
  || pty. region_2
  || ' '
  || pty.postal_code  "Address"
        ,ter.territory_short_name "Country Name"
        ,a.tax_regime_code
        ,a.tax_regime_name
        ,(SELECT lkp1.meaning
  FROM APPS.FND_LOOKUPS lkp1
  WHERE lkp1.lookup_type = 'ZX_SUBSCRIPTION_OPTION'
  AND lkp1.lookup_code   = ZSO.SUBSCRIPTION_OPTION_CODE
  AND SYSDATE BETWEEN lkp1.start_date_active AND NVL(lkp1.end_date_active, SYSDATE)
  AND NVL(lkp1.enabled_flag, 'N') = 'Y'
  ) "Config for Product Exceptions"
        ,(SELECT lkp1.meaning
  FROM APPS.FND_LOOKUPS lkp1
  WHERE lkp1.lookup_type = 'ZX_SUBSCRIPTION_OPTION'
  AND lkp1.lookup_code   = ZSO.EXCEPTION_OPTION_CODE
  AND SYSDATE BETWEEN lkp1.start_date_active AND NVL(lkp1.end_date_active, SYSDATE)
  AND NVL(lkp1.ENABLED_FLAG, 'N') = 'Y'
  ) "Config for Taxes Rules"
        ,a.effective_FROM "Effective From"
        ,a.effective_to "Effective To"
        ,ZSO.effective_FROM "Party Eff From"
        ,ZSO.effective_to "Party Eff To"
        FROM APPS.ZX_REGIMES_USAGES REGM_USG,
  APPS.ZX_SUBSCRIPTION_OPTIONS ZSO,
  APPS.ZX_FIRST_PARTY_ORGS_ALL_V PTY,
  APPS.ZX_PARTY_TAX_PROFILE PTP,
  APPS.ZX_REGIMES_VL A,
  APPS.FND_TERRITORIES_VL TER,
  APPS.HR_OPERATING_UNITS HOU,
  APPS.XLE_ENTITY_PROFILES xle
  WHERE 1=1 AND ZSO.regime_usage_id       = regm_usg.regime_usage_id
AND regm_usg.first_pty_org_id = pty.party_tax_profile_id
AND regm_usg.first_pty_org_id = ptp.party_tax_profile_id
AND pty.party_type_code       =ptp.party_type_code
AND pty.party_tax_profile_id  =ptp.party_tax_profile_id
AND regm_usg.tax_regime_id    = a.tax_regime_id
AND a.country_code            = ter.territory_code(+)
AND ptp.party_id              = hou.organization_id
AND ptp.party_type_code       = 'OU'
AND xle.legal_entity_id       =hou.default_legal_context_id;

Oracle EBS EBTAX-EBTAX TAX REGIME

Oracle EBS EBTAX-EBTAX TAX REGIME

Tables:-
ZX_REGIMES_USAGES
ZX_SUBSCRIPTION_OPTIONS
ZX_FIRST_PARTY_ORGS_ALL_V
ZX_PARTY_TAX_PROFILE
ZX_REGIMES_VL
FND_TERRITORIES_VL
HR_OPERATING_UNITS
HZ_PARTIES
HZ_PARTIES
ZX_PARTY_TAX_PROFILE
ZX_PARTY_TAX_PROFILE
GL_DAILY_CONVERSION_TYPES

SQL Example:-

SELECT
hou.name  "Operating Unit"
,(SELECT A.name FROM APPS.XLE_ENTITY_PROFILES A
WHERE a.legal_entity_id=hou.default_legal_context_id) "Legal Entity"
,a.tax_regime_code
,a.tax_regime_name
,a.country_or_group_code
,a.parent_regime_code
,ter.territory_short_name
,a.effective_FROM
,a.effective_to
,a.allow_recoverability_flag
,a.allow_exemptions_flag
,a.tax_inclusive_override_flag
,a.allow_exceptions_flag
,a.allow_rounding_override_flag
,a.tax_currency_code
,c.user_conversion_type
,a.minimum_accountable_unit
,a.rounding_rule_code
,a.tax_precision
,a.def_inclusive_tax_flag
,a.exchange_rate_type
,a.has_other_jurisdictions_flag
,a.def_rec_settlement_option_code
,a.use_legal_msg_flag
,a.tax_account_precedence_code
,a.regn_num_same_as_le_flag
,a.cross_regime_compounding_flag
,a.regime_precedence
,a.has_sub_regime_flag
,a.country_code
,hz_p1.party_name "Collecting Auth"
,hz_p2.party_name "Reporting Auth"
,pty.party_name "Party Name"
,(SELECT lkp.meaning
  FROM FND_LOOKUPS LKP
  WHERE lkp.lookup_type = 'ZX_PTP_PARTY_TYPE'
  AND lkp.lookup_code   = pty.party_type_code
  AND SYSDATE BETWEEN lkp.start_date_active AND NVL(lkp.end_date_active, SYSDATE)
  AND NVL(lkp.enabled_flag, 'N') = 'Y'
  ) "Party Type"
          ,pty.address_line_1
  || ' '
  || pty.address_line_2
  || ' '
  || pty.address_line_3
  || ' '
  || pty.town_or_city
  || ' '
  || pty.region_1
  || ' '
  || pty. region_2
  || ' '
  || pty.postal_code  "Address"
        ,ter.territory_short_name "Party Country"
        ,zso.subscription_option_code "Config for Product Exception"
        ,zso.exception_option_code  "Config for Taxes Rules"
        ,zso.effective_FROM "Party Effective From"
        ,zso.effective_to "Party Effective To"
        ,zso.enabled_flag   "Enabled"
        FROM  APPS.ZX_REGIMES_USAGES regm_usg,
  APPS.ZX_SUBSCRIPTION_OPTIONS ZSO,
  APPS.ZX_FIRST_PARTY_ORGS_ALL_V pty,
  APPS.ZX_PARTY_TAX_PROFILE ptp,
  APPS.ZX_REGIMES_VL a,
  APPS.FND_TERRITORIES_VL ter,
  APPS.HR_OPERATING_UNITS hou,
  APPS.HZ_PARTIES hz_P1,
  APPS.HZ_PARTIES hz_P2,
  APPS.ZX_PARTY_TAX_PROFILE ZPTP1,
  APPS.ZX_PARTY_TAX_PROFILE ZPTP2,
  APPS.GL_DAILY_CONVERSION_TYPES c
        WHERE 1=1 AND ZSO.regime_usage_id     = regm_usg.regime_usage_id
AND regm_usg.first_pty_org_id = pty.party_tax_profile_id
AND regm_usg.first_pty_org_id = ptp.party_tax_profile_id
AND regm_usg.tax_regime_id    = a.tax_regime_id
AND a.country_code          = ter.territory_code(+)
AND a.rep_tax_authority_id  = zptp2.party_tax_profile_id(+)
AND zptp2.party_id          = hz_P2.party_id (+)
AND a.coll_tax_authority_id = zptp1.party_tax_profile_id(+)
AND zptp1.party_id          = hz_p1.party_id (+)
AND c.conversion_type (+) = a.exchange_rate_type
AND ptp.party_id          = hou.organization_id(+);

Oracle EBS EBTAX-Tax Rates Accounts

Oracle EBS EBTAX-Tax Rates Accounts

Tables:-
ZX_RATES_VL
ZX_ACCOUNTS
GL_LEDGERS
GL_CODE_COMBINATIONS_KFV

SQL Example:-

SELECT
hou.name  "Operationg Unit"
,zxr.tax_regime_code
,zxr.tax
,zxr.tax_status_code
,zxr.tax_rate_code
,zxr.tax_jurisdiction_code
,zxr.rate_type_code
,zxr.percentage_rate
,zxr.effective_FROM
,zxr.effective_to
,zxr.active_flag
,led.name "Ledger Name"
,gcc.concatenated_segments  "Liability Account"
FROM APPS.ZX_RATES_VL ZXR,
  APPS.ZX_ACCOUNTS B,
  APPS.HR_OPERATING_UNITS HOU,
APPS.GL_LEDGERS LED,
  APPS.GL_CODE_COMBINATIONS_KFV GCC WHERE 1=1 AND b.internal_organization_id = hou.organization_id
AND led.ledger_id                = hou.set_of_books_id
AND b.tax_account_entity_code    = 'RATES'
AND b.tax_account_entity_id      = zxr.tax_rate_id
AND gcc.code_combination_id      = b.tax_account_ccid
AND zxr.active_flag  = 'Y'
AND SYSDATE BETWEEN zxr.effective_FROM AND NVL(zxr.effective_to, SYSDATE); 

Oracle EBS EBTAX-Tax Jurisdictions

Oracle EBS EBTAX-Tax Jurisdictions

Tables:-
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS
ZX_REGIMES_USAGES
ZX_REGIMES_VL
ZX_MCO_EO_TAXES_V
ZX_JURISDICTIONS_VL

SQL Example:-

SELECT
hou.name
,jurisdictiontleo.tax_jurisdiction_code
 ,jurisdictiontleo.tax_jurisdiction_name
 ,jurisdictiontleo.tax_regime_code
 ,jurisdictiontleo.tax
 ,taxtleo.parent_geography_type
 ,(SELECT hg.geography_name
  FROM HZ_GEOGRAPHIES hg
  WHERE taxtleo.parent_geography_id = hg.geography_id(+)
  ) "Parent Geography Name"
,taxtleo.zone_geography_type
,jurisdictiontleo.effective_FROM
,jurisdictiontleo.effective_to
,jurisdictiontleo.inner_city_jurisdiction_flag
,(SELECT hg1.geography_name
  FROM HZ_GEOGRAPHIES hg1
  WHERE jurisdictiontleo.zone_geography_id = hg1.geography_id(+)
  ) "Geography Name"
,JurisdictionTLEO.PRECEDENCE_LEVEL
,(SELECT DISTINCT collauth.party_name
  FROM HZ_PARTIES collauth,
ZX_PARTY_TAX_PROFILE collptp
  WHERE collptp.party_id            = collauth.party_id(+)
  AND taxtleo.coll_tax_authority_id = collptp.party_tax_profile_id(+)
  ) "Collecting Tax Authority"
,(SELECT DISTINCT repauth.party_name
  FROM HZ_PARTIES repauth,
ZX_PARTY_TAX_PROFILE repptp
  WHERE repptp.party_id            = repauth.party_id(+)
  AND taxtleo.rep_tax_authority_id = repptp.party_tax_profile_id(+)
  ) "Reporting Tax Authority"
,jurisdictiontleo.default_jurisdiction_flag
,jurisdictiontleo.default_flg_effective_FROM
,jurisdictiontleo.default_flg_effective_to
FROM
APPS.ZX_PARTY_TAX_PROFILE PTP,
 APPS.ZX_SUBSCRIPTION_DETAILS ZSD,
APPS.HR_OPERATING_UNITS HOU,
 APPS.ZX_REGIMES_USAGES ZRU,
 APPS.ZX_REGIMES_VL TAX_REGIM,
 APPS.ZX_MCO_EO_TAXES_V TAXTLEO,
 APPS.ZX_JURISDICTIONS_VL JURISDICTIONTLEO
WHERE 1=1 AND zru.tax_regime_code       =tax_regim.tax_regime_code
AND tax_regim.tax_regime_code   =taxtleo.tax_regime_code
AND taxtleo.tax                 = jurisdictiontleo.tax
AND taxtleo.tax_regime_code     = jurisdictiontleo.tax_regime_code
AND tax_regim.tax_regime_code   = jurisdictiontleo.tax_regime_code
AND ptp.party_type_code         = 'OU'
AND ptp.party_id                = hou.organization_id
AND zru.first_pty_org_id        = ptp.party_tax_profile_id
AND zru.first_pty_org_id        = zsd.first_pty_org_id
AND zsd.tax_regime_code         = zru.tax_regime_code
AND zsd.parent_first_pty_org_id = -99
AND SYSDATE BETWEEN zsd.effective_FROM AND NVL(zsd.effective_to,SYSDATE) ;

Oracle EBS EBTAX-TAXES

Oracle EBS EBTAX-TAXES

Tables:-
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS
ZX_REGIMES_USAGES
ZX_MCO_EO_TAXES_V
ZX_REPORT_CODES_ASSOC
Zx_Reporting_Types_VL
Zx_Reporting_Codes_VL

SQL Example:-

SELECT
taxtleo.tax_regime_code
,taxtleo.content_owner_name
,taxtleo.tax,taxtleo.tax_full_name
,taxtleo.tax_type_code
,taxtleo.effective_FROM
,taxtleo.effective_to
,taxtleo.live_for_processing_flag
,taxtleo.live_for_applicability_flag
,taxtleo.zone_geography_type
,taxtleo.parent_geography_type
,(SELECT hg.geography_name
  FROM HZ_GEOGRAPHIES hg
  WHERE taxtleo.parent_geography_id = hg.geography_id(+)
  ) "Parent Geography Name"
,taxtleo.override_geography_type
,taxtleo.tax_currency_code
,taxtleo.reporting_only_flag
,taxtleo.minimum_accountable_unit
,taxtleo.rounding_rule_code
,taxtleo.tax_precision
,(SELECT gdct.user_conversion_type
  FROM GL_DAILY_CONVERSION_TYPES gdct
  WHERE gdct.conversion_type(+) = taxtleo.exchange_rate_type
  ) "Exchange Rate"
,taxtleo.compounding_precedence
,(SELECT DISTINCT repauth.party_name
  FROM HZ_PARTIES repauth,
ZX_PARTY_TAX_PROFILE repptp
  WHERE repptp.party_id            = repauth.party_id(+)
  AND taxtleo.rep_tax_authority_id = repptp.party_tax_profile_id(+)
  ) "Reporting Auth"
,(SELECT DISTINCT collauth.party_name
  FROM HZ_PARTIES collauth,
ZX_PARTY_TAX_PROFILE collptp
  WHERE collptp.party_id            = collauth.PARTY_ID(+)
  AND taxtleo.coll_tax_authority_id = collptp.party_tax_profile_id(+)
  ) "Collecting Auth"
,DECODE(taxtleo.applied_amt_hANDling_flag,'P','Prorated','R','Recalculated')  "Applied Amount Handling"
,taxtleo.offset_tax_flag
,taxtleo.TAX_INCLUSIVE_OVERRIDE_FLAG
,taxtleo.ALLOW_ROUNDING_OVERRIDE_FLAG
,taxtleo.ALLOW_TAX_OVERRIDE_FLAG
,taxtleo.ALLOW_MANUAL_ENTRY_FLAG
,taxtleo.REGN_NUM_SAME_AS_LE_FLAG
,taxtleo.ALLOW_DUP_REGN_NUM_FLAG
,taxtleo.HAS_OTHER_JURISDICTIONS_FLAG
,taxtleo.ALLOW_MASS_CREATE_FLAG
,taxtleo.TAX_ACCOUNT_CREATE_METHOD_CODE
,taxtleo.TAX_ACCOUNT_SOURCE_TAX
,taxtleo.ALLOW_EXCEPTIONS_FLAG
,taxtleo.ALLOW_EXEMPTIONS_FLAG
,taxtleo.TAX_EXMPT_SOURCE_TAX
,taxtleo.TAX_EXMPT_CR_METHOD_CODE
,taxtleo.ALLOW_RECOVERABILITY_FLAG
,taxtleo.RECOVERY_RATE_OVERRIDE_FLAG
,taxtleo.PRIMARY_RECOVERY_TYPE_CODE
,taxtleo.PRIMARY_REC_RATE_DET_RULE_FLAG
,taxtleo.SEC_REC_RATE_DET_RULE_FLAG
,taxtleo.TAX_RATE_RULE_FLAG
,taxtleo.DEF_REC_SETTLEMENT_OPTION_CODE
,RepType.REPORTING_TYPE_CODE
,(SELECT lkp1.Meaning
  FROM Fnd_lookups lkp1
  WHERE lkp1.Lookup_type = 'ZX_REPORTING_DATA'
  AND lkp1.Lookup_code = RepType.reporting_type_datatype_code
  AND SYSDATE BETWEEN NVL(lkp1.START_DATE_ACTIVE, SYSDATE) AND NVL(lkp1.END_DATE_ACTIVE, SYSDATE)
  )
  ,DECODE(RepType.Reporting_Type_Datatype_Code,'TEXT',ReportCodesAssocEO.Reporting_Code_Char_Value,'YES_NO',
  ReportCodesAssocEO.Reporting_Code_Char_Value,'DATE',ReportCodesAssocEO.Reporting_Code_Date_Value,'NUMERIC_VALUE',
  TO_CHAR(ReportCodesAssocEO.Reporting_Code_Num_Value)),NVL2(RepCode.REPORTING_CODE_NAME, RepCode.REPORTING_CODE_NAME,
    (SELECT lkp.meaning
    FROM fnd_lookups lkp where lkp.lookup_type= 'ZX_YES_NO'
    AND lkp.lookup_code                      = ReportCodesAssocEO.REPORTING_CODE_CHAR_VALUE
AND SYSDATE BETWEEN lkp.START_DATE_ACTIVE
AND NVL(lkp.END_DATE_ACTIVE, SYSDATE)AND NVL(lkp.ENABLED_FLAG, 'N') = 'Y'
    AND RepType.Reporting_Type_Datatype_Code = 'YES_NO'
    ))  "RepType Description"
,ReportCodesAssocEO.EFFECTIVE_FROM  "RepType EFF From"
,ReportCodesAssocEO.EFFECTIVE_TO  "RepType EFF TO"
FROM APPS.ZX_PARTY_TAX_PROFILE PTP,
  APPS.ZX_SUBSCRIPTION_DETAILS ZSD,
  APPS.HR_OPERATING_UNITS HOU,
  APPS.ZX_REGIMES_USAGES ZRU,
  APPS.ZX_MCO_EO_TAXES_V TAXTLEO ,
  ZX_REPORT_CODES_ASSOC ReportCodesAssocEO,
  Zx_Reporting_Types_VL RepType,
  Zx_Reporting_Codes_VL RepCode
  WHERE 1=1
  AND TaxTLEO.tax_regime_code       = zru.tax_regime_code
AND ptp.party_type_code           = 'OU'
AND ptp.party_id                  = hou.organization_id
AND zru.first_pty_org_id          = ptp.party_tax_profile_id
AND zru.first_pty_org_id          = zsd.first_pty_org_id
AND zsd.tax_regime_code           = ZRU.TAX_REGIME_CODE
AND TAXTLEO.tax_id                =ReportCodesAssocEO.ENTITY_ID(+)
AND ReportCodesAssocEO.ENTITY_CODE(+)='ZX_TAXES'
AND ReportCodesAssocEO.Reporting_Type_Id = RepType.Reporting_Type_Id(+)
AND RepCode.Reporting_Type_Id(+)  = ReportCodesAssocEO.Reporting_Type_Id
AND RepCode.reporting_code_id(+)  = ReportCodesAssocEO.Reporting_Code_Id
AND zsd.parent_first_pty_org_id   = -99
AND SYSDATE BETWEEN zsd.effective_FROM AND NVL(zsd.effective_to,SYSDATE) ;

Oracle EBS EBTAX-EBTAX REPTYPE REPCODES

Oracle EBS EBTAX-EBTAX REPTYPE REPCODES

Tables:-
ZX_REPORTING_TYPES_VL
ZX_REPORTING_CODES_VL

SQL Example:-

SELECT
a.reporting_type_code
,a.reporting_type_name
,DECODE(a.legal_message_flag,'A','All','Y','Legal Justification Message Type','N','Tax Reporting Type',a.legal_message_flag) "Reporting Type Purpose"
,(SELECT lkp1.Meaning
  FROM Fnd_lookups lkp1
  WHERE lkp1.Lookup_type = 'ZX_REPORTING_DATA'
  AND lkp1.Lookup_code = a.reporting_type_datatype_code
  AND SYSDATE BETWEEN NVL(lkp1.START_DATE_ACTIVE, SYSDATE) AND NVL(lkp1.END_DATE_ACTIVE, SYSDATE)
  ) "Data Type"
  ,a.has_reporting_codes_flag
  ,a.tax_regime_code
  ,a.Tax
  ,a.max_length
  ,a.min_length
  ,a.effective_FROM "REP Type EFF From"
  ,a.effective_to "REP Type EFF TO"
  ,b.reporting_code_char_value
  ,b.reporting_code_name
  ,b.effective_FROM "REP CODE EFF FROM"
  ,b.effective_to "REP CODE EFF TO"
  FROM apps.ZX_REPORTING_TYPES_VL a,
apps.ZX_REPORTING_CODES_VL b WHERE 1=1 AND a.reporting_type_id =b.reporting_type_id(+);

Oracle EBS EBTAX-EBTax Condition Sets

Oracle EBS EBTAX-EBTax Condition Sets

Tables:-
ZX_MCO_EO_RULES_V
ZX_PROCESS_RESULTS
ZX_CONDITION_GROUPS_VL
ZX_DET_FACTOR_TEMPL_VL
ZX_CONDITIONS
ZX_DETERMINING_FACTORS_VL
ZX_PARTY_TAX_PROFILE
ZX_SUBSCRIPTION_DETAILS

SQL Example:-

SELECT
b.CONDITION_GROUP_CODE "Tax Condition Set"
 ,c.CONDITION_GROUP_NAME  "Condition Set Name"
 ,RuleTLEO.DET_FACTOR_TEMPL_CODE  "Determining Factor Set"
 ,d.DET_FACTOR_TEMPL_NAME "Determining Factor Set Name"
 ,(select ft.TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL ft
  WHERE ft.TERRITORY_CODE=c.COUNTRY_CODE) "Country Name"
  ,c.enabled_flag
  ,(SELECT lkp4.meaning
  FROM FND_LOOKUPS lkp4
  WHERE lkp4.lookup_type ='ZX_DETERMINING_FACTOR_CLASS'
  AND lkp4.lookup_code   =e.DETERMINING_FACTOR_CLASS_CODE
  AND SYSDATE BETWEEN lkp4.START_DATE_ACTIVE AND NVL(lkp4.END_DATE_ACTIVE, SYSDATE)
  AND NVL(lkp4.ENABLED_FLAG, 'N') = 'Y'
  ) "Determining Factor Class"
  ,CASE
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'USER_DEFINED_GEOGRAPHY'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'GEOGRAPHY'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PLACE_OF_SUPPLY_TYPE', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PARTY_FISCAL_CLASS'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'PARTY_SITE_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_GEO_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PARTY'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'LEGAL_PARTY_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_LEGAL_PARTY_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'REGISTRATION'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_REGISTRATION_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'ACCOUNTING_FLEXFIELD'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_ACCOUNT_FLEX_CQ', e.DETERMINING_FACTOR_CQ_CODE)
    WHEN e.DETERMINING_FACTOR_CLASS_CODE = 'PRODUCT_GENERIC_CLASSIFICATION'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'TRX_GENERIC_CLASSIFICATION'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'DOCUMENT'
    OR e.DETERMINING_FACTOR_CLASS_CODE   = 'TRX_FISCAL_CLASS'
    THEN ZX_UTIL_PKG.get_lookup_meaning('ZX_PROD_TRX_CATEG', e.DETERMINING_FACTOR_CQ_CODE)
    ELSE NULL
  END "Class Qualifier"
  ,f.DETERMINING_FACTOR_NAME
  ,e.OPERATOR_CODE
  ,e.VALUE_LOW
  ,e.VALUE_HIGH
  ,e.IGNORE_FLAG
  FROM
  APPS.ZX_MCO_EO_RULES_V RuleTLEO,
  APPS.ZX_PROCESS_RESULTS b,
  APPS.ZX_CONDITION_GROUPS_VL c,
  APPS.ZX_DET_FACTOR_TEMPL_VL d,
  APPS.ZX_CONDITIONS e,
  APPS.ZX_DETERMINING_FACTORS_VL f,
  APPS.ZX_PARTY_TAX_PROFILE PTP,
  APPS.ZX_SUBSCRIPTION_DETAILS ZSD,
  APPS.HR_OPERATING_UNITS HOU,
  APPS.ZX_REGIMES_USAGES ZRU,
  APPS.ZX_REGIMES_VL RegimeTLEO,
  APPS.ZX_TAXES_VL TaxTLEO
  WHERE 1=1
  AND RuleTLEO.CONTENT_OWNER_ID     =-99
AND RuleTLEO.TAX_RULE_ID            = b.TAX_RULE_ID
AND b.CONDITION_GROUP_ID            = c.CONDITION_GROUP_ID
AND c.CONDITION_GROUP_CODE          = e.CONDITION_GROUP_CODE
AND RuleTLEO.DET_FACTOR_TEMPL_CODE  = d.DET_FACTOR_TEMPL_CODE
AND e.DETERMINING_FACTOR_CLASS_CODE = f.DETERMINING_FACTOR_CLASS_CODE
AND e.DETERMINING_FACTOR_CODE       = f.DETERMINING_FACTOR_CODE
AND RuleTLEO. TAX_REGIME_CODE       = RegimeTLEO. TAX_REGIME_CODE
AND RuleTLEO. TAX                   = TaxTLEO.TAX
AND RuleTLEO.TAX_REGIME_CODE        = TaxTLEO.TAX_REGIME_CODE
AND zru.tax_regime_id               = RegimeTLEO.tax_regime_id
AND RegimeTLEO.tax_regime_code      = TaxTLEO.tax_regime_code
AND ptp.party_type_code             = 'OU'
AND ptp.party_id                    = hou.organization_id
AND zru.first_pty_org_id            = ptp.party_tax_profile_id
AND zru.first_pty_org_id            = zsd.first_pty_org_id
AND zsd.tax_regime_code             = ZRU.TAX_REGIME_CODE
AND zsd.parent_first_pty_org_id     = -99
AND SYSDATE BETWEEN zsd.effective_FROM AND NVL(zsd.effective_to,SYSDATE);

Oracle EBS EBTAX-EBTax Determining Factors

Oracle EBS EBTAX-EBTax Determining Factors

Tables:-
ZX_DETERMINING_FACTORS_VL

SQL Example:-

SELECT
Lookup.MEANING  "Determining Factor Class"
,zdfv.DETERMINING_FACTOR_NAME "Determining Factor Name"
,zdfv.DETERMINING_FACTOR_DESC "Description"
,CASE
    WHEN zdfv.tax_rules_flag = 'Y'
    THEN 'Tax Rules'
    WHEN zdfv.tax_regime_det_flag = 'Y'
    THEN 'Tax Regime Determination'
    WHEN zdfv.taxable_basis_flag = 'Y'
    THEN 'Taxable Basis Formula'
  END "Determining Factor Usage"
  FROM APPS.ZX_DETERMINING_FACTORS_VL zdfv,
  APPS.FND_LOOKUPS Lookup
  WHERE 1=1
  AND zdfv.DETERMINING_FACTOR_CLASS_CODE = Lookup.LOOKUP_CODE
AND Lookup.LOOKUP_TYPE                 = 'ZX_DETERMINING_FACTOR_CLASS'
AND Lookup.LOOKUP_CODE                <> 'DERIVED'
AND SYSDATE BETWEEN Lookup.START_DATE_ACTIVE AND NVL(Lookup.END_DATE_ACTIVE, SYSDATE)
AND NVL(Lookup.ENABLED_FLAG, 'N') = 'Y'
AND( zdfv.tax_rules_flag          = 'Y'
OR zdfv.tax_regime_det_flag       = 'Y'
OR zdfv.taxable_basis_flag        = 'Y');

Oracle EBS Latam Tax-Latin Tax Categories

Oracle EBS Latam Tax-Latin Tax Categories

Tables:-
JL_ZZ_AR_TX_CATEGRY_V

SQL Example:-

SELECT
hou.name  "Name"
,jzv.tax_category "Tax Category"
 ,TO_CHAR(jzv.start_date_active,'DD-MM-YYYY') "Effective From"
 ,jzv.description "Category Description"
 ,jzv.threshold_check_level_desc  "Threshold Check"
 ,jzv.threshold_check_grp_by_desc_nb  "Grouping Condition Type"
 ,jzv.threshold_check_grp_by_desc "Grouping Condition"
 ,jzv.org_tax_attribute_desc    "Org Determing Condition"
 ,jzv.cus_tax_attribute "Cust Determining Condtion"
 ,jzv.txn_tax_attribute "Trans Determining Condition"
 ,jzv.mandatory_in_class  "Mandatory In Class"
 ,jzv.tax_code 
 ,jzv.tax_rate 
 ,jzv.tax_authority_code
 ,jzv.print_flag
 FROM
 APPS.JL_ZZ_AR_TX_CATEGRY_V jzv
 , APPS.hr_operating_units hou
 WHERE 1=1
 AND jzv.org_id = hou.organization_id;

Oracle EBS Latam Tax-Latin Tax Lookup

Oracle EBS Latam Tax-Latin Tax Lookup

Tables:-
FND_FLEX_VALUES_VL
FND_FLEX_VALUE_SETS

SQL Example:-

SELECT
ffvv.flex_value
,ffvv.flex_value_meaning
 ,ffvv.description
 ,ffvv.enabled_flag
 ,TO_CHAR(ffvv.start_date_active,'DD-MM-YYYY')
 ,TO_CHAR(ffvv.end_date_active,'DD-MM-YYYY')
 ,ffvs.flex_value_set_name
 FROM APPS.FND_FLEX_VALUES_VL FFVV
 ,APPS.FND_FLEX_VALUE_SETS FFVS
 WHERE 1=1
 AND ffvv.flex_value_set_id = ffvs.flex_value_set_id
 AND ffvs.flex_value_set_name = '&&';

Oracle EBS Latam Tax-LAT JL CITY

Oracle EBS Latam Tax-LAT JL CITY

Tables:-
FND_LOOKUP_TYPES_VL
FND_LOOKUP_VALUES_VL
SQL Example:-

SELECT
fltv.lookup_type  "Lookup Type"
,fltv.meaning "Meaning"
 ,fav.application_name  "Application Name"
 ,fltv.description  "Description"
 ,DECODE(fltv.customization_level,'U','User','','Other')  "Access Level"
 ,flvv.lookup_code
 ,flvv.meaning
 ,flvv.description
 ,flvv.tag
 ,TO_CHAR(flvv.start_date_active,'DD-MM-YYYY')
 ,TO_CHAR(flvv.end_date_active,'DD-MM-YYYY')
 ,flvv.enabled_flag
 FROM
 APPS.FND_LOOKUP_TYPES_VL fltv
 , APPS.FND_LOOKUP_VALUES_VL flvv
 , APPS.FND_APPLICATION_VL FAV
 WHERE 1=1
 AND fltv.lookup_type  =flvv.lookup_type
 AND fltv.application_id = fav.application_id
 AND fav.application_name IN ('Latin America Localizations')
 AND fltv.lookup_type = '&&STATE_PROVINCE'; 

Oracle EBS Latam Tax-Latin Fiscal Classification

Oracle EBS Latam Tax-Latin Fiscal Classification

Tables:-
JL_ZZ_AR_TX_FSC_LOOKUP_V
JL_ZZ_AR_TX_FSC_CLS_ALL

SQL Example:-

SELECT
jzat.lookup_code
,jzat.meaning
,jzat.description
 ,TO_CHAR(jzat.start_date_active,'DD-MM-YYYY')
 ,TO_CHAR(jzat.end_date_active,'DD-MM-YYYY')
 ,hou.name,TO_CHAR(jzatf.start_date_active,'DD-MM-YYYY')
 ,TO_CHAR(jzatf.end_date_active,'DD-MM-YYYY')
 ,jzatf.tax_code,jzatf.enabled_flag
 FROM
 apps.JL_ZZ_AR_TX_FSC_LOOKUP_V jzat
 ,apps.JL_ZZ_AR_TX_FSC_CLS_ALL jzatf
 ,apps.hr_operating_units hou
 WHERE 1=1
 AND jzatf.org_id = hou.organization_id
 AND jzatf.fiscal_classification_code = jzat.lookup_code ;

Oracle EBS Latam Tax-China Golden Tax Rules

Oracle EBS Latam Tax-China Golden Tax Rules
Tables:-
AR_GTA_RULE_HEADERS_ALL
AR_GTA_RULE_TRX_TYPES_ALL

SQL Example:-

SELECT
hou.name
,TO_CHAR(agr.start_date,'DD-MM-YYYY')
 ,agr.rule_name
 ,agr.description
 ,DECODE(agr.Invoice_type,0,'Special vat Invoice',2,'Common Vat invoice')
 ,agr.primary_flag
 ,(select name from APPS.RA_CUST_TRX_TYPES_ALL RCTT where cust_trx_type_id = agrt.cust_trx_type_id)
 ,DECODE(agrt.condition_rule,'I','Include','E','Exclude')
 ,agrt.description
 ,agr.sales_list_flag
 ,agr.currency_option
 ,agr.specific_currency_code
 FROM
 apps.AR_GTA_RULE_HEADERS_ALL agr,
 apps.AR_GTA_RULE_TRX_TYPES_ALL agrt,
 apps.hr_operating_units hou
 WHERE 1=1
 AND agr.rule_header_id = agrt.rule_header_id
AND   agr.org_id =  agrt.org_id
AND   agr.org_id =  hou.organization_id ;

Oracle EBS Latam Tax-Condition Classes

Oracle EBS Latam Tax-Condition Classes

Tables:-
JL_ZZ_AR_TX_ATT_CLS_LOOKUP_V
JL_ZZ_AR_TX_ATT_CLS_V

SQL Example:-

SELECT
jzav.tax_attr_class_type  "Class Type"
,jzav.tax_attr_class_code "Class Code"
,jzav.tax_attribute_name  "Condition Code"
,jzalv.start_date_active  "Start Date Active"
,jzalv.end_date_active  "End Date Active"
,hou.name "Name"
,jzav.tax_category  "Tax Category"
,jzav.enabled_flag  "Enabled Flag"
FROM
JL_ZZ_AR_TX_ATT_CLS_LOOKUP_V jzalv
,JL_ZZ_AR_TX_ATT_CLS_V jzav
,HR_OPERATING_UNITS hou
WHERE 1=1
AND jzalv.lookup_type = jzav.tax_attr_class_type
 AND jzav.org_id = hou.organization_id
 AND jzalv.lookup_type   = UPPER('Organization_Class')
 AND hou.short_code= '&&';

Oracle EBS Latam Tax-Geography Hierarchy

Oracle EBS Latam Tax-Geography Hierarchy

Tables:-
HZ_GEOGRAPHIES
HZ_GEOGRAPHY_IDENTIFIERS
HZ_HIERARCHY_NODES

SQL Example:-

SELECT
hg.geography_element2 "Province"
,hg.geography_name  "Geography Name" 
,hg.geography_code  "Geography Code"
 ,gi.identifier_subtype "Code Type"
 ,gi.geo_data_provider  "Code Data"
 ,hg.COUNTRY_CODE "Country Code"
 ,hg.GEOGRAPHY_ELEMENT1 "Country"
 ,hg.GEOGRAPHY_ELEMENT1_CODE
 ,hg.GEOGRAPHY_ELEMENT2_CODE
 ,hg.GEOGRAPHY_ELEMENT3
 ,hg.GEOGRAPHY_ELEMENT3_CODE
 ,hg.GEOGRAPHY_ELEMENT4
 ,hg.GEOGRAPHY_ELEMENT4_CODE
 ,hg.GEOGRAPHY_ELEMENT5
 ,hg.GEOGRAPHY_ELEMENT5_CODE
 ,hg.GEOGRAPHY_ELEMENT6
 ,hg.GEOGRAPHY_ELEMENT7
 ,hg.GEOGRAPHY_ELEMENT8
 ,hg.GEOGRAPHY_ELEMENT9
 ,hg.GEOGRAPHY_ELEMENT10
 ,gi.IDENTIFIER_VALUE,gi.PRIMARY_FLAG
 FROM APPS.HZ_GEOGRAPHIES hg,
APPS.HZ_GEOGRAPHY_IDENTIFIERS gi,
APPS.HZ_HIERARCHY_NODES nd,
APPS.XLE_ENTITY_PROFILES xep,
APPS.HR_OPERATING_UNITS hou
WHERE 1=1
AND gi.geography_id (+) =hg.geography_id
AND nd.parent_table_name  = 'HZ_GEOGRAPHIES'
AND nd.hierarchy_type     = 'MASTER_REF'
AND nd.level_number       = 1
AND nd.child_id           = hg.geography_id
AND nd.child_object_type  = hg.geography_type
AND nd.child_table_name   = 'HZ_GEOGRAPHIES'
      AND gi.identifier_type(+) ='CODE'
AND gi.geography_use(+)   ='MASTER_REF'
      and hg.OBJECT_VERSION_NUMBER=gi.OBJECT_VERSION_NUMBER
AND nd.PARENT_ID=xep.geography_id
      AND xep.LEGAL_ENTITY_ID=hou.DEFAULT_LEGAL_CONTEXT_ID;

Oracle EBS Latam Tax-Company WTH Applicability

Oracle EBS Latam Tax-Company WTH Applicability

Tables:-
JL_ZZ_AP_COMP_AWT_TYPES

SQL Example:-

SELECT
c.name  "Operating Unit"
,b.name "Legal Entity"
,awt_type_code  "With Holding Tax Type"
,wh_agent_flag  "WH Agent Flag"
,payment_city   "Payment City"
FROM
APPS.JL_ZZ_AP_COMP_AWT_TYPES a
,APPS.XLE_ENTITY_PROFILES b
,APPS.HR_OPERATING_UNITS c
WHERE 1=1
AND a.legal_entity_id = b.legal_entity_id
AND b.legal_entity_id   = c.default_legal_context_id ;

Oracle EBS Latam Tax-Latin Tax Profiles

Oracle EBS Latam Tax-Latin Tax Profiles

Tables:-
JL_ZZ_AR_TX_CATEG_ALL
JL_ZZ_AR_TX_CUS_CLS_ALL

SQL Example:-

SELECT DISTINCT ---'JL','Q_LAT_TAX_PROFILES','DATA','E',
 hou.name "Operating Unit"
 ,tc.tax_category "Tax Category"
 ,tcac.tax_attribute_name
,SUBSTR(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tcac.tax_attribute_name, 'CONTRIBUTOR_ATTRIBUTE'),1,80)  "Condition Code"
,tcac.tax_attribute_value "Value Code"
,SUBSTR(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tcac.tax_attribute_value, 'JLZZ_AR_TX_ATTR_VALUE'),1,80)  "Value Meaning"
,tcac.enabled_flag  "Enabled Flag"
FROM
JL_ZZ_AR_TX_CATEG_ALL tc
,JL_ZZ_AR_TX_CUS_CLS_ALL tcac
,HR_OPERATING_UNITS hou
WHERE 1=1
AND tcac.tax_category_id = tc.tax_category_id
AND tcac.org_id = hou.organization_id; 

Oracle EBS Latam Tax-Geography Management

Oracle EBS Latam Tax-Geography Management

Tables:-
HZ_GEO_STRUCT_MAP_DTL
HZ_GEO_STRUCTURE_LEVELS
HZ_GEOGRAPHY_TYPES_VL
HZ_GEOGRAPHIES
HZ_GEO_STRUCT_MAP

SQL Example:-

SELECT
hg.geography_name "Geography Name"
,a.COUNTRY_CODE "Country Code"
,a.LOC_TBL_NAME "Source Table"
,a.ADDRESS_STYLE  "Address Style"
,decode(a.ADDRESS_STYLE,null,( select meaning from apps.ar_lookups where lookup_type = 'HZ_GEO_NO_STYLE'and lookup_code = 'NOSTYLE')
,decode( a.LOC_TBL_NAME,'HZ_LOCATIONS',nvl(( select descriptive_flex_context_name from apps.fnd_descr_flex_contexts_vl where application_id in ( '222')
and descriptive_flexfield_name ='Remit Address HZ' and descriptive_flex_context_code = a.ADDRESS_STYLE)
,a.ADDRESS_STYLE),nvl((select descriptive_flex_context_name from apps.fnd_descr_flex_contexts_vl where application_id in ('800')
and descriptive_flexfield_name ='Address Location' and descriptive_flex_context_code =a.ADDRESS_STYLE),
a.ADDRESS_STYLE ))) "Address Style Desc"
,decode( a.LOC_TBL_NAME, 'HZ_LOCATIONS', 'Remit Address HZ', 'Address Location') "Desc Flex Name"
,(select meaning from apps.ar_lookups where lookup_type = 'HZ_GEO_NO_STYLE' and lookup_code = 'NOSTYLE')  "Default Address Style"
,decode(a.LOC_TBL_NAME, 'HZ_LOCATIONS',decode(a.ADDRESS_STYLE, null, 'N', 'Y'), 'Y')  "Delete enabled"
,hgt.geography_type_name  "Geography Type Name"
,b.loc_component  "Geo Valid"
,hg.geography_name  "Geography Name"
,hgt.geography_type_name  "Geography Type"
,b.loc_component  "LOC Componenet"
,HZ_GEO_UI_UTIL_PUB.check_geo_tax_valid( b.map_id, b.geography_type, 'TAX') "Tax Valid"
,HZ_GEO_UI_UTIL_PUB.check_geo_tax_valid( b.map_id, b.geography_type, 'GEOGRAPHY') "Geo Valid"
FROM
APPS.HZ_GEO_STRUCT_MAP_DTL b,
APPS.HZ_GEO_STRUCTURE_LEVELS lvl,
APPS.HZ_GEOGRAPHY_TYPES_VL hgt,
APPS.HZ_GEOGRAPHIES hg,
APPS.XLE_ENTITY_PROFILES xep,
apps.HZ_GEO_STRUCT_MAP a,
APPS.XLE_REGISTRATIONS reg,
APPS.HR_OPERATING_UNITS hou
WHERE 1=1
AND b.geography_type (+) = lvl.geography_type
AND b.geography_type = lvl.geography_type
AND hgt.geography_type     = lvl.geography_type
AND hg.geography_id = lvl.geography_id
AND  hg.geography_id =xep.geography_id
AND a.COUNTRY_CODE = hg.country_code
AND b.map_id = a.map_id
 AND xep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND xep.legal_entity_id = hou.default_legal_context_id;

Oracle EBS Latam Tax-State Province

Oracle EBS Latam Tax-State Province

Tables:-
FND_LOOKUP_TYPES_VL
FND_LOOKUP_VALUES_VL

SQL Example:-

SELECT
fltv.lookup_type
,fltv.meaning
,fav.application_name
,fltv.description
,DECODE(fltv.customization_level,'U','User','','Other') "Access Level"
,flvv.lookup_code
,flvv.meaning "Line Meaning"
,flvv.description "Line Desc"
,flvv.tag
,TO_CHAR(flvv.start_date_active,'DD-MM-YYYY') "Start Date Active"
,TO_CHAR(flvv.end_date_active,'DD-MM-YYYY') "End Date Active"
,flvv.enabled_flag
FROM
APPS.FND_LOOKUP_TYPES_VL fltv
,APPS.FND_LOOKUP_VALUES_VL flvv
,APPS.FND_APPLICATION_VL FAV
WHERE 1=1
AND fltv.lookup_type  =flvv.lookup_type
AND fltv.application_id = fav.application_id
AND fav.application_name IN ('Latin America Localizations')
AND fltv.lookup_type = 'JLZZ_STATE_PROVINCE';

Oracle EBS Latam Tax-China System Profiles

Oracle EBS Latam Tax-China System Profiles
Tables:-
AR_GTA_SYSTEM_PARAMETERS_ALL
AR_GTA_TAX_LIMITS_ALL

SQL Example:-

SELECT
 hou.name "OU NAME"
 ,agt.FP_tax_registration_number  "Gol Tax Registration Num"
 ,DECODE(agt.Invoice_type,0,'Special vat Invoice',2,'Common Vat invoice') "Invoice Type"
 ,agt.max_amount  "Gol Tax Max Amount"
 ,agt.max_num_of_line "Gol Tax No Line"
 ,ags.gt_currency_code  "Gol Tax Currency Code"
 ,ags.vat_tax_type_code "Gol Tax Type"
 ,ags.trx_line_split_flag "Gol Split Transaction Line"
 ,ags.auto_batch_numbering_flag "Batch Entry"
 ,ags.next_batch_number "Batch Number"
 ,ags.item_name_source_flag "Item Source flag"
 ,ags.ra_line_context_code  "Context"
 ,ags.ra_model_attribute_column "Item Model"
 ,ags.ra_tax_attribute_column   "Tax Denomination"
 FROM
 apps.AR_GTA_SYSTEM_PARAMETERS_ALL ags,
     apps.AR_GTA_TAX_LIMITS_ALL agt,
     apps.hr_operating_units hou
        WHERE 1=1
     AND agt.org_id =  ags.org_id
 AND ags.org_id = hou.organization_id;

Oracle EBS Latam Tax-AP Application Tax Options

Oracle EBS Latam Tax-AP Application Tax Options

Tables:-
ZX_PRODUCT_OPTIONS_ALL

SQL Example:-
SELECT DISTINCT
  b.name  "OU Name"
  ,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                 = DECODE(a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY', 8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                   = a.def_option_hier_1_code
     AND START_DATE_ACTIVE            <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE)>= SYSDATE
     AND ENABLED_FLAG                  = 'Y') "Defaulting Order1"
,(SELECT Meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200,'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_2_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y') "Defaulting Order2"
,(SELECT Meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222,'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_3_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y') "Defaulting Order3"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201,'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.def_option_hier_4_code
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y')  "Defaulting Order4"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_5_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y')  "Defaulting Order5"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_6_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y')  "Defaulting Order6"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_7_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y')  "Defaulting Order7"
,a.tax_classification_code  "Fin. Option Tax Classification"
,DECODE(a.tax_method_code,'LTE',NULL,c.Meaning) "Default Order Use Status"
FROM
APPS.ZX_PRODUCT_OPTIONS_ALL a,   
APPS.HR_OPERATING_UNITS b,   
APPS.FND_LOOKUPS c
WHERE 1=1
AND a.org_id= b.organization_id 
AND c.lookup_code                    = a.use_tax_classification_flag 
AND c.lookup_type                    = 'ZX_DEFAULTING_HIERARCHY_STATUS' 
AND c.start_date_active             <= SYSDATE 
AND NVL(c.end_date_active, SYSDATE) >= SYSDATE 
AND c.enabled_flag                   = 'Y' 
AND a.APPLICATION_ID =   
(SELECT fav.application_id   
FROM apps.fnd_application_vl fav   
WHERE fav.application_short_name='SQLAP')
AND b.name= '&&';

Oracle EBS Latam Tax-Latin Tax Groups

Oracle EBS Latam Tax-Latin Tax Groups
Tables:-
JL_ZZ_AR_TX_GROUPS

SQL Example:-

 SELECT DISTINCT
 b.name "Operating Unit"
,(select TAX_CODE from APPS.AR_VAT_TAX WHERE ORG_ID=a.org_id and VAT_TAX_ID=a.GROUP_TAX_ID) "Tax Group"
,c.TAX
,(select CUS_TAX_ATTRIBUTE from JL_ZZ_AR_TX_CATEGRY where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Contributor Condition"
,a.CONTRIBUTOR_TYPE "Contributor Value"
,(select ORG_TAX_ATTRIBUTE from JL_ZZ_AR_TX_CATEGRY where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Organization Condition"
,a.ESTABLISHMENT_TYPE "Organization Value"
,(select TXN_TAX_ATTRIBUTE from JL_ZZ_AR_TX_CATEGRY where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Transaction Condition"
,a.TRANSACTION_NATURE "Transaction Value"
,a.START_DATE_ACTIVE  "Effective From"
,a.END_DATE_ACTIVE  "Effective To"
,a.USE_TX_CATEG_THRESHOLDS  "Use Category Thresholds"
,a.TAX_CODE
,a.BASE_RATE
,DECODE(a.TRIBUTARY_SUBSTITUTION, NULL, 'Y', 'N') "Tributary Substitution"
,(select distinct TAX_CATEG_TO_REDUCE_DESC FROM APPS.JL_ZZ_AR_TX_GROUPS_V
WHERE ORG_ID=a.org_id and GROUP_TAX_ID=a.GROUP_TAX_ID and TAX_CATEGORY=c.TAX)  "Tax Category to Compound Base"
,a.MIN_AMOUNT
,a.MIN_TAXABLE_BASIS
,a.MIN_PERCENTAGE
,a.TAX_INCLUSIVE
,a.CALCULATE_IN_OE  "Calculate in OM"
,(select CUS_TAX_ATTRIBUTE_DESC from JL_ZZ_AR_TX_CATEGRY_V where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Deter_Fact_Tran_Condi"
,(select ORG_TAX_ATTRIBUTE_DESC from JL_ZZ_AR_TX_CATEGRY_V where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Deter_Fact_Org_Cond"
,(select ATTR_VAL_DESC from JL_ZZ_AR_TX_ATT_VAL_V where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_ATTRIBUTE_VALUE=a.ESTABLISHMENT_TYPE
and DEFAULT_TO_CLASS='Y') "Deter_Fact_Org_Value"
,(select TXN_TAX_ATTRIBUTE_DESC from JL_ZZ_AR_TX_CATEGRY_V where TAX_CATEGORY_ID=a.TAX_CATEGORY_ID
and ORG_ID=a.ORG_ID
and TAX_CATEGORY=c.TAX) "Deter_Fact_Tran_Condi"
,NVL((select TRANSACTION_NATURE_DESC from APPS.JL_ZZ_AR_TX_GROUPS_V where GROUP_TAX_ID=a.GROUP_TAX_ID
and ORG_ID=a.ORG_ID
and TAX_GROUP_RECORD_ID=a.TAX_GROUP_RECORD_ID
and TRANSACTION_NATURE=a.TRANSACTION_NATURE
and TAX_CATEGORY=c.TAX) ,a.TRANSACTION_NATURE) "Deter_Fact_Tran_Value"
,(select DESCRIPTION from APPS.AR_VAT_TAX WHERE ORG_ID=a.org_id and VAT_TAX_ID=a.GROUP_TAX_ID)  "DESCRIPTION"
,(select ATTRIBUTE1 from CLL_F043_JL_ZZ_AR_TX_GRPS_EXT
where TAX_GROUP_RECORD_ID=a.TAX_GROUP_RECORD_ID)  "Withholding Tax Type"
,c.TAX_RATE
,c.DESCRIPTION
FROM
APPS.JL_ZZ_AR_TX_GROUPS a,
APPS.HR_OPERATING_UNITS b,
APPS.AR_VAT_TAX c,
WHERE 1=1
AND a.org_id= b.organization_id
AND a.TAX_CODE=c.TAX_CODE
AND a.org_id not in ('4176')
AND a.ORG_ID=c.ORG_ID
and c.END_DATE is  null
and c.ENABLED_FLAG='Y'
and a.TAX_CATEGORY_ID=c.GLOBAL_ATTRIBUTE1;

Oracle EBS Latam Tax-Vat Document Letter Assignment

Oracle EBS Latam Tax-Vat Document Letter Assignment

Tables:-
JL_AR_AR_DOC_LETTER_ALL

SQL Example:-

SELECT
hou.name  "Operating Unit"
,vlkp1.meaning  "Organization Condition"
,vlkp2.meaning  "Organization Value"
,clkp1.meaning  "Contributor Condition"
,clkp2.meaning  "Contributor Value"
,doc.document_letter  "Document Letter"
,doc.start_date_active  "From Date"
,doc.end_date_active  "To Date"
FROM
JL_AR_AR_DOC_LETTER_ALL doc
,FND_LOOKUPS vlkp1
,FND_LOOKUPS vlkp2
,FND_LOOKUPS clkp1
,FND_LOOKUPS clkp2
,fnd_lookups dlkp
,HR_OPERATING_UNITS hou
WHERE 1=1
AND doc.org_tax_attribute_name = vlkp1.lookup_code
AND vlkp1.lookup_type = 'ORGANIZATION_ATTRIBUTE'
AND doc.org_tax_attribute_value  = vlkp2.lookup_code
AND vlkp2.lookup_type = 'JLZZ_AR_TX_ATTR_VALUE'
AND doc.con_tax_attribute_name   = clkp1.lookup_code
AND clkp1.lookup_type = 'CONTRIBUTOR_ATTRIBUTE'
AND doc.con_tax_attribute_value  = clkp2.lookup_code
AND clkp2.lookup_type = 'JLZZ_AR_TX_ATTR_VALUE'
AND doc.document_letter = dlkp.lookup_code
AND dlkp.lookup_type = 'JLAR_DOCUMENT_LETTER'
AND doc.org_id = hou.organization_id
AND hou.short_code= '&&';

SQL for AR CM & DM

SELECT rct.CUSTOMER_TRX_ID
,rct.TRX_NUMBER
,rctt.CUST_TRX_TYPE_ID
,rct.OLD_TRX_NUMBER
,rctt.NAME
,rctt.DESCRIPTION
,rctt.TYPE
FROM
APPS.RA_CUSTOMER_TRX_ALL rct,
APPS.RA_CUST_TRX_TYPES_ALL rctt
WHERE rct.CUST_TRX_TYPE_ID=rctt.CUST_TRX_TYPE_ID
and rct.LEGAL_ENTITY_ID=&&
and rctt.TYPE='DM';

Oracle EBS Latam Tax-Source and Type Relationship

Oracle EBS Latam Tax-Source and Type Relationship

Tables:-
JG_ZZ_AR_SRC_TRX_TY_ALL

SQL Example:-
SELECT
hou.name  "OU NAME"
,(SELECT name
    FROM apps.RA_CUST_TRX_TYPES_ALL rct
      WHERE rct.cust_trx_type_id = jt.cust_trx_type_id) "Transaction Type Name"
,(SELECT name
    FROM apps.RA_BATCH_SOURCES_ALL rbs
      WHERE rbs.batch_source_id =jt.batch_source_id)  "Transaction Source Name"
,jt.Invoice_class "Invoice Class"
FROM
JG_ZZ_AR_SRC_TRX_TY_ALL jt
,hr_operating_units hou
WHERE 1=1
AND jt.org_id = hou.organization_id 
AND hou.short_code= '&&';

Oracle EBS Latam Tax-AR Application Tax Options

Oracle EBS Latam Tax-AR Application Tax Options

Tables:-
ZX_PRODUCT_OPTIONS_ALL

SQL Example:-
SELECT 
b.name "OU Name"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                 = DECODE(a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY', 8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                   = a.def_option_hier_1_code
     AND START_DATE_ACTIVE            <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE)>= SYSDATE
     AND ENABLED_FLAG                  = 'Y'  ) "Defaulting Order1"
,(SELECT Meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200,'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_2_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y'  ) "Defaulting Order2"
,(SELECT Meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222,'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_3_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y'   ) "Defaulting Order3"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201,'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.def_option_hier_4_code
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y'   ) "Defaulting Order4"
,(SELECT meaning
     FROM Fnd_lookups
     WHERE lookup_type                  = DECODE (a.application_id,200, 'ZX_AP_DEFAULT_HIERARCHY',222, 'ZX_AR_DEFAULT_HIERARCHY',201, 'ZX_PO_DEFAULT_HIERARCHY',275, 'ZX_PA_DEFAULT_HIERARCHY',8407,'ZX_IGC_DEFAULT_HIERARCHY')
     AND lookup_code                    = a.DEF_OPTION_HIER_5_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y'   ) "Defaulting Order5"
,a.tax_classification_code "System Option Tax Classi"
,(SELECT Meaning
     FROM Fnd_Lookups
     WHERE Lookup_Type                  = 'ZX_TAX_METHOD_OPTIONS'
     AND Lookup_Code                    = a.TAX_METHOD_CODE
     AND START_DATE_ACTIVE             <= SYSDATE
     AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
     AND ENABLED_FLAG                   = 'Y'  ) "Tax Determination Method"
,DECODE(a.tax_method_code,'LTE',NULL,c.Meaning) "Default Order Use Status"
FROM
APPS.ZX_PRODUCT_OPTIONS_ALL a,
APPS.HR_OPERATING_UNITS b,
APPS.FND_LOOKUPS c
WHERE 1=1
AND a.org_id= b.organization_id 
AND c.lookup_code                    = a.use_tax_classification_flag 
AND c.lookup_type                    = 'ZX_DEFAULTING_HIERARCHY_STATUS' 
AND c.start_date_active             <= SYSDATE 
AND NVL(c.end_date_active, SYSDATE) >= SYSDATE 
AND c.enabled_flag                   = 'Y' 
AND a.APPLICATION_ID =(SELECT fav.application_id   
FROM apps.fnd_application_vl fav   
WHERE fav.application_short_name='AR')
AND b.name= '&&';