代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
OracleEBS
】
应付的帐龄分析SQL
作者:
专注Oracle EBS
/ 发布于
2012/11/29
/
622
select hao.name 公司名称, pha.segment1 订单编号, to_char(pla.line_num) 订单行编号, pv.vendor_name 供应商名, pvs.vendor_site_code 供应商地点, nvl(msib.segment1, null) 物料编码, pla.item_description 物料名称, hao1.name 发运组织, rec.currency_code 订单币种, decode(rec.transaction_type, 'RETURN TO VENDOR', -rec.quantity, rec.quantity) 数量, pla.unit_price 单价, decode(rrs.entered_cr, null, -rrs.entered_dr, rrs.entered_cr) 金额, rec.currency_conversion_rate 接收汇率, decode(rrs.accounted_cr, null, -rrs.accounted_dr, rrs.accounted_cr) 本位币金额, plla.accrue_on_receipt_flag 接收应计标志, gcc.segment3 科目, to_char(rec.transaction_date, 'YYYY-MM-DD') 总帐日期, '采购接收' 来源, rsh.receipt_num 接收编号, rec.currency_conversion_date 接收日期, pl2.displayed_field 接收类型, pda.recovery_rate 退税率, apca.name 税码 from po_headers_all pha, po_lines_all pla, po_line_locations_all plla, po_distributions_all pda, po_vendors pv, po_vendor_sites_all pvs, hr_all_organization_units hao, hr_all_organization_units hao1, gl_code_combinations gcc, rcv_transactions rec, rcv_shipment_headers rsh, ap_tax_codes_all apca, rcv_receiving_sub_ledger rrs, po_lookup_codes pl2, mtl_system_items_b msib where pha.org_id = hao.organization_id and msib.organization_id = 103 and pha.vendor_id = pv.vendor_id and pha.vendor_site_id = pvs.vendor_site_id and plla.ship_to_organization_id = hao1.organization_id and pha.po_header_id = pla.po_header_id and plla.po_line_id = pla.po_line_id and user_entered_flag = 'Y' and rec.po_line_id = pla.po_line_id and rec.po_header_id = pha.po_header_id and rec.po_line_location_id = plla.line_location_id and pda.line_location_id = plla.line_location_id and pda.accrual_account_id = gcc.code_combination_id and rec.shipment_header_id = rsh.shipment_header_id and plla.tax_code_id = apca.tax_id(+) and pda.po_distribution_id = rrs.reference3 and rec.transaction_id = rrs.rcv_transaction_id and gcc.code_combination_id = rrs.code_combination_id and rrs.set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID') and rec.transaction_type in ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT') and pl2.lookup_code = rec.transaction_type and pl2.lookup_type = 'RCV TRANSACTION TYPE' and pla.item_id = msib.inventory_item_id union all select hao.name 公司名称, pha.segment1 订单编号, to_char(pla.line_num) 订单行编号, pv.vendor_name 供应商名, pvs.vendor_site_code 供应商地点, nvl(msib.segment1, null) 物料编码, pla.item_description 物料名称, hao1.name 发运组织, decode(rt.currency_code, null, pha.currency_code, rt.currency_code) 订单币种, -aida.quantity_invoiced 数量, aida.unit_price 单价, nvl(aala.entered_cr, 0) - nvl(aala.entered_dr, 0) 金额, rt.currency_conversion_rate 接收汇率, nvl(aala.accounted_cr, 0) - nvl(aala.accounted_dr, 0) 本位币金额, plla.accrue_on_receipt_flag 接收应计标志, gcc.segment3 科目, to_char(aida.accounting_date, 'YYYY-MM-DD') 总帐日期, '发票匹配' 来源, rsh.receipt_num 接收编号, rt.currency_conversion_date 接收日期, pl1.displayed_field 接收类型, pda.recovery_rate 退税率, apca.name 税码 from ap_invoices_all aia, ap_invoice_distributions_all aida, ap_ae_lines_all aala, hr_all_organization_units hao, gl_code_combinations gcc, po_vendors pv, po_vendor_sites_all pvs, po_distributions_all pda, po_line_locations_all plla, hr_all_organization_units hao1, po_lines_all pla, po_headers_all pha, rcv_shipment_headers rsh, rcv_transactions rt, ap_tax_codes_all apca, po_lookup_codes pl1, mtl_system_items_b msib where aia.invoice_id = aida.invoice_id and msib.organization_id = 103 and aida.org_id = hao.organization_id and aala.source_table = 'AP_INVOICE_DISTRIBUTIONS' and aida.invoice_distribution_id = aala.source_id and aala.code_combination_id = gcc.code_combination_id and aida.po_distribution_id = pda.po_distribution_id and pda.line_location_id = plla.line_location_id and plla.ship_to_organization_id = hao1.organization_id and plla.po_line_id = pla.po_line_id and pla.po_header_id = pha.po_header_id and aia.vendor_id = pv.vendor_id and aia.vendor_site_id = pvs.vendor_site_id and aida.po_distribution_id is not null and aala.ae_line_type_code = 'AP ACCRUAL' and aida.rcv_transaction_id = rt.transaction_id(+) and rt.shipment_header_id = rsh.shipment_header_id(+) and plla.tax_code_id = apca.tax_id(+) and (rt.transaction_type in ('RECEIVE', 'RETURN TO VENDOR') or rt.transaction_type is null) and pl1.lookup_code(+) = rt.transaction_type and pl1.lookup_type(+) = 'RCV TRANSACTION TYPE' and pla.item_id = msib.inventory_item_id union all select hao.name 公司名称, aia.invoice_num 订单编号, to_char(aida.distribution_line_number) 订单行编号, pv.vendor_name 供应商名, pvs.vendor_site_code 供应商地点, null 物料编码, null 物料名称, null 发运组织, aia.invoice_currency_code 订单币种, null 数量, null 单价, aia.invoice_amount * (-1) 金额, aida.exchange_rate 接收汇率, nvl(aida.base_amount * (-1), aida.amount * (-1)) 本位币金额, null 接收应计标志, gcc.segment3 科目, to_char(aida.accounting_date, 'YYYY-MM-DD') 总帐日期, '发票无匹配' 来源, null 接收编号, null 接收日期, null 接收类型, null 退税率, null 税码 from ap_invoice_distributions_all aida, ap_invoices_all aia, hr_all_organization_units hao, gl_code_combinations gcc, po_vendors pv, po_vendor_sites_all pvs where gcc.code_combination_id = aida.dist_code_combination_id and aia.invoice_id = aida.invoice_id and pv.vendor_id = aia.vendor_id and aida.org_id = hao.organization_id and pv.vendor_id = pvs.vendor_id and aia.vendor_site_id = pvs.vendor_site_id and gcc.segment3 like '2121020000' and aida.po_distribution_id is null and aida.reversal_flag is null
试试其它关键字
帐龄分析
同语言下
.
设置窗体的位置
.
初始化操作
.
在FORMS调用WEB页面
.
判断数据块的项是否为空
.
EBS 循环处理块记录
.
Oracle Form中调用并发请求生成报表并输出为PDF的方法
.
添加并发程序到指定职责API
.
EBS请求查询输出
.
删除 AP 发票相关脚本
.
ebs安全性
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现,不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
专注Oracle EBS
贡献的其它代码
(
66
)
.
ERP系统里的BOM展开函数
.
EBS查找报表或菜单所属职责
.
查询用户客户化的文件配置
.
SQL写出带扩展数量的BOM多级清单
.
oracle开发常用LOV
.
Oracle Patch 版本的查询
.
科目余额SQL
.
根据报表文件名称关键字查找报表的执行文件名称等信息
.
查找在标准请求组里提交的报表所在的职责
.
查找在菜单里提交的报表所在职责
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3