|
|
REM $Header: oeordrpt.sql 110.3 2000/05/05 18:24:08 tbharti noship $
REM +=======================================================================+
REM | Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA |
REM | All rights reserved. |
REM +=======================================================================+
REM | FILENAME |
REM | oeordrpt.sql |
REM | |
REM | DESCRIPTION |
REM | Following information is printed by this script for a given |
REM | Sales Order : |
REM | |
REM | o Order Header Information |
REM | o Order Line Cycle Status |
REM | o Order Line Information |
REM | o Order Line Detail Information |
REM | o Picking Line Information |
REM | o Picking Line Detail Information |
REM | o Order Demand Information |
REM | o Order Supply Information |
REM | o Drop Ship Information |
REM | |
REM | Following information is printed by this script for a given |
REM | Return Material Authorization : |
REM | |
REM | o Order Header Information |
REM | o Return Line Cycle Status |
REM | o Return Line Information |
REM | o Return Line Interface Details |
REM | o Return Line Receipt Details |
REM | |
REM | In addtion to above information, the script also generates a |
REM | list of cancelable lines for the given Sales Order or RMA. |
REM | |
REM | This Listing named "Line Cancellation Report" contains the |
REM | Line No., Shipment No., Item, Qty allowed to cancel, Line Id |
REM | and Reason(if any) for NOT being allowed to cancel the line. |
REM | |
REM | The Reason will be based on one of the several System Defined |
REM | Security Rules for Line Cancellation. |
REM | |
REM | Inputs : Order Number ( Required ), Header Id( Required ) |
REM | |
REM | Output : Output of this script is written to an O/S file. |
REM | File Name : <Order Number>_RDA |
REM | |
REM | NOTE |
REM | This script should be used only for Release 11.x |
REM | |
REM | HISTORY |
REM | 11-FEB-2000 Tarun Bharti Created |
REM +=======================================================================+
clear buffer;
set ver off
set feed off
set pagesize 1000
set underline '='
set serveroutput on size 100000
set linesize 138
col hdr_id format 999999990 heading 'Header Id';
col ord_num format 999999990 heading 'Order|Number';
col ord_typ format a25 heading 'Order Type';
col ord_cat format a8 heading 'Order|Category';
col date_ordered format a9 heading 'Order|Date';
col cycle_id format 999990 heading 'Cycle Id';
col customer_id format 9999990 heading 'Customer';
col ossc format a11 heading 'Order|Source Code';
col osr format a14 heading 'Order |Source No.';
col enter format a6 heading 'Enter';
col close format a8 heading 'Close';
col cancel format a8 heading 'Cancel';
undefine def_hdr_id
col hdr_id new_value def_hdr_id
Prompt
Prompt IMPORTANT : User must read associated README prior to using this script.
Prompt
accept order_number num prompt 'Please Enter Order Number (Required) : '
--- spool &order_number
def c1='_'
def pref='RDA'
def out_file='&order_number&c1&pref'
spool &out_file;
ttitle center 'Header Information' skip 1 -
center '<====================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======= : ===========' skip 1 -
left ' O : Open Flag' skip 2
select h.header_id hdr_id,
h.order_number ord_num,
ot.name ord_typ,
decode (h.original_system_source_code,
'2', 'Copy',
'3', 'Service Req',
h.original_system_source_code) ossc,
h.original_system_reference osr,
h.cycle_id,
h.customer_id,
h.date_ordered,
decode( h.order_category,
'P', 'Internal'
, 'R', 'Regular'
, 'RMA', 'Return'
, h.order_category ) ord_cat,
decode (h.s1,
15, 'Enter',
1, 'Booked',
5, 'Partial',
s1) enter,
decode (h.s6,
18, 'Eligible',
10, 'Closed',
s6) close,
decode (h.s4,
18, 'Eligible',
11, 'Complete',
5, 'Partial',
s9) cancel,
open_flag
from so_headers_all h, so_order_types_all ot
where h.order_number = &order_number
and h.order_type_id = ot.order_type_id;
Prompt
declare
n_cnt number;
n_ord number := &order_number;
begin
select count(*)
into n_cnt
from so_headers_all
where order_number = n_ord;
if n_cnt = 0 then
null;
elsif n_cnt > 1 then
dbms_output.put_line('Warning : Multiple Orders exist, enter the Header Id or choose default Header Id '||ltrim(&def_hdr_id)||'.');
else
dbms_output.put_line('Only One order with this Number exist, choose default Header Id '||ltrim(&def_hdr_id)||'.');
end if;
end;
/
Prompt
accept ord_hdr_id char -
prompt 'Please Enter Header Id (See above) OR Press Enter to Choose Default : '
set linesize 141
col line format 999999990 heading 'Line Id';
col s_type format a6 heading 'Source|Type';
col demand format a8 heading 'Demand|Iface';
col mfgrel format a12 heading 'Mfg|Release';
col service format a8 heading 'Service|Iface';
col pickrel format a9 heading 'Pick|Release';
col purrel format a9 heading 'Purchase|Release';
col backord format a8 heading 'Back Ord|Release';
col ship format a11 heading 'Ship|Confirm';
col raiface format a8 heading 'Rec|Iface';
col inviface format a8 heading 'INV|Iface';
col org_id format 99990 heading 'Org Id';
ttitle center 'Order Line Cycle Status' skip 1 -
center '<=========================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======= : ===========' skip 1 -
left ' O : Open Flag' skip 2
select line_id line,
substr(source_type_code, 1, 3) s_type,
decode (s1,
15, 'Enter',
1, 'Booked',
5, 'Partial',
s1) enter,
decode (s28,
18, 'Eligible',
8, 'N/A',
14, 'Ifaced',
s28) demand,
decode (s27,
18, 'Eligible',
8, 'N/A',
4, 'Released',
19, 'WO Complete',
20, 'WO Partial',
23, 'Itm Created',
21, 'WO Open',
s27) mfgrel,
decode (s25,
18, 'Eligible',
8, 'N/A',
14, 'Ifaced',
s25) service,
decode (s2,
18, 'Eligible',
4, 'Released',
5, 'Partial',
8, 'N/A',
s2) pickrel,
decode (s26,
18, 'Eligible',
4, 'Released',
5, 'Partial',
8, 'N/A',
6, 'Confirmed',
14, 'Ifaced',
24, 'N/A',
s26) purrel,
decode (s3,
18, 'Eligible',
4, 'Released',
5, 'Partial',
8, 'N/A',
s3) backord,
decode (s4,
18, 'Eligible',
6, 'Confirmed',
7, 'BO Complete',
5, 'Partial',
22, 'BO Partial',
8, 'N/A',
s4) ship,
decode (s5,
18, 'Eligible',
8, 'N/A',
5, 'Partial',
9, 'Ifaced',
s5) raiface,
decode (s8,
18, 'Eligible',
5, 'Partial',
8, 'N/A',
14, 'Ifaced',
13, 'Error',
s8) inviface,
decode (s6,
18, 'Eligible',
10, 'Closed',
s6) close,
decode (s9,
18, 'Eligible',
11, 'Complete',
5, 'Partial',
s9) cancel,
open_flag,
org_id
from so_lines_all
where header_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and line_type_code <> 'RETURN'
order by ship_set_number,
NVL(parent_line_id, line_id),
NVL(ato_line_id, line_id),
NVL(link_to_line_id, line_id),
line_id;
set linesize 141
col spline format 999999990 heading 'Service|Line Id';
col p_line format 999999990 heading 'Parent|Line Id';
col a_line format 999999990 heading 'ATO|Line Id';
col s_line format 999999990 heading 'Shipment|Line Id';
col l_line format 999999990 heading 'Link To|Line Id';
col l_type format a4 heading 'Line|Type';
col i_type format a4 heading 'Item|Type';
col ordered format 9999990 heading 'Ordered|Quantity';
col shipped format 9999990 heading 'Shipped|Quantity';
col canceled format 9999990 heading 'Canceled|Quantity';
col invoiced format 9999990 heading 'Invoiced|Quantity';
col qti format 9999990 heading 'Qty To|Invoice';
col item format 999999990 heading 'Item Id';
col ssn format 90 heading 'SSN';
ttitle center 'Order Line Information' skip 1 -
center '<========================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======= : ===================' skip 1 -
left ' A : ATO Flag' skip 1 -
left ' O : Option Flag' skip 1 -
left ' SSN : Ship Set Number' skip 1 -
left ' S : Ship Model Complete' skip 2
select line_id line,
nvl(shipment_schedule_line_id, 0) s_line,
nvl(parent_line_id,0) p_line,
nvl(service_parent_line_id,0) spline,
nvl(ato_line_id, 0) a_line,
nvl(link_to_line_id,0) l_line,
substr(line_type_code, 1, 3) l_type,
inventory_item_id item,
substr(item_type_code, 1, 3) i_type,
ato_flag,
option_flag,
ship_set_number ssn,
ship_model_complete_flag,
ordered_quantity ordered,
shipped_quantity shipped,
cancelled_quantity canceled,
quantity_to_invoice qti,
invoiced_quantity invoiced
from so_lines_all
where header_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and line_type_code <> 'RETURN'
order by ship_set_number,
NVL(parent_line_id, line_id),
NVL(ato_line_id, line_id),
NVL(link_to_line_id, line_id),
line_id;
set linesize 141
col l_det format 999999990 heading 'Detail Id';
col dlv format 9999990 heading 'Delivery';
col qty format 9999990 heading 'Quantity';
col res_q format 9999990 heading 'WIP|Reserve';
col comp_q format 9999990 heading 'WIP|Complete';
col ratio format 9990 heading 'Ratio';
col ssc format a10 heading 'Schedule|Status';
col whse format 9990 heading 'Ware-|house';
col sub format a10 heading 'Sub|Inventory';
col s_date format a6 heading 'Sch Dt';
col dlv_id format 9999990 heading 'Delivery|Id';
col dep_id format 9999990 heading 'Depart|Id';
ttitle center 'Order Line Details' skip 1 -
center '<====================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======= : =========================' skip 1 -
left ' I : Included Item Flag' skip 1 -
left ' C : Configuration Item Flag' skip 1 -
left ' S : Shippable Flag' skip 1 -
left ' T : OE Transactable Flag' skip 1 -
left ' R : Reservable Flag' skip 1 -
left ' Q : ReQuired for Revenue Flag' skip 1 -
left ' L : ReLeased Flag' skip 1 -
left ' Sch Dt : Schedule Date' skip 2
select ld.line_detail_id l_det,
l.line_id line,
ld.delivery dlv,
ld.inventory_item_id item,
ld.included_item_flag,
nvl(ld.configuration_item_flag,'N') c,
ld.shippable_flag,
ld.transactable_flag,
ld.reservable_flag,
ld.required_for_revenue_flag q,
ld.quantity qty,
ld.component_ratio ratio,
ld.wip_reserved_quantity res_q,
ld.wip_completed_quantity comp_q,
ld.warehouse_id whse,
ld.subinventory sub,
substr(ld.schedule_status_code, 1, 10) ssc,
to_char(ld.schedule_date,'DD-MON') s_date,
ld.released_flag l,
ld.departure_id dep_id,
ld.delivery_id dlv_id
from so_line_details ld, so_lines_all l
where ld.line_id = l.line_id
and l.header_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
order by l.ship_set_number,
NVL(l.parent_line_id, l.line_id),
NVL(l.ato_line_id, l.line_id),
NVL(l.link_to_line_id, l.line_id),
ld.line_id,
ld.inventory_item_id,
ld.included_item_flag,
ld.warehouse_id,
ld.schedule_date;
set linesize 175
col batch format 99999990 heading 'Batch Id';
col pick_hdr format a10 heading 'Picking|Header Id';
col pick_lin format 999999990 heading 'Picking|Line Id';
col orq format 9999990 heading 'Original|Req Qty';
col rq format 9999990 heading 'Request Qty';
col ph_sta format a14 heading 'Picking|Header Status';
col inv_sta format a14 heading 'Inventory|Status';
col ra_sta format a9 heading 'Receivable|Status';
ttitle center 'Picking Lines' skip 1 -
center '<===============>' skip 2
select ph.batch_id batch,
decode (pl.picking_header_id,
0, 'BO',
pl.picking_header_id) pick_hdr,
pl.order_line_id line,
pl.line_detail_id l_det,
pl.picking_line_id pick_lin,
pl.original_requested_quantity orq,
pl.requested_quantity rq,
pl.shipped_quantity shipped,
pl.cancelled_quantity canceled,
pl.invoiced_quantity invoiced,
to_char(pl.schedule_date,'DD-MON') s_date,
pl.inventory_item_id item,
pl.warehouse_id whse,
ph.delivery_id dlv_id,
decode(ph.status_code,
'BACKORDER RELEASED', 'BACKORDER REL',
ph.status_code) ph_sta,
pl.inventory_status inv_sta,
pl.ra_interface_status ra_sta
from so_picking_lines_all pl, so_picking_headers_all ph
where ph.picking_header_id(+) = pl.picking_header_id
and pl.order_line_id in
(select line_id
from so_lines_all
where header_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id'))
order by pl.picking_line_id,
pl.order_line_id,
pl.component_code,
decode(pl.picking_header_id,
0, 99999,
pl.picking_header_id),
pl.picking_line_id;
set linesize 180
col pick_slip format 99999990 heading 'Pick Slip|Number';
col pld_id format 999999990 heading 'Picking|Detail Id';
col dem_id format 999999990 heading 'Demand Id';
ttitle center 'Picking Line Details' skip 1 -
center '<======================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======= : ====================' skip 1 -
left ' L : ReLeased Flag' skip 1 -
left ' T : OE Transactable Flag' skip 1 -
left ' A : Autoscheduled Flag' skip 2
select ph.batch_id batch,
decode (pl.picking_header_id,
0, 'BO',
pl.picking_header_id) pick_hdr,
pld.pick_slip_number pick_slip,
pl.picking_line_id pick_lin,
pld.picking_line_detail_id pld_id,
pl.order_line_id line,
pl.line_detail_id l_det,
pld.requested_quantity rq,
pld.shipped_quantity shipped,
pl.inventory_item_id item,
pl.warehouse_id whse,
pld.subinventory sub,
pld.delivery dlv,
pld.released_flag l,
pld.transactable_flag t,
pld.autoscheduled_flag a,
pld.demand_id dem_id,
substr(pld.schedule_status_code, 1, 10) ssc,
pld.departure_id dep_id,
pld.delivery_id dlv_id
from so_picking_lines_all pl, so_picking_headers_all ph,
so_picking_line_details pld
where ph.picking_header_id(+) = pl.picking_header_id
and pl.order_line_id in
(select line_id
from so_lines_all
where header_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id'))
and pld.picking_line_id = pl.picking_line_id
order by pl.picking_line_id,
pld.picking_line_detail_id,
pl.order_line_id,
pl.component_code,
decode(pl.picking_header_id,
0, 99999,
pl.picking_header_id),
pl.picking_line_id;
col dem_id format 99999990 heading 'Demand Id';
col p_dem_id format a10 heading 'Parent|Demand Id';
col dst format 90;
col dsh format 9999990;
col dsl format a10;
col dsd format a10;
col ssh format 9999990;
col dt format 0;
col rt format 0;
col line_qty format 9999990;
col lres_qty format 9999990;
col pri_qty format 9999990;
col res_qty format 9999990;
col comp_qty format 9999990;
col unit format a3 heading 'UOM';
col lot format a10 heading 'Lot Number';
col loc format 9999990 heading 'Locator|Id';
col cs format 90;
col ad format 9999990;
ttitle center 'Order Demand Information' skip 1 -
center '<==========================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======== : =========================' skip 1 -
left ' DST : Demand Source Type' skip 1 -
left ' DSH : Demand Source Header ID' skip 1 -
left ' DSL : Demand Source Line' skip 1 -
left ' DSD : Demand Source Delivery' skip 1 -
left ' RT : Reservation Type' skip 1 -
left ' DT : Demand Type' skip 1 -
left ' SSH : Supply Source Header ID ' skip 1 -
left 'LINE_QTY : Line Item Qty' skip 1 -
left 'LRES_QTY : Line Item Reservation Qty ' skip 1 -
left ' RES_QTY : Reservation Qty' skip 1 -
left ' PRI_QTY : Primary UOM Qty' skip 1 -
left 'COMP_QTY : Completed Quantity' skip 1 -
left ' CS : Configuration Status' skip 1 -
left ' AD : Autodetail Group ID' skip 2
select demand_id dem_id,
nvl(to_char(parent_demand_id), 'Summary') p_dem_id,
demand_source_type dst,
demand_source_header_id dsh,
demand_source_line dsl,
demand_source_delivery dsd,
reservation_type rt,
demand_type dt,
supply_source_header_id ssh,
inventory_item_id item,
organization_id whse,
subinventory sub,
lot_number lot,
locator_id loc,
line_item_quantity line_qty,
line_item_reservation_qty lres_qty,
primary_uom_quantity pri_qty,
reservation_quantity res_qty,
completed_quantity comp_qty,
uom_code unit,
config_status cs,
autodetail_group_id ad
from mtl_demand
where demand_source_line in
( select line_id
from so_lines_all
where header_id = decode ('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id'))
and demand_source_type in (2,8)
and line_item_quantity > 0
order by
demand_source_line,
demand_source_delivery,
inventory_item_id,
nvl(parent_demand_id,0),
demand_id;
set linesize 95
col req_hdr format 99999990 heading 'Req|Header Id';
col req_lin format 99999990 heading 'Req|Line Id';
col from_org format 99990 heading 'From|Org Id';
col from_sub format a10 heading 'From Sub|Inventory';
col to_org format 99990 heading 'To|Org Id';
col to_sub format a10 heading 'To Sub|Inventory';
col uom format a6 heading 'UOM';
ttitle center 'Order Supply Information' skip 1 -
center '<==========================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======== : ===========' skip 1 -
left ' C : Change Flag' skip 2
select ms.req_header_id req_hdr,
ms.req_line_id req_lin,
l.line_id line,
ms.item_id item,
ms.quantity qty,
ms.unit_of_measure uom,
ms.from_organization_id from_org,
ms.from_subinventory from_sub,
ms.to_organization_id to_org,
ms.to_subinventory to_sub,
ms.change_flag
from mtl_supply ms,
po_requisition_lines_all prl, po_requisition_headers_all prh,
so_headers_all h, so_lines_all l
where h.header_id = decode ('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and h.order_category = 'P'
and l.header_id = h.header_id
and prh.segment1 = h.original_system_reference
and prl.line_num = l.original_system_line_reference
and prl.requisition_header_id = prh.requisition_header_id
and ms.supply_type_code = 'REQ'
and ms.req_line_id = prl.requisition_line_id;
set linesize 95
col doc_type format a12 heading 'Document|Type';
col po_req_hdr format 99999990 heading 'PO Req|Header Id';
col po_req_num format a10 heading 'PO Req No.';
col lin_num format 99990 heading 'Line No.';
col sup_item format a15 heading 'Supplier Item';
col received format 9999990 heading 'Received|Quantity';
col uom format a6 heading 'UOM';
ttitle center 'Drop Ship Information' skip 1 -
center '<==========================>' skip 2
select sdss.line_id line,
'Purchase Ord' doc_type,
poh.po_header_id po_req_hdr,
poh.segment1 po_req_num,
pol.line_num lin_num,
pol.vendor_product_num sup_item,
pol.quantity ordered,
poll.quantity_received received,
pol.unit_meas_lookup_code uom
from po_headers_all poh, po_lines_all pol,
po_line_locations_all poll, so_drop_ship_sources sdss
where sdss.header_id = decode ('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and poh.po_header_id = sdss.po_header_id
and pol.po_line_id = sdss.po_line_id
and poll.line_location_id = sdss.line_location_id
and sdss.po_release_id is null
union
select sdss.line_id line,
'Purchase Req'doc_type,
prh.requisition_header_id po_req_hdr,
prh.segment1 po_req_num,
prl.line_num lin_num,
prl.suggested_vendor_product_code sup_item,
prl.quantity ordered,
to_number(null),
prl.unit_meas_lookup_code unit
from po_requisition_headers_all prh, po_requisition_lines_all prl,
so_drop_ship_sources sdss
where sdss.header_id = decode ('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and prh.requisition_header_id = sdss.requisition_header_id
and prl.requisition_line_id = sdss.requisition_line_id
and sdss.po_line_id is null
union
select sdss.line_id line,
'Blanket Rel' doc_type,
por.po_release_id po_req_hdr,
poh.segment1 po_req_num,
pol.line_num lin_num,
pol.vendor_product_num sup_item,
poll.quantity ordered,
poll.quantity_received received,
pol.unit_meas_lookup_code unit
FROM po_headers_all poh, po_releases_all por,
po_lines_all pol, po_line_locations_all poll,
so_drop_ship_sources sdss
where sdss.header_id = decode ('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and poh.po_header_id = sdss.po_header_id
and pol.po_line_id = sdss.po_line_id
and poll.line_location_id = sdss.line_location_id
and por.po_release_id = sdss.po_release_id;
set linesize 80
col rma format a10 heading 'RMA|Iface';
ttitle center 'Return Line Cycle Status' skip 1 -
center '<=========================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======= : ===========' skip 1 -
left ' O : Open Flag' skip 2
select line_id line,
substr(source_type_code, 1, 3) s_type,
decode (s1,
15, 'Enter',
1, 'Booked',
5, 'Partial',
s1) enter,
decode (s29,
18, 'Eligible',
8, 'N/A',
14, 'Ifaced',
16, 'P Accepted',
17, 'C Accepted',
s29) rma,
decode (s5,
18, 'Eligible',
8, 'N/A',
5, 'Partial',
9, 'Ifaced',
s5) raiface,
decode (s8,
18, 'Eligible',
5, 'Partial',
8, 'N/A',
14, 'Ifaced',
13, 'Error',
s8) inviface,
decode (s6,
18, 'Eligible',
10, 'Closed',
s6) close,
decode (s9,
18, 'Eligible',
11, 'Complete',
5, 'Partial',
s9) cancel,
open_flag,
org_id
from so_lines_all
where header_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and line_type_code = 'RETURN'
order by line_number;
set linesize 105
col accepted format 9999990 heading 'Accepted|Quantity';
col ref_code format a8 heading 'Return|Ref Code';
col ref_id format 9999990 heading 'Reference|Id';
ttitle center 'Return Line Information' skip 1 -
center '<=========================>' skip 2
select line_id line,
substr(source_type_code, 1, 3) s_type,
substr(line_type_code, 1, 3) l_type,
substr(item_type_code, 1, 3) i_type,
inventory_item_id item,
ordered_quantity ordered,
shipped_quantity accepted,
cancelled_quantity canceled,
quantity_to_invoice qti,
invoiced_quantity invoiced,
return_reference_type_code ref_code,
return_reference_id ref_id
from so_lines_all
where header_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and line_type_code = 'RETURN'
order by line_number;
set linesize 70
col iface format 99999990 heading 'Interface|Id';
col delivered format 99999990 heading 'Delivered|Quantity';
col quantity format 99999990 heading 'Quantity';
ttitle center 'Return Line Interface Details' skip 1 -
center '<===============================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======= : ===========' skip 1 -
left ' C : Closed Flag' skip 2
select rma_line_id line,
rma_interface_id iface,
inventory_item_id item,
quantity,
received_quantity received,
delivered_quantity delivered,
unit_code unit,
closed_flag
from mtl_so_rma_interface
where rma_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
order by rma_line_id;
set linesize 95
col receipt format 99999990 heading 'Receipt|Id';
col delivered format 99999990 heading 'Accepted|Quantity';
col receipt_date format a9 heading 'Receipt|Date';
ttitle center 'Return Line Receipt Details' skip 1 -
center '<===============================>' skip 1 -
left 'Heading : Description' skip 1 -
left '======= : ===========' skip 1 -
left ' C : Closed Flag' skip 2
select mti.rma_line_id line,
mtr.rma_interface_id iface,
mtr.rma_receipt_id receipt,
mtr.inventory_item_id item,
mtr.received_quantity received,
mtr.accepted_quantity accepted,
mtr.unit_code unit,
mtr.receipt_date,
mtr.organization_id whse,
mtr.return_subinventory_name sub,
mtr.closed_flag
from mtl_so_rma_interface mti, mtl_so_rma_receipts mtr
where mti.rma_id = decode('&ord_hdr_id', null, '&def_hdr_id', '&ord_hdr_id')
and mtr.rma_interface_id = mti.rma_interface_id;
set linesize 80
DECLARE
n_ord_hdr_id number;
n_order_exists varchar2(1) := 'N';
n_line_exists varchar2(1) := 'N';
n_item varchar2(30);
n_report_title varchar2(80);
n_qty_to_cancel number;
n_err_code varchar2(30);
n_err_desc varchar2(100);
/*
** Selects Order Information
*/
cursor order_info
is
select
h.order_number
, h.order_category
, decode(h.order_category, 'RMA', 'Return', 'Sales Order') category_desc
, h.header_id
, h.org_id
from so_headers_all h
where h.header_id = n_ord_hdr_id;
/*
** Selects Order Line Information
*/
cursor line_info (c_header_id number)
is
select
line_id
, line_number line_num
, shipment_number shipment_num
, allowed_to_cancel
, revenue_amount
, s5
, s8
, s25
, item_id
from
(select
oe_query.base_line_number( sl.parent_line_id
, sl.service_parent_line_id
, sl.shipment_schedule_line_id
, sl.line_number ) line_number
, to_number( decode( sl.shipment_schedule_line_id
, null, null
, sl.line_number ) ) shipment_number
, sl.ordered_quantity - nvl(sl.cancelled_quantity, 0)
- greatest(nvl(sl.invoiced_quantity, 0), nvl(sl.shipped_quantity, 0))
allowed_to_cancel
, sl.line_id
, sl.revenue_amount
, sl.s5
, sl.s8
, sl.s25
, sl.inventory_item_id item_id
from so_lines_all sl
where sl.header_id = c_header_id
and sl.parent_line_id is null
and sl.service_parent_line_id is null
and sl.line_type_code in ('REGULAR','DETAIL','RETURN'))
order by line_number, nvl(shipment_number, 0);
/*
** Get Item Name and Description
*/
cursor item_info (c_item_id number)
is
select
substr(msi.concatenated_segments||' - '||msi.description, 1, 30) item
from mtl_system_items_kfv msi
where msi.inventory_item_id = c_item_id
and rownum = 1;
/*
** Inventory Interface cycle status is set to Interfaced
** for one of the option components in the configuration
*/
cursor inv_iface_config (c_line_id number)
is
select 'Y'
from so_lines
where parent_line_id = c_line_id
and s8 = 14
and rownum <= 1;
/*
** Manufacturing Release cycle status is set to Work
** Order Open for the line or one of its components
*/
cursor wo_open_ato_config (c_line_id number)
is
select 'Y'
from so_lines
where (line_id = c_line_id
or ato_line_id = c_line_id)
and s27 = 21
and rownum <= 1;
/*
** If Model or one of its component(s) have already
** been Shipped/Invoiced, cancellation is complex
** and will not be allowed thru' the generic script.
*/
cursor model_ship_invoice (c_line_id number)
is
select 'Y'
from so_lines
where item_type_code = 'MODEL'
and (line_id = c_line_id
or parent_line_id = c_line_id)
and (nvl(shipped_quantity, 0) > 0
or nvl(invoiced_quantity, 0) > 0)
and rownum <= 1;
BEGIN
if nvl('&ord_hdr_id', null) is null then
if nvl('&def_hdr_id', null) is null then
dbms_output.put_line(chr(10)||'Error : Order doesn''t exist, re-run script and enter correct Number.'||chr(10));
goto end_block;
else
n_ord_hdr_id := to_number('&def_hdr_id');
end if;
else
n_ord_hdr_id := to_number('&ord_hdr_id');
end if;
for oi_rec in order_info loop
n_order_exists := 'Y';
fnd_client_info.set_org_context(oi_rec.org_id);
n_report_title := ' Line Cancellation Report for '||oi_rec.category_desc||' No. '||oi_rec.order_number||' ';
n_report_title := lpad(rpad(n_report_title, length(n_report_title) + trunc((80-length(n_report_title))/2), '*'), 78, '*');
for li_rec in line_info(oi_rec.header_id) loop
if n_line_exists = 'N' then
dbms_output.put_line(chr(10)||n_report_title||chr(10));
dbms_output.put_line('+--------+--------+------------------------------+--------------+--------------+');
dbms_output.put_line('|Line No.|Shipment|Item Name - Description | Qty To Cancel| Line Id|');
dbms_output.put_line('+--------+--------+------------------------------+--------------+--------------+');
n_line_exists := 'Y';
end if;
/* Get the Item Info */
for ii_rec in item_info(li_rec.item_id) loop
n_item := ii_rec.item;
end loop;
/* Check Security Rules based on the Order Category (Sales Order or RMA) */
if oi_rec.order_category = 'RMA' then
/*
** Checking Security Rules which disallow RMA Line Cancelltion.
**
** 1. Cycle Status Receivables Interface -> Interfaced for RMA Line.
** Current Line is Interfaced to Receivables.
** Cancellation disallowed to ensure data integrity between OE and
** Receivables Module.
*/
if n_err_code is null then
if nvl(li_rec.s5, -1) = 9 then
n_err_code := 'REC_IFACE_RMA_LINE';
end if;
end if;
else
/*
** Checking Security Rules which disallow Sales Order Line Cancelltion.
**
** 1. Cycle Status Inventory Interface -> Interfaced for Configuration.
** Optional component(s) in the configuration are Interfaced to Inventory.
** Cancellation disallowed to ensure data integrity between OE and INV.
*/
if n_err_code is null then
for rec1 in inv_iface_config(li_rec.line_id) loop
n_err_code := 'INV_IFACE_CONFIG';
end loop;
end if;
/*
** 2. Cycle Status Inventory Interface -> Interfaced for Line/Shipment.
** Current Line/Shipment is Interfaced to Inventory.
** Cancellation disallowed to ensure data integrity between OE and INV.
*/
if n_err_code is null then
if nvl(li_rec.s8, -1) = 14 then
n_err_code := 'INV_IFACE_LINE_SHIPMENT';
end if;
end if;
/*
** 3. Prorated Prices Exist
** Receivables Interface has updated the lines with prorated prices.
** Cancellation disallowed to ensure data integrity between OE and
** Receivables Module.
*/
if nvl(li_rec.revenue_amount, 0) > 0 then
n_err_code := 'PRORATED_PRICES';
end if;
/*
** 4. Sales Order is Closed.
** Although Sales Order is closed we can still allow cancellation of a line
** if the line is open because logically the order would have closed only if
** all its lines were closed.
**
** 5. Cycle Status Manufacturing Release -> W/O Open for ATO Configuration.
** Current Line or its component(s) has W/O Open
** Cancellation disallowed to ensure data integrity between OE and WIP.
*/
if n_err_code is null then
for rec2 in wo_open_ato_config(li_rec.line_id) loop
n_err_code := 'WO_OPEN_ATO_CONFIG';
end loop;
end if;
/*
** 6. Cycle Status Service Interface -> Interfaced for Line.
** If Service lines of the current line have been Interfaced to Service.
** Cancellation disallowed to ensure data integrity between OE and Service.
*/
if n_err_code is null then
if nvl(li_rec.s25, -1) = 14 then
n_err_code := 'SER_IFACE_LINE';
end if;
end if;
if n_err_code is null then
for rec3 in model_ship_invoice(li_rec.line_id) loop
n_err_code := 'MODEL_SHIP_INVOICE';
end loop;
end if;
end if ; /* Checking Category Code */
if n_err_code is not null then
n_qty_to_cancel := 0;
if n_err_code = 'INV_IFACE_CONFIG' then
n_err_code := null;
n_err_desc := rpad('Option(s) have already been Interfaced to Inventory.', 70)||'|';
elsif n_err_code = 'INV_IFACE_LINE_SHIPMENT' then
n_err_code := null;
n_err_desc := rpad('Line/Shipment has already been Interfaced to Inventory.', 70)||'|';
elsif n_err_code = 'WO_OPEN_ATO_CONFIG' then
n_err_code := null;
n_err_desc := rpad('Work Order is open for ATO Line or Option(s).', 70)||'|';
elsif n_err_code = 'SER_IFACE_LINE' then
n_err_code := null;
n_err_desc := rpad('Line/Shipment has already been Interfaced to Service.', 70)||'|';
elsif n_err_code = 'MODEL_SHIP_INVOICE' then
n_err_code := null;
n_err_desc := rpad('Model or Option(s) have already been Shipped/Invoiced.', 70)||'|';
elsif n_err_code = 'PRORATED_PRICES' then
n_err_desc := null;
elsif n_err_code = 'REC_IFACE_RMA_LINE' then
n_err_code := null;
n_err_desc := rpad('Return Line has already been Interfaced to Receivables.', 70)||'|';
end if;
else
n_qty_to_cancel := li_rec.allowed_to_cancel;
end if;
dbms_output.put_line('|'||lpad(to_char(li_rec.line_num), 8)||
'|'||lpad(nvl(to_char(li_rec.shipment_num), ' '), 8)||
'|'||rpad(n_item, 30)||
'|'||lpad(to_char(n_qty_to_cancel), 14)||
'|'||lpad(to_char(li_rec.line_id), 14)||'|');
if n_err_desc is not null then
dbms_output.put_line('+--------+--------+------------------------------+--------------+--------------+');
dbms_output.put_line( rpad('|Cancelling above line NOT allowed.', 79)||'|');
dbms_output.put_line( '|Reason: '||n_err_desc);
n_err_desc := null;
end if;
dbms_output.put_line('+--------+--------+------------------------------+--------------+--------------+');
end loop;
if n_err_code = 'PRORATED_PRICES' then
n_err_desc := rpad('Prorated Price exist on above line(s).', 70)||'|';
dbms_output.put_line( rpad('|Cancelling above line(s) NOT allowed.', 79)||'|');
dbms_output.put_line( '|Reason: '||n_err_desc);
dbms_output.put_line('+------------------------------------------------------------------------------+');
end if;
if n_line_exists = 'Y' then
dbms_output.put_line(chr(10)||'IMPORTANT, Please Read ...');
dbms_output.put_line('==========================');
dbms_output.put_line('File '||'&out_file'||'.lst containing above Output created in USER''s current directory.');
end if;
end loop;
if n_order_exists = 'N' then
dbms_output.put_line (chr(10));
dbms_output.put_line('ERROR : Incorrect Header Id, please rerun the script and enter correct Id.');
elsif n_line_exists = 'N' then
dbms_output.put_line (chr(10));
dbms_output.put_line('Order does not have any Line(s).');
end if;
<<END_BLOCK>>
NULL;
END;
/
spool off;
clear columns;
clear sql;
ttitle off;
set underline '-';
执行一下看看结果是什么？ |
|
楼主: melaney
IP卡
狗仔卡
发表于 2007-10-17 11:37
显身卡
发表于 2008-2-29 18:15