Oracle EBS GST-Line Transaction DFF

Oracle EBS GST-Line Transaction DFF
Configuration:Line Transaction DFF,
Responsibility:Application Developer, 
Navigation:Flex field-> Descriptive -> Segments

Tables:-
FND_DESCR_FLEX_CONTEXTS_VL
FND_DESCRIPTIVE_FLEXS_VL
FND_APPLICATION_VL
FND_DESCR_FLEX_COL_USAGE_VL

SQL Example:-

SELECT
ap.application_name Application,
fdf.TITLE
,fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE "Code"
,fdfc.DESCRIPTION "Description"
,fdfc.ENABLED_FLAG  "Enabled"
,fdfcu.column_seq_num "Segment Number"
,fdfcu.form_left_prompt "Segment Name"
,fdfcu.application_column_name "Window Prompt"
,fdfcu.FORM_LEFT_PROMPT "Column"
,fdfcu.FLEX_VALUE_SET_ID  "Value Set"
,fdfcu.DISPLAY_FLAG "Displayed"
,fdfcu.ENABLED_FLAG "SS-Enabled"
,fdfc.DESCRIPTIVE_FLEX_CONTEXT_NAME "Name"
FROM
APPS.fnd_descr_flex_contexts_vl  fdfc,
APPS.fnd_descriptive_flexs_vl fdf,
APPS.fnd_application_vl ap,
APPS.FND_DESCR_FLEX_COL_USAGE_VL fdfcu
WHERE 1=1
AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE='OFI TAX IMPORT'
AND  fdfc.DESCRIPTIVE_FLEXFIELD_NAME=fdf.DESCRIPTIVE_FLEXFIELD_NAME
AND fdfc.APPLICATION_ID =fdf.APPLICATION_ID
AND fdf.TITLE='Line Transaction Flexfield'
AND fdfc.APPLICATION_ID =222
AND fdfc.APPLICATION_ID =ap.APPLICATION_ID
AND fdfc.APPLICATION_ID =fdfcu.APPLICATION_ID
AND fdfc.DESCRIPTIVE_FLEXFIELD_NAME=fdfcu.DESCRIPTIVE_FLEXFIELD_NAME
AND fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE=fdfcu.DESCRIPTIVE_FLEX_CONTEXT_CODE;

Oracle EBS GST-AP Financial Options

Oracle EBS GST-AP Financial Options
Configuration:AP-Financial Options,
Responsibility:Oracle Payables,
Navigation:Setup ->Options ->Financials Options

Tables:-
FINANCIALS_SYSTEM_PARAMS_ALL
HR_OPERATING_UNITS
GL_LEDGER_CONFIG_DETAILS
HRFV_BUSINESS_GROUPS
XLE_ENTITY_PROFILES
ORG_ORGANIZATION_DEFINITIONS
PO_LOCATIONS_VAL_V

SQL Example:-

SELECT
hou.name  "OPERATING_UNIT"
,fsp.future_period_limit 
,gcck.concatenated_segments "LIABILITY"
,gcck2.concatenated_segments  "PREPAYMENT"
,gcck3.concatenated_segments  "BILLS_PAYABLE"
,gcck4.concatenated_segments  "DISCOUNT_TAKEN"
,gcck5.concatenated_segments  "PO_RATE_VARIANCE_GAIN"
,gcck6.concatenated_segments  "PO_RATE_VARIANCE_LOSS"
,gcck7.concatenated_segments  "EXPENSES_CLEARING"
,gcck8.concatenated_segments  "MISCELLANEOUS"
,gcck9.concatenated_segments  "RETAINAGE"
,fsp.rfq_only_site_flag "RFQ_ONLY_SITE"
,plvv.location_code "SHIP_TO_LOCATION"
,plvv2.location_code  "BILL_TO_LOCATION"
,ood.organization_code ||' - '|| ood.organization_name  "INVENTORY_ORGANIZATION"
,fsp.ship_via_lookup_code "SHIP_VIA"
,fsp.fob_lookup_code  "FOB"
,fsp.freight_terms_lookup_code  "FREIGHT_TERMS"
,fsp.req_encumbrance_flag "USE_REQUISITION_ENCUMBRANCE"
,fsp.reserve_at_completion_flag "RESERVE_AT_COMPLETION"
,fsp.purch_encumbrance_flag "USE_PO_ENCUMBRANCE"
,fsp.vat_country_code "MEMBER_STATE"
,fsp.vat_registration_num "VAT_REGISTRATION_NUMBER"
,hbg.business_group_name  "BUSINESS_GROUP"
,decode(fsp.expense_check_address_flag,'h','home','o','office','p','provisional') "EXPENSE_REIMBURSEMENT_ADDRESS"
,fsp.use_positions_flag "USE_APPROVAL_HIERARCHIES"
FROM
APPS.FINANCIALS_SYSTEM_PARAMS_ALL fsp ,
APPS.HR_OPERATING_UNITS HOU,
APPS.GL_LEDGER_CONFIG_DETAILS GLCV,
APPS.HRFV_BUSINESS_GROUPS HBG,
APPS.XLE_ENTITY_PROFILES XEP,
APPS.ORG_ORGANIZATION_DEFINITIONS OOD ,
APPS.PO_LOCATIONS_VAL_V PLVV ,
APPS.PO_LOCATIONS_VAL_V PLVV2,
APPS.GL_CODE_COMBINATIONS_KFV GCCK ,
APPS.GL_CODE_COMBINATIONS_KFV GCCK2 ,
APPS.GL_CODE_COMBINATIONS_KFV GCCK3 ,
APPS.GL_CODE_COMBINATIONS_KFV GCCK4 ,
APPS.GL_CODE_COMBINATIONS_KFV GCCK5 ,
APPS.GL_CODE_COMBINATIONS_KFV GCCK6 ,
APPS.GL_CODE_COMBINATIONS_KFV GCCK7 ,
APPS.GL_CODE_COMBINATIONS_KFV GCCK8 ,
APPS.GL_CODE_COMBINATIONS_KFV GCCK9
WHERE 1=1
AND xep.legal_entity_id=glcv.object_id
AND glcv.object_id = hou.default_legal_context_id
AND fsp.accts_pay_code_combination_id =gcck.code_combination_id(+)
AND fsp.prepay_code_combination_id    =gcck2.code_combination_id(+)
AND fsp.future_dated_payment_ccid     =gcck3.code_combination_id(+)
AND fsp.disc_taken_code_combination_id=gcck4.code_combination_id(+)
AND fsp.rate_var_gain_ccid            =gcck5.code_combination_id(+)
AND fsp.rate_var_loss_ccid            =gcck6.code_combination_id(+)
AND fsp.expense_clearing_ccid         =gcck7.code_combination_id(+)
AND fsp.misc_charge_ccid              =gcck8.code_combination_id(+)
AND fsp.retainage_code_combination_id =gcck9.code_combination_id(+)
AND ood.organization_id               =fsp.inventory_organization_id
AND fsp.org_id                        =hou.organization_id
AND plvv.location_id(+)               =fsp.ship_to_location_id
AND plvv2.location_id(+)              =fsp.ship_to_location_id 
AND hou.name= '&&';

Oracle EBS GST-Tax Authority

Oracle EBS GST-Tax Authority
Configuration:Tax Authority,
Responsibility:Oracle Payables,
Navigation:Suppliers ->Entry

Tables:-
AP_SUPPLIERS
FND_LOOKUP_VALUES
AP_SUPPLIER_SITES_ALL
HZ_LOCATIONS
FND_TERRITORIES_VL
HZ_PARTY_SITES
HR_OPERATING_UNITS

SQL Example:-

SELECT
asp.VENDOR_NAME "Supplier Name"
,asp.SEGMENT1 "Supplier Number"
,flv.MEANING  "Type"
,hps.PARTY_SITE_NUMBER  "Site Number"
,ftv.TERRITORY_SHORT_NAME "Country"
,hl.ADDRESS1
,hl.ADDRESS2
,hl.ADDRESS3
,hl.ADDRESS4
,hl.CITY
,hl.COUNTY
,hl.STATE
,hl.PROVINCE
,hl.POSTAL_CODE
,hps.PARTY_SITE_NAME  "Address Name"
,hps.ADDRESSEE 
,decode(hps.status,'A','Active','I','Inactive','M') "Status"
,hps.LANGUAGE
,hps.ATTRIBUTE_CATEGORY "Context Value"
,(select DISTINCT NOTES
FROM pos_address_notes
WHERE PARTY_SITE_ID=hps.PARTY_SITE_ID) "Communication Details"
,(SELECT DISTINCT HCP2.PHONE_AREA_CODE
FROM   APPS.HZ_RELATIONSHIPS HRR2,
APPS.HZ_CONTACT_POINTS HCP2,
APPS.AP_SUPPLIERS ASS2, 
APPS.AP_SUPPLIER_SITES_ALL  assa3,
      APPS.HZ_PARTIES  hp3,
APPS.HR_OPERATING_UNITS     hou3
WHERE 1=1
AND HRR2.SUBJECT_TYPE='ORGANIZATION'
AND HRR2.RELATIONSHIP_CODE='CONTACT'
AND HRR2.STATUS='A'
AND HCP2.OWNER_TABLE_NAME='HZ_PARTIES'
AND HRR2.SUBJECT_ID=ASS2.PARTY_ID
AND HCP2.PRIMARY_FLAG='Y'
AND HCP2.STATUS='A'
AND HCP2.CONTACT_POINT_TYPE='PHONE'
AND ass2.segment1=asp.segment1
AND HCP2.OWNER_TABLE_ID=HRR2.PARTY_ID
AND HRR2.object_id= hps.party_id
AND ass2.vendor_id = assa3.vendor_id) "Phone Area Code"
,(SELECT DISTINCT HCP1.PHONE_NUMBER
FROM   APPS.HZ_RELATIONSHIPS HRR1,
APPS.HZ_CONTACT_POINTS HCP1,
APPS.AP_SUPPLIERS ASS1, 
APPS.AP_SUPPLIER_SITES_ALL  assa2,
APPS.HZ_PARTIES  hp2,
APPS.HR_OPERATING_UNITS     hou2
WHERE 1=1
AND HRR1.SUBJECT_TYPE='ORGANIZATION'
AND HRR1.RELATIONSHIP_CODE='CONTACT'
AND HRR1.STATUS='A'
AND HCP1.OWNER_TABLE_NAME='HZ_PARTIES'
AND HRR1.SUBJECT_ID=ASS1.PARTY_ID
AND HCP1.PRIMARY_FLAG='Y'
AND HCP1.STATUS='A'
AND HCP1.CONTACT_POINT_TYPE='PHONE'
AND ass1.segment1=asp.segment1
AND HCP1.OWNER_TABLE_ID=HRR1.PARTY_ID
AND HRR1.object_id=hps.party_id
AND ass1.vendor_id = assa2.vendor_id) "Phone Number"
,(SELECT DISTINCT HCP.PHONE_AREA_CODE 
FROM HZ_RELATIONSHIPS HR
,HZ_CONTACT_POINTS HCP
,AP_SUPPLIERS ASS
WHERE 1=1
AND HR.SUBJECT_TYPE='ORGANIZATION'
AND HR.RELATIONSHIP_CODE='CONTACT'
AND HR.STATUS='A'
AND HCP.OWNER_TABLE_NAME='HZ_PARTIES'
AND HR.SUBJECT_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HR.PARTY_ID
AND HCP.PRIMARY_FLAG='Y'
AND HCP.STATUS='A'
AND HCP.CONTACT_POINT_TYPE='PHONE'
AND HCP.PHONE_LINE_TYPE='FAX'
AND ASS.PARTY_ID=asp.PARTY_ID) "Fax Area Code"
,(SELECT DISTINCT HCP.PHONE_NUMBER
FROM HZ_RELATIONSHIPS HR
,HZ_CONTACT_POINTS HCP
,AP_SUPPLIERS ASS
WHERE 1=1
AND HR.SUBJECT_TYPE='ORGANIZATION'
AND HR.RELATIONSHIP_CODE='CONTACT'
AND HR.STATUS='A'
AND HCP.OWNER_TABLE_NAME='HZ_PARTIES'
AND HR.SUBJECT_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HR.PARTY_ID
AND HCP.PRIMARY_FLAG='Y'
AND HCP.STATUS='A'
AND HCP.CONTACT_POINT_TYPE='PHONE'
AND HCP.PHONE_LINE_TYPE='FAX'
AND ASS.PARTY_ID=asp.PARTY_ID)  "Fax Number"
,(SELECT DISTINCT HCP1.PHONE_NUMBER
FROM   APPS.HZ_RELATIONSHIPS HRR1,
APPS.HZ_CONTACT_POINTS HCP1,
APPS.AP_SUPPLIERS ASS1, 
APPS.AP_SUPPLIER_SITES_ALL  assa2,
APPS.HZ_PARTIES  hp2,
APPS.HR_OPERATING_UNITS     hou2
WHERE 1=1
AND HRR1.SUBJECT_TYPE='ORGANIZATION'
AND HRR1.RELATIONSHIP_CODE='CONTACT'
AND HRR1.STATUS='A'
AND HCP1.OWNER_TABLE_NAME='HZ_PARTIES'
AND HRR1.SUBJECT_ID=ASS1.PARTY_ID
AND HCP1.PRIMARY_FLAG='Y'
AND HCP1.STATUS='A'
AND HCP1.CONTACT_POINT_TYPE='PHONE'
AND ass1.segment1=asp.segment1
AND HCP1.OWNER_TABLE_ID=HRR1.PARTY_ID
AND HRR1.object_id= hps.party_id
AND ass1.vendor_id = assa2.vendor_id) "Email Address"
,(SELECT DECODE(hpsu.site_use_type, null, 'N', 'Y')
FROM hz_party_site_uses  hpsu
WHERE  hps.PARTY_SITE_ID=hpsu.PARTY_SITE_ID
AND hpsu.SITE_USE_TYPE='PAY') "Purchasing"
,(SELECT DECODE(hpsu1.site_use_type, null, 'N', 'Y')
  FROM hz_party_site_uses  hpsu1
WHERE  hps.PARTY_SITE_ID=hpsu1.PARTY_SITE_ID 
AND hpsu1.SITE_USE_TYPE='PURCHASING')   "Payment"
,(SELECT DECODE(HPSU2.SITE_USE_TYPE, NULL, 'N', 'Y')
  FROM hz_party_site_uses  hpsu2
WHERE  hps.PARTY_SITE_ID=hpsu2.PARTY_SITE_ID
AND hpsu2.SITE_USE_TYPE='RFQ') "RFQ Only"
,'Y' As "Select"
,hps.PARTY_SITE_NAME "Site Name"
,hou.NAME  "Opearting Unit"
,NULL AS "Tax Reportable"
FROM
APPS.AP_SUPPLIERS asp,
APPS.FND_LOOKUP_VALUES flv,
APPS.AP_SUPPLIER_SITES_ALL assa,
APPS.HZ_LOCATIONS  hl,
APPS.FND_TERRITORIES_VL  ftv,
APPS.HZ_PARTY_SITES  hps,
APPS.HR_OPERATING_UNITS  hou
WHERE 1=1
AND asp.VENDOR_TYPE_LOOKUP_CODE=flv.LOOKUP_CODE
AND flv.LANGUAGE='US'
AND flv.LOOKUP_TYPE='VENDOR TYPE'
AND asp.VENDOR_NAME='GST Tax Authority'
AND asp.vendor_id=assa.vendor_id
AND assa.location_id=hl.location_id
AND hl.COUNTRY=ftv.TERRITORY_CODE
AND assa.location_id=hps.location_id
AND assa.org_id=hou.ORGANIZATION_ID
AND hou.name= '&&';

Oracle EBS GST-Commn Configuration

Oracle EBS GST-Commn Configuration
Configuration:Commn Configuration,
Responsibility:Oracle Financials For India,
Navigation:Tax Configuration ->Define Common Configuration

Tables:-
JAI_TAX_CONFIGURATIONS
JAI_TAX_DEFAULTING_BASIS

SQL Example:-

SELECT
'Operating Unit' as "Organization Type"
,e.name "Organization Name"
,(SELECT PARTY_SITE_NAME from hz_party_sites WHERE location_id=f.LOCATION_ID) "Location"
,b.TAX_DEFAULTING_BASIS "Defaulting Basis"
,b.MAX_DAYS
,b.EFFECTIVE_FROM
,b.EFFECTIVE_TO
,b.COPY_TAX_FROM_SOURCE
,(SELECT jr.REGIME_CODE
FROM    APPS.JAI_REGIMES jr, APPS.JAI_TAX_RATES jtr , APPS.hr_organization_units hou
WHERE jr.REGIME_ID=jtr.REGIME_ID
AND jtr.ORG_ID=hou.ORGANIZATION_ID
AND hou.ORGANIZATION_ID=e.ORGANIZATION_ID 
AND REGIME_TYPE='W'
AND COUNTRY_CODE='IN'
AND REGIME_JURISDICTION='COUNTRY')  "Tax Regim"
,(SELECT jtc.TAX_CATEGORY_NAME
FROM    APPS.JAI_REGIMES jr, APPS.JAI_TAX_RATES jtr , APPS.hr_organization_units hr,APPS.JAI_TAX_CATEGORIES jtc
WHERE jr.REGIME_ID=jtr.REGIME_ID
AND jtr.ORG_ID=hr.ORGANIZATION_ID
AND hr.ORGANIZATION_ID=e.ORGANIZATION_ID 
AND REGIME_TYPE='W'
AND COUNTRY_CODE='IN'
AND REGIME_JURISDICTION='COUNTRY'
AND jtr.ORG_ID=jtc.ORG_ID
AND (jtr.TAX_RATE_CODE LIKE ('%'||jtc.TAX_CATEGORY_NAME||'%'))) "Tax Category"
,a.APPROVED_INVOICE_FLAG
,a.APPROVED_TDS_FLAG
,a.DSPTCH_EXCEED_ORG_QTY_FLAG
,a.OSP_RETURN_DAYS
FROM APPS.JAI_TAX_CONFIGURATIONS a,
APPS.jai_tax_defaulting_basis b,
APPS.hr_organization_units e,
APPS.hr_locations_all f
WHERE 1=1
AND a.LOCATION_ID(+)=f.LOCATION_ID
AND b.ORG_ID=e.ORGANIZATION_ID
AND e.LOCATION_ID=f.LOCATION_ID
AND e.name= '&&';

Oracle EBS GST-Third Party

Oracle EBS GST-Third Party
Configuration:Third Party,
Responsibility:Oracle Financials For India,
Navigation:Party Registration->Define Third Party Registration

Tables:-
JAI_PARTY_REGS_V
JAI_PARTY_REG_LINES
JAI_REGIMES
JAI_PARTY_REG_LINES_V
jai_reporting_associations

SQL Example:-

SELECT DISTINCT 
hou.NAME  "Opearting Unit"
,jprv.PARTY_CLASS_NAME "Party Type"
 ,jprv.PARTY_NAME "Party Name"
,jprv.PARTY_NUMBER "Party Number"
,jprv.PARTY_SITE_NAME  "Party Site Name"
,jprv.ITEM_CATEGORY_LIST_DESC  "Item Category"
,jprv.INVOICE_TAX_CATEGORY_NAME  "DTC for Standalone Invoice"
,jprv.PARTY_SITE_ADDRESS "Address"
,jr.REGIME_NAME "Regime Code"
,jprlv.REGISTRATION_TYPE_NAME "Primary Registration Name"
,jprl.REGISTRATION_NUMBER  "Primary Registration Number"
,jprlv.SEC_REGISTRATION_TYPE_NAME "Secondary Registration Name"
,jprl.SECONDARY_REGISTRATION_NUMBER  "Secondary Registration Number"
,jprlv.ASSESSABLE_PRICE_LIST_NAME "Assessable Price List"
,jprlv.DEFAULT_SECTION_NAME  "Default Section"
,jprl.EFFECTIVE_FROM  "Start Date"
,jprl.EFFECTIVE_TO  "End Date"
,(select REGIME_CODE from JAI_PARTY_EXEMPTION_LINES_V where PARTY_REG_ID=jprv.PARTY_REG_ID and LINE_CONTEXT='EXEMPTION') "E-Regime Code"
,(select REGISTRATION_NUMBER from JAI_PARTY_EXEMPTION_LINES_V where PARTY_REG_ID=jprv.PARTY_REG_ID and LINE_CONTEXT='EXEMPTION') "Registration Number"
,(select EXEMPTION_TYPE_DSP from JAI_PARTY_EXEMPTION_LINES_V where PARTY_REG_ID=jprv.PARTY_REG_ID and LINE_CONTEXT='EXEMPTION') "Exemption Type"
,(select TRACKING_NUM from JAI_PARTY_EXEMPTION_LINES_V where PARTY_REG_ID=jprv.PARTY_REG_ID and LINE_CONTEXT='EXEMPTION') "Tracking Number"
,(select EFFECTIVE_FROM from JAI_PARTY_EXEMPTION_LINES_V where PARTY_REG_ID=jprv.PARTY_REG_ID and LINE_CONTEXT='EXEMPTION') "E-Start Date"
,(select EFFECTIVE_TO from JAI_PARTY_EXEMPTION_LINES_V where PARTY_REG_ID=jprv.PARTY_REG_ID and LINE_CONTEXT='EXEMPTION') "E-End Date"
,(select REGIME_CODE from JAI_REGIMES where REGIME_ID=jra.REGIME_ID) "RC-Regime Code"
,jra.REPORTING_TYPE_NAME  "Reporting Type Name"
,jra.REPORTING_CODE "Reporting Code"
,jra.REPORTING_CODE_DESCRIPTION "Reporting Code Description"
,jra.EFFECTIVE_FROM "Reporting Start Date"
,jra.EFFECTIVE_TO "Reporting End Date"
FROM
APPS.JAI_PARTY_REGS_V  jprv,
APPS.hr_organization_units hou,
APPS.JAI_PARTY_REG_LINES jprl,
APPS.JAI_REGIMES jr,
APPS.JAI_PARTY_REG_LINES_V jprlv,
APPS.jai_reporting_associations  jra
WHERE 1=1
AND jprv.ORG_ID=hou.ORGANIZATION_ID
AND jprv.PARTY_REG_ID=jprl.PARTY_REG_ID
AND jprl.REGIME_ID=jr.REGIME_ID
AND jr.REGIME_TYPE='T'
AND jprl.PARTY_REG_ID=jprlv.PARTY_REG_ID
AND jprl.PARTY_REG_LINE_ID=jprlv.PARTY_REG_LINE_ID
AND jprv.PARTY_REG_ID=jra.ENTITY_ID
AND hou.name= '&&';