po订单
-
2023年3月17日发(作者：医院中心供氧系统)OracleEBS-SQL（PO-5）：采购订单控制信息查询.sql
selectdistinct
_header_id,
--_lookup_code,
t1采购订单号,
_name采购员,
_num订单⾏,
t1物料编码,
_description物料说明,
case
ization_Status='APPROVED'then'已审批'
ization_status='REQUIRESREAPPROVAL'then'要求重新审批'
ization_status='INPROCESS'then'处理中'
ization_status='REJECTED'then'拒绝'
else'未完成'
end审批状态,
--decode(ed_flag,case
--'Y',ed_flag='Y'then'已审批'
--'已审批',
--'N',ed_flag='N'then'处理中'
--'处理中',
--'R',ed_flag='R'then'要求重新审批'
--'要求重新审批',
--'F',ed_flag='F'then'拒绝'
--'拒绝',else'未完成'
--'未完成')审批状态,
end审批状态,
case
_code='CLOSED'then'已关闭'
_code='FINALLYCLOSED'then'最终关闭'
else'打开'
end关闭状态,
case
_code='CLOSED'then'已关闭'
_code='CLOSEDFORRECEIVING'then'接收关闭'
_code='CLOSEDFORINVOICE'then'发票关闭'
_code='FINALLYCLOSED'then'最终关闭'
else'打开'
end发运⾏状态,
--_code状态,
ty数量,
--TY_DUE数量到期,
--(TY_RECEIVED)数量到期,
TY_RECEIVED接收数量,
--TY_ACCEPTED验收数量,
TY_REJECTED拒收数量,
TY_BILLED开单数量,
TY_CANCELLED取消数量,
(TY_TY_BILLED)未开单数,
_name供应商,
/*case
_type_lookup_code='A_VENDOR'then'固定资产供应商'
_type_lookup_code='L_VENDOR'then'备品备件供应商'
_type_lookup_code='M_VENDOR'then'材料供应商'
_type_lookup_code='F_VENDOR'then'成品供应商'
_type_lookup_code='C_VENDOR'then'费⽤资⾦类供应商'
_type_lookup_code='VENDOR'then'供应商'
_type_lookup_code='PUBLICSECTORCOMPANIES'then'公关部门公司'
_type_lookup_code='TAXAUTHORITY'then'税务机关'
_type_lookup_code='EMPLOYEE'then'员⼯'
_type_lookup_code='S_VENDOR'then'安装维修类供应商'
_type_lookup_code='O_VENDOR'then'其它类供应商'
else''
end供应商类型,*/
_site_code供应商地点,
_price单价,
on_date创建⽇期,
_update_date修改⽇期,
ed_date承诺⽇期,
_by_date需求⽇期,
ts订单说明
_headers_allpha,
_lines_allpla,
_line_locations_allplla,
_system_items_bmsi,
_vendorsppv,
_vendor_sites_allpvs,
_people_fappf
_header_id=_header_id
_id=ory_item_id
_header_id=_header_id
zation_id=X
_id=Y
_line_id=_line_id
_id=_id
_id=_id
_site_id=_site__lookup_code='STANDARD'--标准采购订单--'BLANKET'
--anddecode(_code,'','OPEN',_code)='OPEN'--查找未关闭订单⾏
--on_datebetweento_date('20**-01-01','yyyy-mm-dd')andto_date('20**-01-31','yyyy-mm-dd')
on_date>to_date('20**-01-15','yyyy-mm-dd')
_type='OP'_namenotlike'ERP⼯%'
anddecode(_flag,'','N',_flag)='N'
--查找未取消订单⾏--on_date --and(TY_RECEIVED)>0--查找有剩余数订单 --ty_received>_price=0 --ty>1000000 t1, _num