主sql
select msib.segment1 item,
a.*,
(select
sum(decode(RT.TRANSACTION_TYPE,'RETURN TO RECEIVING',-1,'RETURN TO VENDOR',-1,1)*nvl(rt.quantity,0)) receipt_qty
from RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll
where 1=1
and RSH.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID= RT.SHIPMENT_LINE_ID
AND (RT.TRANSACTION_TYPE='DELIVER'
or
RT.TRANSACTION_TYPE='RETURN TO VENDOR'
or
RT.TRANSACTION_TYPE='RETURN TO RECEIVING'
)
AND RT.DESTINATION_TYPE_CODE='INVENTORY'
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'))
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 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 ph.vendor_id=a.vendor_id
and ph.po_header_id=pl.po_header_id
and pl.item_id=a.inventory_item_id
and pl.ORG_ID=a.from_ORG_ID
) receipt_qty,
(select sum(nvl(pl.unit_price,0)
*currency_exchange(ph.CURRENCY_CODE,'RMB',ood.SET_OF_BOOKS_ID,sysdate)
*decode(RT.TRANSACTION_TYPE,'RETURN TO RECEIVING',-1,'RETURN TO VENDOR',-1,1)
*nvl(rt.quantity,0)) receipt_amount
from RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
ORG_ORGANIZATION_DEFINITIONS ood,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll
where 1=1
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.TRANSACTION_TYPE='DELIVER'
or
RT.TRANSACTION_TYPE='RETURN TO VENDOR'
or
RT.TRANSACTION_TYPE='RETURN TO RECEIVING'
)
AND RT.DESTINATION_TYPE_CODE='INVENTORY'
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'))
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 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 ph.vendor_id=a.vendor_id
and ph.po_header_id=pl.po_header_id
and pl.item_id=a.inventory_item_id
and pl.ORG_ID=a.from_ORG_ID
) receipt_amount
from (
select to_char(DECODE(OL.LINE_CATEGORY_CODE,'RETURN',ol.CREATION_DATE,ol.ACTUAL_SHIPMENT_DATE),'yyyymm') ship_ym,
opcv.dest_org_id,opcv.dest_org,opcv.dest_org_name,
opcv.from_org_id,opcv.from_org,opcv.from_org_name,
opcv.customer_id,opcv.customer_number,opcv.customer_name,opcv.vendor_id,
ol.inventory_item_id,
sum(DECODE(ol.LINE_CATEGORY_CODE,'RETURN',-ol.ORDERED_quantity,OL.SHIPPED_QUANTITY)) ship_qty,
sum(DECODE(ol.LINE_CATEGORY_CODE,'RETURN',-ol.ORDERed_quantity,OL.SHIPPED_QUANTITY)*nvl(ol.unit_selling_price,0)*
currency_exchange(oh.TRANSACTIONAL_CURR_CODE,'RMB',ood.SET_OF_BOOKS_ID,sysdate)) ship_amount
from oe_order_lines_all ol,
oe_order_headers_all oh,
ORG_ORGANIZATION_DEFINITIONS ood,
otc_po_cust_v opcv
where ood.organization_id=ol.org_id
and DECODE(OL.LINE_CATEGORY_CODE,'RETURN',OL.ORDERED_QUANTITY,ol.shipped_quantity)>0
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 ol.header_id=oh.header_id
and ol.org_id=oh.org_id
and oh.order_number=nvl(:p_pi_number,oh.order_number)
and oh.sold_to_org_id=opcv.customer_id
and oh.org_id=opcv.dest_org_id
and opcv.from_org_id=nvl(:p_from_org_id,opcv.from_org_id)
and opcv.dest_org_id=:p_org_id
group by to_char(DECODE(OL.LINE_CATEGORY_CODE,'RETURN',ol.CREATION_DATE,ol.ACTUAL_SHIPMENT_DATE),'yyyymm'),
opcv.dest_org_id,opcv.dest_org,opcv.dest_org_name,
opcv.from_org_id,opcv.from_org,opcv.from_org_name,
opcv.customer_id,opcv.customer_number,opcv.customer_name,opcv.vendor_id,
ol.inventory_item_id
union all
select distinct to_char(rt.transaction_date,'yyyymm') ship_ym,
opcv.dest_org_id,opcv.dest_org,opcv.dest_org_name,
opcv.from_org_id,opcv.from_org,opcv.from_org_name,
opcv.customer_id,opcv.customer_number,opcv.customer_name,opcv.vendor_id,
pl.item_id inventory_item_id,
0 ship_qty,
0 ship_amount
from RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
OTC_PO_CUST_V opcv,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll
where 1=1
and RSH.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID= RT.SHIPMENT_LINE_ID
AND (RT.TRANSACTION_TYPE='DELIVER'
or
RT.TRANSACTION_TYPE='RETURN TO VENDOR'
or
RT.TRANSACTION_TYPE='RETURN TO RECEIVING'
)
AND RT.DESTINATION_TYPE_CODE='INVENTORY'
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'))
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 ph.po_header_id=pl.po_header_id
and ph.vendor_id=opcv.vendor_id
and opcv.from_org_id=nvl(:p_from_org_id,opcv.from_org_id)
and opcv.dest_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 pl.ORG_ID=opcv.from_org_id
and not exists
(
select 1
from oe_order_lines_all ol,
OTC_PO_CUST_V opcv
where DECODE(OL.LINE_CATEGORY_CODE,'RETURN',OL.ORDERED_QUANTITY,ol.shipped_quantity)>0
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 ol.inventory_item_id=pl.item_id
and ol.sold_to_org_id=opcv.customer_id
and ol.org_id=opcv.dest_org_id
and opcv.from_org_id=nvl(:p_from_org_id,opcv.from_org_id)
and opcv.dest_org_id=:p_org_id
)
) a,
mtl_system_items_b msib
where msib.inventory_item_id=a.inventory_item_id
and msib.organization_id=a.from_org_id

銷貨明細子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 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

收料明細子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 RECEIVING',-(rt.quantity),
'RETURN TO VENDOR',-(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 RSH.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID= RT.SHIPMENT_LINE_ID
AND (RT.TRANSACTION_TYPE='DELIVER'
or
RT.TRANSACTION_TYPE='RETURN TO VENDOR'
or
RT.TRANSACTION_TYPE='RETURN TO RECEIVING'
)
AND RT.DESTINATION_TYPE_CODE='INVENTORY'
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'))
and pv.VENDOR_ID=ph.vendor_id
and ood.ORGANIZATION_ID=ph.ORG_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 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=:from_org_id
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 pl.ORG_ID=:from_ORG_ID