Friday, October 18, 2013

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
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,
  jitc.tax_type,
  apdv.amount line_amount,
  decode(jitc.tax_type,'Service',apdv.amount,0) "Service",
  decode(jitc.tax_type,'SERVICE_EDUCATION_CESS',apdv.amount,0) "ST Edu Cess",
  decode(jitc.tax_type,'SERVICE_SH_EDU_CESS',apdv.amount,0) "Service Tax SH",
  decode(jitc.tax_type,'VALUE ADDED TAX',(decode(jitc.mod_cr_percentage,'100',apdv.amount,0)),0) "VAT Recoverable",
  decode(jitc.tax_type,'VALUE ADDED TAX',(decode(jitc.mod_cr_percentage,'',apdv.amount,0)),0) "VAT Non Recoverable",
  decode(jitc.tax_type,'CST',apdv.amount,0) "CST",
  decode(jitc.tax_type,'Other',apdv.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.ap_invoice_distributions_all apd,
  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.apps.JA_IN_PO_VENDOR_SITES japvs1,
  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='MISCELLANEOUS'
and japvs.service_type_code=fl.lookup_code(+)
and fl.language='US'
--and apd.distribution_line_number=apdv.distribution_line_number
and jitc.tax_name(+)=apdv.description

UNION ALL
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,
  jitc.tax_type,
  apdv.amount line_amount,
  decode(jitc.tax_type,'Service',apdv.amount,0) "Service",
  decode(jitc.tax_type,'SERVICE_EDUCATION_CESS',apdv.amount,0) "ST Edu Cess",
  decode(jitc.tax_type,'SERVICE_SH_EDU_CESS',apdv.amount,0) "Service Tax SH",
  decode(jitc.tax_type,'VALUE ADDED TAX',(decode(jitc.mod_cr_percentage,'100',apdv.amount,0)),0) "VAT Recoverable",
  decode(jitc.tax_type,'VALUE ADDED TAX',(decode(jitc.mod_cr_percentage,'',apdv.amount,0)),0) "VAT Non Recoverable",
  decode(jitc.tax_type,'CST',apdv.amount,0) "CST",
  decode(jitc.tax_type,'Other',apdv.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.ap_invoice_distributions_all apd,
  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.JA_IN_PO_VENDOR_SITES japvs1,
  --apps.apps.JA_IN_PO_VENDOR_SITES japvs1,
  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 japvs1.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 japvs.service_type_code=fl.lookup_code(+)
and fl.language='US'
--and apdv.rcv_shipment_line_id=jirtl.shipment_line_id
and jitc.tax_name(+)=apdv.description
--and apdv.line_type_lookup_code in ('ITEM','MISCELLANEOUS')
--and apd.distribution_line_number=apdv.distribution_line_number
--and apd.row_id=apdv.row_id
and apdv.po_header_id is NULL
UNION ALL
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,
  jitc.tax_type,
  apdv.amount line_amount,
  decode(jitc.tax_type,'Service',apdv.amount,0) "Service",
  decode(jitc.tax_type,'SERVICE_EDUCATION_CESS',apdv.amount,0) "ST Edu Cess",
  decode(jitc.tax_type,'SERVICE_SH_EDU_CESS',apdv.amount,0) "Service Tax SH",
  decode(jitc.tax_type,'VALUE ADDED TAX',(decode(jitc.mod_cr_percentage,'100',apdv.amount,0)),0) "VAT Recoverable",
  decode(jitc.tax_type,'VALUE ADDED TAX',(decode(jitc.mod_cr_percentage,'',apdv.amount,0)),0) "VAT Non Recoverable",
  decode(jitc.tax_type,'CST',apdv.amount,0) "CST",
  decode(jitc.tax_type,'Other',apdv.amount,0) "Other",
  jiat.tds_amount,
  jiat.tds_invoice_num,
  jiat.tds_section,
  jiat.tds_tax_rate,
  NULL Meaning,--  fl.meaning,
  NULL service_tax_regno,--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.ap_invoice_distributions_all apd,
  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.JA_IN_PO_VENDOR_SITES japvs1,
  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 pv.vendor_id not in (select japvs.vendor_id from apps.JA_IN_PO_VENDOR_SITES japvs)
and pvs.vendor_site_id not in (select japvs.vendor_site_id from apps.JA_IN_PO_VENDOR_SITES japvs)
and aip.check_id=apc.check_id(+)
and api.invoice_id=aip.invoice_id(+)
and api.last_updated_by=fu.user_id
--and japvs.service_type_code is not NULL
--and fl.language='US'
--and apdv.rcv_shipment_line_id=jirtl.shipment_line_id
and jitc.tax_name(+)=apdv.description
--and apdv.line_type_lookup_code in ('ITEM','MISCELLANEOUS')
--and apd.distribution_line_number=apdv.distribution_line_number
--and apd.row_id=apdv.row_id
and apdv.po_header_id is NULL
--order by 7,6,8,14,40
and apdv.rcv_shipment_header_id is NULL
order by 8, 40

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect