Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
FROM oe_order_headers_all oha ,oe_order_lines_all ola
WHERE oha.header_id=ola.header_id
and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
--and trunc(ola.schedule_ship_date) is not null
and ola.CANCELLED_FLAG <> 'Y'
and rownum=1) NEED_BY_dATE,
NULL customer_num,
NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id (+) =rsl.shipment_line_id
and rcv.shipment_header_id (+) =rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is null
and trunc(ve.shipped_date) <= :P_TO_DATE
AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
rsl.shipment_line_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,ve.shipment_num,
ve.from_organization_name,
ve.shipped_date
union all
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE)--INTO :CP_NEED_BY_DATE
FROM oe_order_headers_all oha ,oe_order_lines_all ola
WHERE oha.header_id=ola.header_id
and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
--and trunc(ola.schedule_ship_date) is not null
and ola.CANCELLED_FLAG <> 'Y'
and rownum=1) NEED_BY_dATE,
NULL customer_num,
NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id=rsl.shipment_line_id
and rcv.shipment_header_id=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
and rcv.TRANSACTION_TYPE='DELIVER' and trunc(rcv.TRANSACTION_DATE) > :P_TO_DATE and trunc(ve.shipped_date) <= :P_TO_DATE
AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
rsl.shipment_line_id,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id
UNION ALL
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
FROM oe_order_headers_all oha ,oe_order_lines_all ola
WHERE oha.header_id=ola.header_id
and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
--and trunc(ola.schedule_ship_date) is not null
and ola.CANCELLED_FLAG <> 'Y'
and rownum=1) NEED_BY_dATE,
NULL customer_num,
NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS NULL
AND trunc(ve.shipped_date) <= :P_TO_DATE
AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
rsl.shipment_line_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id
union all
Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
FROM oe_order_headers_all oha ,oe_order_lines_all ola
WHERE oha.header_id=ola.header_id
and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
--and trunc(ola.schedule_ship_date) is not null
and ola.CANCELLED_FLAG <> 'Y'
and rownum=1) NEED_BY_dATE,
NULL customer_num,
NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS not NULL
and rcv.shipment_line_id NOT IN (
SELECT shipment_line_id
FROM rcv_transactions rcv1, rcv_shipment_headers rsh1
WHERE rcv1.shipment_header_id = rsh1.shipment_header_id
AND rcv1.transaction_type IN ('ACCEPT', 'DELIVER','RETURN TO VENDOR'))
AND trunc(ve.shipped_date) <= :P_TO_DATE
AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
rsl.shipment_line_id,
b.inventory_item_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date
union all
SELECT 'Drop Ship' Col_Type,
od.ship_from_org_id organizaiton_id,
od.organization_code,
od.organization_name,
ola.inventory_item_id,
ROUND (wsh.shipped_quantity, 2) qty,
wsh.lot_number,
ola.order_quantity_uom,
ola.ordered_item Item,
(SELECT DISTINCT description
FROM mtl_system_items_kfv
WHERE inventory_item_id =
ola.inventory_item_id
AND organization_id = ola.ship_from_org_id) description,
to_char(wnd.delivery_id) shipent_number,
(select organization_name from org_organization_definitions where organization_id=ola.ship_from_org_id) from_organization_name,
TRUNC (wnd.initial_pickup_date+(10.5/24)) shipped_Date,
null shipment_line_id,
od.REQUEST_DATE need_by_date ,
od.customer_num,
od.customer_name
FROM oe_order_headers_all oha,
oe_order_lines_all ola,
wsh_delivery_details wsh,
wsh_new_deliveries wnd,
wsh_delivery_assignments wdd,
hz_locations hz,
(select ooh.order_number,
ool.line_number
,ool.ordered_item
,ool.ordered_quantity
,ooh.flow_status_code header_status
,ool.flow_status_code line_status
,prha.segment1 requisition
,poh.segment1 po_number
,odss.po_header_id
,poh.closed_code po_status
,pll.quantity
,pll.quantity_received
,ool.REQUEST_DATE
,pll.closed_code po_shipment_status,
ool.ship_from_org_id,
(SELECT DISTINCT ACCOUNT_NUMBER
FROM hz_parties hz, hz_cust_accounts hcu
WHERE hz.party_id = hcu.party_id
AND hcu.cust_account_id = ool.sold_to_org_id)
customer_num,
(SELECT DISTINCT party_name
FROM hz_parties hz, hz_cust_accounts hcu
WHERE hz.party_id = hcu.party_id
AND hcu.cust_account_id = ool.sold_to_org_id)
customer_name,
(select organization_code from org_organization_definitions where organization_id=ool.ship_from_org_id) organization_code,
(select organization_name from org_organization_definitions where organization_id=ool.ship_from_org_id) organization_name
from apps.oe_order_headers_all ooh
,apps.oe_order_lines_all ool
,apps.oe_drop_ship_sources odss
,apps.po_requisition_headers_all prha
,apps.po_headers_all poh
,apps.po_lines_all pol
,apps.po_line_locations_all pll
where ool.header_id = ooh.header_id
and odss.header_id = ooh.header_id
and odss.line_id = ool.line_id
and prha.requisition_header_id = odss.requisition_header_id
and poh.po_header_id = odss.po_header_id
and pol.po_line_id = odss.po_line_id
and pol.po_header_id = poh.po_header_id
and pll.po_line_id = pol.po_line_id
and ool.flow_status_code='AWAITING_RECEIPT'
--and poh.segment1='41102417'
and ooh.org_id=:p_org_id) od
WHERE oha.header_id = ola.header_id
AND ola.line_id = wsh.source_line_id
AND oha.header_id = wsh.source_header_id
AND wsh.delivery_detail_id=wdd.delivery_detail_id
AND wdd.delivery_id=wnd.delivery_id(+)
-- AND wsh.delivery_detail_id = jail.delivery_detail_id
AND hz.location_id=wsh.ship_to_location_id
and wsh.source_line_id not in (select DISTINCT REFERENCE_LINE_ID from oe_order_lines_all
where REFERENCE_LINE_ID is not null)
-- and ola.line_id=1379928
and ola.line_number (+) =od.line_number
and ola.cust_po_number (+) =od.po_number
order by 3,9,7
******************************************************************
Some other fields that can be used in placeholders to print more descriptive information like IR number/ PO Number. Invoice number etc.....
function CF_VALUESFormula return Number is
begin
if :COL_TYPE='Intransit' THEN
------------------IR NUMBER-------------------
BEGIN
SELECT distinct ORIG_SYS_DOCUMENT_REF INTO :CP_IR_NUM
FROM oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM);
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_IR_NUM := NULL;
END;
------------------IR STATUS-------------------
BEGIN
select DISTINCT AUTHORIZATION_STATUS INTO :CP_IR_STATUS from PO_REQUISITION_HEADERS_ALL where segment1 =
(SELECT distinct ORIG_SYS_DOCUMENT_REF
FROM oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM));
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_IR_NUM := NULL;
END;
----------------------------RECEIPT DATE IF REPORT RUN BACK DATED------------------
BEGIN
select transaction_Date into :cp_rec_date
from rcv_transactions
where shipment_line_id=:shipment_line_id
and transaction_type='RECEIVE'
and rownum=1;
EXCEPTION WHEN NO_DATA_FOUND
THEN :cp_rec_date := NULL;
END;
--------------------AR INVOICE NUMBER ---------------------
begin
select trx_number INTO :CP_TRX_NUMBER from ra_customer_trx_all
where to_char(INTERFACE_HEADER_ATTRIBUTE1) in (select to_char(order_number) from oe_order_headers_all
where orig_sys_document_ref in (SELECT ORIG_SYS_DOCUMENT_REF
FROM oe_order_headers_all WHERE HEADER_ID in (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM)))
and rownum=1 ;
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_TRX_NUMBER := NULL;
END;
END IF;
if :COL_TYPE='Drop Ship' THEN
------------------IR NUMBER-------------------
begin
SELECT distinct CUST_PO_NUMBER INTO :CP_IR_NUM
FROM oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM);
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_IR_NUM := NULL;
END;
--------------------AR INVOICE NUMBER ---------------------
begin
select trx_number INTO :CP_TRX_NUMBER from ra_customer_trx_all
where to_char(INTERFACE_HEADER_ATTRIBUTE1) = (select to_char(order_number) from oe_order_headers_all
where orig_sys_document_ref = (SELECT ORIG_SYS_DOCUMENT_REF
FROM oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM)))
and rownum=1 ;
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_TRX_NUMBER := NULL;
END;
RETURN (0);
EXCEPTION
WHEN NO_DATA_FOUND
THEN RETURN(0);
WHEN OTHERS THEN
SRW.MESSAGE(999, 'Error In Formula CF_VALUES : ' || SQLERRM);
RAISE SRW.PROGRAM_ABORT;
end;
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
FROM oe_order_headers_all oha ,oe_order_lines_all ola
WHERE oha.header_id=ola.header_id
and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
--and trunc(ola.schedule_ship_date) is not null
and ola.CANCELLED_FLAG <> 'Y'
and rownum=1) NEED_BY_dATE,
NULL customer_num,
NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id (+) =rsl.shipment_line_id
and rcv.shipment_header_id (+) =rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is null
and trunc(ve.shipped_date) <= :P_TO_DATE
AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
rsl.shipment_line_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,ve.shipment_num,
ve.from_organization_name,
ve.shipped_date
union all
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE)--INTO :CP_NEED_BY_DATE
FROM oe_order_headers_all oha ,oe_order_lines_all ola
WHERE oha.header_id=ola.header_id
and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
--and trunc(ola.schedule_ship_date) is not null
and ola.CANCELLED_FLAG <> 'Y'
and rownum=1) NEED_BY_dATE,
NULL customer_num,
NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id=rsl.shipment_line_id
and rcv.shipment_header_id=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
and rcv.TRANSACTION_TYPE='DELIVER' and trunc(rcv.TRANSACTION_DATE) > :P_TO_DATE and trunc(ve.shipped_date) <= :P_TO_DATE
AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
rsl.shipment_line_id,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id
UNION ALL
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
FROM oe_order_headers_all oha ,oe_order_lines_all ola
WHERE oha.header_id=ola.header_id
and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
--and trunc(ola.schedule_ship_date) is not null
and ola.CANCELLED_FLAG <> 'Y'
and rownum=1) NEED_BY_dATE,
NULL customer_num,
NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS NULL
AND trunc(ve.shipped_date) <= :P_TO_DATE
AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
rsl.shipment_line_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id
union all
Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date,
rsl.shipment_line_id,
(SELECT trunc(ola.REQUEST_DATE) --INTO :CP_NEED_BY_DATE
FROM oe_order_headers_all oha ,oe_order_lines_all ola
WHERE oha.header_id=ola.header_id
and oha.HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=ve.SHIPMENT_NUM)
--and trunc(ola.schedule_ship_date) is not null
and ola.CANCELLED_FLAG <> 'Y'
and rownum=1) NEED_BY_dATE,
NULL customer_num,
NULL customer_name
from rcv_msh_v ve,
rcv_shipment_lines rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS not NULL
and rcv.shipment_line_id NOT IN (
SELECT shipment_line_id
FROM rcv_transactions rcv1, rcv_shipment_headers rsh1
WHERE rcv1.shipment_header_id = rsh1.shipment_header_id
AND rcv1.transaction_type IN ('ACCEPT', 'DELIVER','RETURN TO VENDOR'))
AND trunc(ve.shipped_date) <= :P_TO_DATE
AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
rsl.shipment_line_id,
b.inventory_item_id,
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description,
ve.shipment_num,
ve.from_organization_name,
ve.shipped_date
union all
SELECT 'Drop Ship' Col_Type,
od.ship_from_org_id organizaiton_id,
od.organization_code,
od.organization_name,
ola.inventory_item_id,
ROUND (wsh.shipped_quantity, 2) qty,
wsh.lot_number,
ola.order_quantity_uom,
ola.ordered_item Item,
(SELECT DISTINCT description
FROM mtl_system_items_kfv
WHERE inventory_item_id =
ola.inventory_item_id
AND organization_id = ola.ship_from_org_id) description,
to_char(wnd.delivery_id) shipent_number,
(select organization_name from org_organization_definitions where organization_id=ola.ship_from_org_id) from_organization_name,
TRUNC (wnd.initial_pickup_date+(10.5/24)) shipped_Date,
null shipment_line_id,
od.REQUEST_DATE need_by_date ,
od.customer_num,
od.customer_name
FROM oe_order_headers_all oha,
oe_order_lines_all ola,
wsh_delivery_details wsh,
wsh_new_deliveries wnd,
wsh_delivery_assignments wdd,
hz_locations hz,
(select ooh.order_number,
ool.line_number
,ool.ordered_item
,ool.ordered_quantity
,ooh.flow_status_code header_status
,ool.flow_status_code line_status
,prha.segment1 requisition
,poh.segment1 po_number
,odss.po_header_id
,poh.closed_code po_status
,pll.quantity
,pll.quantity_received
,ool.REQUEST_DATE
,pll.closed_code po_shipment_status,
ool.ship_from_org_id,
(SELECT DISTINCT ACCOUNT_NUMBER
FROM hz_parties hz, hz_cust_accounts hcu
WHERE hz.party_id = hcu.party_id
AND hcu.cust_account_id = ool.sold_to_org_id)
customer_num,
(SELECT DISTINCT party_name
FROM hz_parties hz, hz_cust_accounts hcu
WHERE hz.party_id = hcu.party_id
AND hcu.cust_account_id = ool.sold_to_org_id)
customer_name,
(select organization_code from org_organization_definitions where organization_id=ool.ship_from_org_id) organization_code,
(select organization_name from org_organization_definitions where organization_id=ool.ship_from_org_id) organization_name
from apps.oe_order_headers_all ooh
,apps.oe_order_lines_all ool
,apps.oe_drop_ship_sources odss
,apps.po_requisition_headers_all prha
,apps.po_headers_all poh
,apps.po_lines_all pol
,apps.po_line_locations_all pll
where ool.header_id = ooh.header_id
and odss.header_id = ooh.header_id
and odss.line_id = ool.line_id
and prha.requisition_header_id = odss.requisition_header_id
and poh.po_header_id = odss.po_header_id
and pol.po_line_id = odss.po_line_id
and pol.po_header_id = poh.po_header_id
and pll.po_line_id = pol.po_line_id
and ool.flow_status_code='AWAITING_RECEIPT'
--and poh.segment1='41102417'
and ooh.org_id=:p_org_id) od
WHERE oha.header_id = ola.header_id
AND ola.line_id = wsh.source_line_id
AND oha.header_id = wsh.source_header_id
AND wsh.delivery_detail_id=wdd.delivery_detail_id
AND wdd.delivery_id=wnd.delivery_id(+)
-- AND wsh.delivery_detail_id = jail.delivery_detail_id
AND hz.location_id=wsh.ship_to_location_id
and wsh.source_line_id not in (select DISTINCT REFERENCE_LINE_ID from oe_order_lines_all
where REFERENCE_LINE_ID is not null)
-- and ola.line_id=1379928
and ola.line_number (+) =od.line_number
and ola.cust_po_number (+) =od.po_number
order by 3,9,7
******************************************************************
Some other fields that can be used in placeholders to print more descriptive information like IR number/ PO Number. Invoice number etc.....
function CF_VALUESFormula return Number is
begin
if :COL_TYPE='Intransit' THEN
------------------IR NUMBER-------------------
BEGIN
SELECT distinct ORIG_SYS_DOCUMENT_REF INTO :CP_IR_NUM
FROM oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM);
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_IR_NUM := NULL;
END;
------------------IR STATUS-------------------
BEGIN
select DISTINCT AUTHORIZATION_STATUS INTO :CP_IR_STATUS from PO_REQUISITION_HEADERS_ALL where segment1 =
(SELECT distinct ORIG_SYS_DOCUMENT_REF
FROM oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM));
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_IR_NUM := NULL;
END;
----------------------------RECEIPT DATE IF REPORT RUN BACK DATED------------------
BEGIN
select transaction_Date into :cp_rec_date
from rcv_transactions
where shipment_line_id=:shipment_line_id
and transaction_type='RECEIVE'
and rownum=1;
EXCEPTION WHEN NO_DATA_FOUND
THEN :cp_rec_date := NULL;
END;
--------------------AR INVOICE NUMBER ---------------------
begin
select trx_number INTO :CP_TRX_NUMBER from ra_customer_trx_all
where to_char(INTERFACE_HEADER_ATTRIBUTE1) in (select to_char(order_number) from oe_order_headers_all
where orig_sys_document_ref in (SELECT ORIG_SYS_DOCUMENT_REF
FROM oe_order_headers_all WHERE HEADER_ID in (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM)))
and rownum=1 ;
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_TRX_NUMBER := NULL;
END;
END IF;
if :COL_TYPE='Drop Ship' THEN
------------------IR NUMBER-------------------
begin
SELECT distinct CUST_PO_NUMBER INTO :CP_IR_NUM
FROM oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM);
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_IR_NUM := NULL;
END;
--------------------AR INVOICE NUMBER ---------------------
begin
select trx_number INTO :CP_TRX_NUMBER from ra_customer_trx_all
where to_char(INTERFACE_HEADER_ATTRIBUTE1) = (select to_char(order_number) from oe_order_headers_all
where orig_sys_document_ref = (SELECT ORIG_SYS_DOCUMENT_REF
FROM oe_order_headers_all WHERE HEADER_ID = (SELECT distinct WSH.SOURCE_HEADER_ID FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
wsh_new_deliveries WND,
WSH_DELIVERY_DETAILS WSH
WHERE WSH.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID= WDA.DELIVERY_ID
and wnd.DELIVERY_ID=:SHIPMENT_NUM)))
and rownum=1 ;
EXCEPTION WHEN NO_DATA_FOUND
THEN :CP_TRX_NUMBER := NULL;
END;
RETURN (0);
EXCEPTION
WHEN NO_DATA_FOUND
THEN RETURN(0);
WHEN OTHERS THEN
SRW.MESSAGE(999, 'Error In Formula CF_VALUES : ' || SQLERRM);
RAISE SRW.PROGRAM_ABORT;
end;
No comments:
Post a Comment