Oracle Applications: Query

select distinct

api.source,

apb.batch_name,

api.doc_sequence_value,

apdv.description line_description,

pv.vendor_name,

pv.segment1 vendor_number,

pvs.vendor_site_code,

api.invoice_num,

api.invoice_currency_code,

api.invoice_date,

aps.due_date,

api.gl_date,

gcc.segment1||'.'||

gcc.segment2||'.'||

gcc.segment3||'.'||

gcc.segment4||'.'||

gcc.segment5||'.'||

gcc.segment6||'.'||

gcc.segment7||'.'||

gcc.segment8 Charge_account,

(SELECT FV.description FROM apps.FND_FLEX_VALUES_VL FV,apps.FND_ID_FLEX_SEGMENTS_VL FS WHERE 1=1 AND fv.FLEX_VALUE = gcc.segment1

AND fs.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID AND fs.ID_FLEX_NUM =gcc.chart_of_accounts_id and id_flex_code ='GL#' AND segment_num =1)||'.'||

(SELECT FV.description FROM apps.FND_FLEX_VALUES_VL FV,apps.FND_ID_FLEX_SEGMENTS_VL FS WHERE 1=1 AND fv.FLEX_VALUE = gcc.segment2

AND fs.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID AND fs.ID_FLEX_NUM =gcc.chart_of_accounts_id AND id_flex_code ='GL#' AND segment_num =2)||'.'||

(SELECT FV.description FROM apps.FND_FLEX_VALUES_VL FV,apps.FND_ID_FLEX_SEGMENTS_VL FS WHERE 1=1 AND fv.FLEX_VALUE = gcc.segment3

AND fs.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID AND fs.ID_FLEX_NUM =gcc.chart_of_accounts_id AND id_flex_code ='GL#' AND segment_num =3)||'.'||

(SELECT FV.description FROM apps.FND_FLEX_VALUES_VL FV,apps.FND_ID_FLEX_SEGMENTS_VL FS WHERE 1=1 AND fv.FLEX_VALUE = gcc.segment4

AND fs.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID AND fs.ID_FLEX_NUM =gcc.chart_of_accounts_id AND id_flex_code ='GL#' AND segment_num =4)||'.'||

(SELECT FV.description FROM apps.FND_FLEX_VALUES_VL FV,apps.FND_ID_FLEX_SEGMENTS_VL FS WHERE 1=1 AND fv.FLEX_VALUE = gcc.segment5

AND fs.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID AND fs.ID_FLEX_NUM =gcc.chart_of_accounts_id AND id_flex_code ='GL#' AND segment_num =5)||'.'||

(SELECT FV.description FROM apps.FND_FLEX_VALUES_VL FV,apps.FND_ID_FLEX_SEGMENTS_VL FS WHERE 1=1 AND fv.FLEX_VALUE = gcc.segment6

AND fs.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID AND fs.ID_FLEX_NUM =gcc.chart_of_accounts_id AND id_flex_code ='GL#' AND segment_num =6)||'.'||

(SELECT FV.description FROM apps.FND_FLEX_VALUES_VL FV,apps.FND_ID_FLEX_SEGMENTS_VL FS WHERE 1=1 AND fv.FLEX_VALUE = gcc.segment7

AND fs.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID AND fs.ID_FLEX_NUM =gcc.chart_of_accounts_id AND id_flex_code ='GL#' AND segment_num =7)||'.'||

(SELECT FV.description from apps.FND_FLEX_VALUES_VL FV,apps.FND_ID_FLEX_SEGMENTS_VL FS WHERE 1=1 AND fv.FLEX_VALUE = gcc.segment8

AND fs.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID AND fs.ID_FLEX_NUM =gcc.chart_of_accounts_id AND id_flex_code ='GL#' AND segment_num =8) Account_Description,

apdv.line_type_lookup_code,

api.invoice_amount,

NULL Tax_Type, --jirtl.tax_type,

apdv.amount line_amount,

NULL Service, --decode(jirtl.tax_type,'Service',jirtl.tax_amount,0) "Service",

NULL ST_Edu_Cess, --decode(jirtl.tax_type,'SERVICE_EDUCATION_CESS',jirtl.tax_amount,0) "ST Edu Cess",

NULL Service_Tax_SH, --decode(jirtl.tax_type,'SERVICE_SH_EDU_CESS',jirtl.tax_amount,0) "Service Tax SH",

NULL VAT_Recoverable, --decode(jirtl.tax_type,'VALUE ADDED TAX',(decode(jitc.mod_cr_percentage,'100',jirtl.tax_amount,0)),0) "VAT Recoverable",

NULL VAT_Non_Recoverable, --decode(jirtl.tax_type,'VALUE ADDED TAX',(decode(jitc.mod_cr_percentage,'',jirtl.tax_amount,0)),0) "VAT Non Recoverable",

NULL CST, --decode(jirtl.tax_type,'CST',jirtl.tax_amount,0) "CST",

NULL Other, --decode(jirtl.tax_type,'Other',jirtl.tax_amount,0) "Other",

jiat.tds_amount,

jiat.tds_invoice_num,

jiat.tds_section,

jiat.tds_tax_rate,

fl.meaning,

japvs.service_tax_regno,

vtds.pan_no,

vtds.tan_no,

api.pay_group_lookup_code,

apc.checkrun_name,

apc.check_number,

apc.check_date,

apc.amount check_amount,

fu.user_name,

apdv.distribution_line_number,

apc.attribute3 TDS_Challan_No,

apc.attribute1 TDS_Challan_Date

from

apps.ap_invoices_all api,

-- apps.FND_ID_FLEX_SEGMENTS_VL FS,

apps.gl_code_combinations gcc,

apps.gl_code_combinations gcc1,

apps.JA_IN_AP_TDS_INVOICES jiat,

apps.po_vendors pv,

apps.AP_INVOICE_PAYMENT_HISTORY_V pay,

apps.po_vendor_sites_all pvs,

apps.JA_IN_VENDOR_TDS_INFO_HDR vtds,

apps.JA_IN_VENDOR_TDS_INFO_HDR vtds1,

apps.ap_invoice_distributions_v apdv,

apps.po_headers_all ph,

apps.ap_terms apt,

apps.JA_IN_PO_VENDOR_SITES japvs,

-- apps.FND_FLEX_VALUES_VL FV,

apps.AP_PAYMENT_SCHEDULES aps,

apps.ap_batches apb,

apps.ap_checks apc,

apps.fnd_user fu,

--apps.AP_INV_SELECTION_CRITERIA_v aisc,

apps.AP_INVOICE_PAYMENTS aip, apps.JA_IN_TAX_CODES jitc,

-- apps.JA_IN_RECEIPT_TAX_LINES jirtl,

apps.fnd_lookup_values fl

where api.set_of_books_id=2002

--and api.invoice_num in ('05/01/2011-1')

--and api.invoice_id=apd.invoice_id

and gcc.code_combination_id=apdv.dist_code_combination_id

and gcc1.code_Combination_id=api.accts_pay_code_Combination_id

and api.vendor_id=pv.vendor_id

and jiat.invoice_id(+)=api.invoice_id

and api.invoice_id=pay.invoice_id(+)

and api.invoice_id=aps.invoice_id(+)

and api.vendor_site_id=pvs.vendor_site_id

and pv.vendor_id=vtds.vendor_id(+)

and pvs.vendor_site_id=vtds1.vendor_site_id(+)

and apdv.invoice_id=api.invoice_id

and apdv.po_header_id=ph.po_header_id(+)

and api.terms_id=apt.term_id

and api.batch_id=apb.batch_id(+)

and japvs.vendor_id=pv.vendor_id

and japvs.vendor_site_id=pvs.vendor_site_id

and aip.check_id=apc.check_id(+)

and api.invoice_id=aip.invoice_id(+)

and api.last_updated_by=fu.user_id

--and apdv.description=jirtl.tax_name

--and apdv.rcv_shipment_header_id=jirtl.shipment_header_id

--and apdv.rcv_shipment_line_id=jirtl.shipment_line_id

--and jitc.tax_id=jirtl.tax_id

and apdv.line_type_lookup_code in ('ITEM')

and japvs.service_type_code=fl.lookup_code(+)

and fl.language='US'

--and apdv.rcv_shipment_header_id is not NULL

and apdv.po_header_id is not null--and apd.row_id=apdv.row_id

UNION ALL