為了方便集團內廠間的對帳方便而設計.

集團內部關系由table: otc_po_cust_all 來設定

from_org_id : 需求方即採購方的org_id

dest_org_id : 供給方即接單方的org_id

customer_id : 需求方作為供給方的客戶customer_id

vendor_id : 供給方作為需求方的供應商vendor_id

[@more@]

一次可查兩個org_id間的交易.

主sql:
select a.*,
(select sum(DECODE(ol.LINE_CATEGORY_CODE,'RETURN',-ol.shipped_quantity,OL.SHIPPED_QUANTITY) )
from oe_order_headers_all oh,
oe_order_lines_all ol,
oe_transaction_types_all ota
where 1=1
and nvl(ota.ATTRIBUTE1,'N')='N'
and ota.TRANSACTION_TYPE_ID=oh.ORDER_TYPE_ID
and to_char(ol.actual_shipment_date,'yyyymmdd')>=nvl(:p_date_from,to_char(ol.actual_shipment_date,'yyyymmdd'))
and to_char(ol.actual_shipment_date,'yyyymmdd')<=nvl(:p_date_to,to_char(ol.actual_shipment_date,'yyyymmdd'))
and ol.inventory_item_id=a.inventory_item_id
and ol.header_id=oh.header_id
and ol.org_id=oh.org_id
and oh.sold_to_org_id=a.customer_id
and oh.org_id=a.dest_org_id
) shipped_qty,
(select sum(DECODE(ol.LINE_CATEGORY_CODE,'RETURN',-ol.shipped_quantity,OL.SHIPPED_QUANTITY) *nvl(ol.unit_selling_price,0)*
currency_exchange(oh.TRANSACTIONAL_CURR_CODE,'RMB',oo.SET_OF_BOOKS_ID,sysdate)
)
from oe_order_headers_all oh,
oe_order_lines_all ol,
ORG_ORGANIZATION_DEFINITIONS oo,
oe_transaction_types_all ota
where 1=1
and nvl(ota.ATTRIBUTE1,'N')='N'
and ota.TRANSACTION_TYPE_ID=oh.ORDER_TYPE_ID
and to_char(ol.actual_shipment_date,'yyyymmdd')>=nvl(:p_date_from,to_char(ol.actual_shipment_date,'yyyymmdd'))
and to_char(ol.actual_shipment_date,'yyyymmdd')<=nvl(:p_date_to,to_char(ol.actual_shipment_date,'yyyymmdd'))
and oo.organization_id=oh.org_id
and ol.inventory_item_id=a.inventory_item_id
and ol.header_id=oh.header_id
and ol.org_id=oh.org_id
and oh.sold_to_org_id=a.customer_id
and oh.org_id=a.dest_org_id
) shipped_amount,
(select sum(olrt.ordered_quantity)
from oe_order_headers_all ohrt,
-- oe_order_lines_all ol,
oe_order_lines_all olrt
where 1=1
and olrt.org_id=a.dest_org_id
and olrt.inventory_item_id=a.inventory_item_id
and olrt.header_id=ohrt.header_id
and ohrt.sold_to_org_id=a.customer_id
and to_char(olrt.CREATION_DATE,'yyyymmdd')>=nvl(:p_date_from,to_char(olrt.CREATION_DATE,'yyyymmdd'))
and to_char(olrt.CREATION_DATE,'yyyymmdd')<=nvl(:p_date_to,to_char(olrt.CREATION_DATE,'yyyymmdd'))
and olrt.LINE_CATEGORY_CODE='RETURN'
and nvl(olrt.CANCELLED_FLAG,'N')='N'
and olrt.BOOKED_FLAG='Y'
/*
and (olrt.org_id,olrt.reference_header_id,olrt.reference_line_id,olrt.inventory_item_id)
in
(select ol.org_id,ol.header_id,ol.line_id,ol.inventory_item_id
from oe_order_headers_all oh,oe_order_lines_all ol
where 1=1
and to_char(ol.actual_shipment_date,'yyyymmdd')>=nvl(:p_date_from,to_char(ol.actual_shipment_date,'yyyymmdd'))
and to_char(ol.actual_shipment_date,'yyyymmdd')<=nvl(:p_date_to,to_char(ol.actual_shipment_date,'yyyymmdd'))
and ol.inventory_item_id=a.inventory_item_id
and ol.header_id=oh.header_id
and ol.org_id=oh.org_id
and oh.sold_to_org_id=a.customer_id
and oh.org_id=a.dest_org_id
)
*/
) return_qty,
(select sum(olrt.ordered_quantity*nvl(olrt.unit_selling_price,0)*
currency_exchange(ohrt.TRANSACTIONAL_CURR_CODE,'RMB',oo.SET_OF_BOOKS_ID,sysdate)
)
from oe_order_headers_all ohrt,
oe_order_lines_all olrt,
ORG_ORGANIZATION_DEFINITIONS oo
where 1=1
and olrt.org_id=a.dest_org_id
and olrt.inventory_item_id=a.inventory_item_id
and olrt.header_id=ohrt.header_id
and ohrt.sold_to_org_id=a.customer_id
and to_char(olrt.CREATION_DATE,'yyyymmdd')>=nvl(:p_date_from,to_char(olrt.CREATION_DATE,'yyyymmdd'))
and to_char(olrt.CREATION_DATE,'yyyymmdd')<=nvl(:p_date_to,to_char(olrt.CREATION_DATE,'yyyymmdd'))
and olrt.LINE_CATEGORY_CODE='RETURN'
and nvl(olrt.CANCELLED_FLAG,'N')='N'
and olrt.BOOKED_FLAG='Y'
and oo.organization_id=ohrt.org_id
/*
and (olrt.org_id,olrt.reference_header_id,olrt.reference_line_id,olrt.inventory_item_id)
in
(select ol.org_id,ol.header_id,ol.line_id,ol.inventory_item_id
from oe_order_headers_all oh,oe_order_lines_all ol
where 1=1
and to_char(ol.actual_shipment_date,'yyyymmdd')>=nvl(:p_date_from,to_char(ol.actual_shipment_date,'yyyymmdd'))
and to_char(ol.actual_shipment_date,'yyyymmdd')<=nvl(:p_date_to,to_char(ol.actual_shipment_date,'yyyymmdd'))
and ol.inventory_item_id=a.inventory_item_id
and ol.header_id=oh.header_id
and ol.org_id=oh.org_id
and oh.sold_to_org_id=a.customer_id
and oh.org_id=a.dest_org_id
)
*/
) return_amount
from (
select ym,organization_code,organization_name,vendor_id,vendor_name,item,
inventory_item_id,
CUSTOMER_ID,
DEST_ORG_ID,
sum(receipt_qty) receipt_qty,
sum(receipt_qty*rmb_price) receipt_amount
from (
select to_char(rt.transaction_date,'yyyymm') ym,
ood.ORGANIZATION_CODE,ood.ORGANIZATION_NAME,
ood.organization_id,
ood.SET_OF_BOOKS_ID,ph.vendor_id,
msi.inventory_item_id,
opca.CUSTOMER_ID,
opca.DEST_ORG_ID,
ph.SEGMENT1 po_number,
pl.LINE_NUM,ph.CURRENCY_CODE,
pl.UNIT_PRICE,
pl.unit_price*
currency_exchange(ph.CURRENCY_CODE,'RMB',ood.SET_OF_BOOKS_ID,sysdate) rmb_price,
rt.transaction_date receipt_transaction_date,rsh.receipt_num,
msi.segment1 item,
rsl.item_revision receipt_revision,
decode(RT.TRANSACTION_TYPE,
'RETURN TO VENDOR',-(rt.quantity),
'RETURN TO RECEIVING',-(rt.quantity),
rt.quantity
) receipt_qty,
ph.VENDOR_SITE_ID,
pv.vendor_name,
pv.vendor_name_alt,
pv.segment1 vendor_number
from RCV_TRANSACTIONS RT,
mtl_system_items_b msi,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
ORG_ORGANIZATION_DEFINITIONS ood,
po_vendors pv,
otc_po_cust_all opca,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll
where 1=1
-- and msi.segment1='132288ND'
and msi.inventory_item_id=pl.ITEM_ID
and msi.organization_id=pl.ORG_ID
and pv.VENDOR_ID=ph.vendor_id
and ood.ORGANIZATION_ID=ph.ORG_ID
and RSH.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID= RT.SHIPMENT_LINE_ID
and RT.ORGANIZATION_ID=pll.SHIP_TO_organization_ID
AND RT.PO_LINE_ID=PLL.PO_LINE_ID
and RT.PO_LINE_LOCATION_ID=PLL.LINE_LOCATION_ID
AND RT.PO_RELEASE_ID=PLL.PO_RELEASE_ID
AND ((RT.TRANSACTION_TYPE='DELIVER'
AND RT.DESTINATION_TYPE_CODE='INVENTORY'
)
or
(RT.TRANSACTION_TYPE='RETURN TO VENDOR'
AND RT.DESTINATION_TYPE_CODE='INVENTORY')
or
(RT.TRANSACTION_TYPE='RETURN TO RECEIVING'
AND RT.DESTINATION_TYPE_CODE='INVENTORY')
)
and ph.po_header_id=pl.po_header_id
and ph.vendor_id=opca.vendor_id
and opca.vendor_id=nvl(:p_vendor_id,opca.vendor_id)
and opca.FROM_ORG_ID=:p_org_id
and ph.segment1=nvl(:p_po_number,ph.segment1)
and pl.PO_LINE_ID=pll.PO_LINE_ID
and pl.PO_header_ID=pll.PO_header_ID
and pl.ORG_ID=pll.ORG_ID
and pll.ORG_ID=:p_ORG_ID
and to_char(rt.transaction_date,'yyyymmdd')>=nvl(:p_date_from,to_char(rt.transaction_date,'yyyymmdd'))
and to_char(rt.transaction_date,'yyyymmdd')<=nvl(:p_date_to,to_char(rt.transaction_date,'yyyymmdd'))

union all

select
ym,
oo.ORGANIZATION_CODE,oo.ORGANIZATION_NAME,
oo.organization_id,
oo.SET_OF_BOOKS_ID,op.vendor_id,
ms.inventory_item_id,
op.CUSTOMER_ID,
op.DEST_ORG_ID,
null po_number,
null LINE_NUM,null CURRENCY_CODE,
0 UNIT_PRICE,
0 rmb_price,
null receipt_transaction_date, null receipt_num,
ms.segment1 item,
null receipt_revision,
0 receipt_qty,
null VENDOR_SITE_ID,
pv.vendor_name,
pv.vendor_name_alt,
pv.segment1 vendor_number
from
(
SELECT distinct TO_CHAR(nvl(OL.ACTUAL_SHIPMENT_DATE,ol.CREATION_DATE),'YYYYMM') ym, OL.inventory_item_id
FROM OE_ORDER_LINES_ALL OL,
OE_ORDER_HEADERS_ALL OH,
otc_po_cust_all op
WHERE OL.HEADER_ID=OH.HEADER_ID
and nvl(ol.CANCELLED_FLAG,'N')='N'
and ol.BOOKED_FLAG='Y'
and op.vendor_id=nvl(:p_vendor_id,op.vendor_id)
and op.from_org_id=:p_org_id
AND OH.ORG_ID=op.dest_org_id
AND OH.SOLD_TO_ORG_ID=op.CUSTOMER_ID
AND ( (TO_CHAR(OL.ACTUAL_SHIPMENT_DATE,'YYYYMMDD')>=nvl(:p_date_from,TO_CHAR(OL.ACTUAL_SHIPMENT_DATE,'YYYYMMDD'))
AND TO_CHAR(OL.ACTUAL_SHIPMENT_DATE,'YYYYMMDD')<=nvl(:p_date_to,TO_CHAR(OL.ACTUAL_SHIPMENT_DATE,'YYYYMMDD'))
)
OR
(
to_char(ol.CREATION_DATE,'yyyymmdd')>=nvl(:p_date_from,to_char(ol.CREATION_DATE,'yyyymmdd'))
and to_char(ol.CREATION_DATE,'yyyymmdd')<=nvl(:p_date_to,to_char(ol.CREATION_DATE,'yyyymmdd'))
and ol.LINE_CATEGORY_CODE='RETURN'
)
)
AND NOT EXISTS
(
select 1
from RCV_TRANSACTIONS RT,
mtl_system_items_b msi,
RCV_SHIPMENT_LINES RSL,
otc_po_cust_all opca,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll
where 1=1
-- and msi.segment1 IN ('132288ND','015004N')
and msi.inventory_item_id=pl.ITEM_ID
and msi.organization_id=pl.ORG_ID
AND MSI.INVENTORY_ITEM_ID=OL.INVENTORY_ITEM_ID
AND RSL.SHIPMENT_LINE_ID= RT.SHIPMENT_LINE_ID
and RT.ORGANIZATION_ID=pll.SHIP_TO_organization_ID
AND RT.PO_LINE_ID=PLL.PO_LINE_ID
and RT.PO_LINE_LOCATION_ID=PLL.LINE_LOCATION_ID
AND RT.PO_RELEASE_ID=PLL.PO_RELEASE_ID
AND ((RT.TRANSACTION_TYPE='DELIVER'
AND RT.DESTINATION_TYPE_CODE='INVENTORY'
)
or
(RT.TRANSACTION_TYPE='RETURN TO VENDOR'
AND RT.DESTINATION_TYPE_CODE='INVENTORY')
or
(RT.TRANSACTION_TYPE='RETURN TO RECEIVING'
AND RT.DESTINATION_TYPE_CODE='INVENTORY')
)
and ph.po_header_id=pl.po_header_id
and ph.vendor_id=opca.vendor_id
and opca.vendor_id=nvl(:p_vendor_id,opca.vendor_id)
and opca.FROM_ORG_ID=:p_org_id
and pl.PO_LINE_ID=pll.PO_LINE_ID
and pl.PO_header_ID=pll.PO_header_ID
and pl.ORG_ID=pll.ORG_ID
and pll.ORG_ID=:p_ORG_ID
and to_char(rt.transaction_date,'yyyymmdd')>=nvl(:p_date_from,to_char(rt.transaction_date,'yyyymmdd'))
and to_char(rt.transaction_date,'yyyymmdd')<=nvl(:p_date_to,to_char(rt.transaction_date,'yyyymmdd'))
)
) a,
po_vendors pv,
otc_po_cust_all OP,
mtl_system_items_b MS,
ORG_ORGANIZATION_DEFINITIONS oo
where ms.inventory_item_id=a.inventory_item_id
and ms.organization_id=op.dest_org_id
and op.vendor_id=nvl(:p_vendor_id,op.vendor_id)
and op.from_org_id=:p_org_id
and pv.vendor_id=op.vendor_id
and oo.organization_id=op.dest_org_id

)
group by ym,organization_code,organization_name,vendor_id,vendor_name,item, inventory_item_id,
CUSTOMER_ID,
DEST_ORG_ID
) a

收料明細子sql:由vendor_id與item_id跟主sql關聯

select pl.item_id,
ph.SEGMENT1 po_number,pra.release_num,
pl.LINE_NUM,ph.CURRENCY_CODE,
pl.UNIT_PRICE,
pl.unit_price*
currency_exchange(ph.CURRENCY_CODE,'RMB',ood.SET_OF_BOOKS_ID,sysdate) rmb_price,
rt.transaction_date receipt_transaction_date,rsh.receipt_num,
rsl.item_revision receipt_revision,
decode(RT.TRANSACTION_TYPE,
'RETURN TO VENDOR',-(rt.quantity),
'RETURN TO RECEIVING',-(rt.quantity),
rt.quantity
) receipt_qty,
ph.vendor_id
from RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
ORG_ORGANIZATION_DEFINITIONS ood,
po_vendors pv,
otc_po_cust_all opca,
po_headers_all ph,
po_releases_all pra,
po_lines_all pl,
po_line_locations_all pll
where 1=1
and pv.VENDOR_ID=ph.vendor_id
and ood.ORGANIZATION_ID=ph.ORG_ID
and RSH.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID= RT.SHIPMENT_LINE_ID
and RT.ORGANIZATION_ID=pll.SHIP_TO_organization_ID
AND RT.PO_LINE_ID=PLL.PO_LINE_ID
and RT.PO_LINE_LOCATION_ID=PLL.LINE_LOCATION_ID
AND RT.PO_RELEASE_ID=PLL.PO_RELEASE_ID
AND ((RT.TRANSACTION_TYPE='DELIVER'
AND RT.DESTINATION_TYPE_CODE='INVENTORY'
)
or
(RT.TRANSACTION_TYPE='RETURN TO VENDOR'
AND RT.DESTINATION_TYPE_CODE='INVENTORY')
or
(RT.TRANSACTION_TYPE='RETURN TO RECEIVING'
AND RT.DESTINATION_TYPE_CODE='INVENTORY')
or
(RT.TRANSACTION_TYPE='RETURN TO RECEIVING'
AND RT.DESTINATION_TYPE_CODE='INVENTORY')
)
and pra.po_release_id=pll.po_release_id
and pra.po_header_id=pll.po_header_id
and ph.po_header_id=pl.po_header_id
and ph.vendor_id=opca.vendor_id
and opca.vendor_id=:vendor_id
and opca.FROM_ORG_ID=:p_org_id
and ph.segment1=nvl(:p_po_number,ph.segment1)
and pl.item_id=:inventory_item_id
and pl.PO_LINE_ID=pll.PO_LINE_ID
and pl.PO_header_ID=pll.PO_header_ID
and pl.ORG_ID=pll.ORG_ID
and pll.ORG_ID=:p_ORG_ID
and to_char(rt.transaction_date,'yyyymmdd')>=nvl(:p_date_from,to_char(rt.transaction_date,'yyyymmdd'))
and to_char(rt.transaction_date,'yyyymmdd')<=nvl(:p_date_to,to_char(rt.transaction_date,'yyyymmdd'))

供給方銷貨明細子sql:由org_id,sold_to_org_id,inventory_item_id與主sql關聯.

select * from (
select oh.org_id,oh.sold_to_org_id,oh.TRANSACTIONAL_CURR_CODE,oh.cust_po_number,
to_char(ol.actual_shipment_date,'yyyymmdd') pi_date,
oh.order_number,ol.line_number||'.'||ol.shipment_number line_no,
ol.inventory_item_id,
ol.unit_selling_price,
DECODE(ol.LINE_CATEGORY_CODE,'RETURN',-ol.shipped_quantity,OL.SHIPPED_QUANTITY) qty,
DECODE(ol.LINE_CATEGORY_CODE,'RETURN',-ol.shipped_quantity,OL.SHIPPED_QUANTITY)*nvl(ol.unit_selling_price,0)*
currency_exchange(oh.TRANSACTIONAL_CURR_CODE,'RMB',oo.SET_OF_BOOKS_ID,sysdate) rmb_amount
from oe_order_headers_all oh,
oe_order_lines_all ol,
ORG_ORGANIZATION_DEFINITIONS oo ,
oe_transaction_types_all ota
where 1=1
and nvl(ota.ATTRIBUTE1,'N')='N'
and ota.TRANSACTION_TYPE_ID=oh.ORDER_TYPE_ID
and to_char(ol.actual_shipment_date,'yyyymmdd')>=nvl(:p_date_from,to_char(ol.actual_shipment_date,'yyyymmdd'))
and to_char(ol.actual_shipment_date,'yyyymmdd')<=nvl(:p_date_to,to_char(ol.actual_shipment_date,'yyyymmdd'))
and oo.organization_id=oh.org_id
-- and ol.LINE_CATEGORY_CODE<>'RETURN'
and ol.inventory_item_id=:inventory_item_id
and ol.header_id=oh.header_id
and ol.org_id=oh.org_id
and oh.sold_to_org_id=:customer_id
and oh.org_id=:dest_org_id
union all
select ohrt.org_id,ohrt.sold_to_org_id,ohrt.TRANSACTIONAL_CURR_CODE,ohrt.cust_po_number,
to_char(olrt.CREATION_DATE,'yyyymmdd') pi_date,
ohrt.order_number,olrt.line_number||'.'||olrt.shipment_number line_no,
olrt.inventory_item_id,
olrt.unit_selling_price,
-olrt.ordered_quantity qty,
-olrt.ordered_quantity*nvl(olrt.unit_selling_price,0)*
currency_exchange(ohrt.TRANSACTIONAL_CURR_CODE,'RMB',oo.SET_OF_BOOKS_ID,sysdate) rmb_amount
from --oe_order_headers_all oh,
oe_order_headers_all ohrt,
-- oe_order_lines_all ol,
oe_order_lines_all olrt,
ORG_ORGANIZATION_DEFINITIONS oo
where 1=1
and olrt.inventory_item_id=:inventory_item_id
and oo.organization_id=ohrt.org_id
and ohrt.sold_to_org_id=:customer_id
and ohrt.org_id=:dest_org_id
and ohrt.header_id=olrt.header_id
and ohrt.org_id=olrt.org_id
and to_char(olrt.CREATION_DATE,'yyyymmdd')>=nvl(:p_date_from,to_char(olrt.CREATION_DATE,'yyyymmdd'))
and to_char(olrt.CREATION_DATE,'yyyymmdd')<=nvl(:p_date_to,to_char(olrt.CREATION_DATE,'yyyymmdd'))
and olrt.LINE_CATEGORY_CODE='RETURN'
and nvl(olrt.CANCELLED_FLAG,'N')='N'
and olrt.BOOKED_FLAG='Y'
)
order by order_number,line_no