|
/* # $Header: HTMomse11i.sql 1.9.1 02/08/02 Support $
#+======================================================================================+
#| Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA |
#| Oracle Support Services. All rights reserved |
#+======================================================================================+
#========================================================================================
# PURPOSE: This script will collect information related to a sales order
# transaction and it's Workflow processes. This script should be
# run whenever there are problems related to a sales order
# transaction, including processes like Pick Release, Shipping,
# Invoice interface.
#
# FILE NAME: HTMomse11i.sql
# FILE VERSION: 7.5.2
# PRODUCT: Oracle Order Management
# PRODUCT VERSIONS: 11.5.0 (and above)
# PLATFORM: Generic
# PARAMETERS: Order_Number, Header_Id (opt), Line_Id (opt)
#========================================================================================
# USAGE: sqlplus apps/apps @HTMomse11i.sql
#
# This script requires a header_id to be passed in as a parameter ONLY if there are
# multiple header_id's for the order requested. If there is only one header_id returned
# after inputting the order number, simply hit enter and this header_id will be assumed.
#
# Entering a line_id is only required when you wish to have output for one order line.
# Otherwise, hit enter without entering a line_id and all lines will be output in the
# script.
#
# The script writes to an output file named HTLomse11i_<ordernumber>.html in the
# current directory. This file may be viewed in any text editor.
*/
set term off;
/*
#=========================================================================================
# CHANGE HISTORY:
31-MAR-00 Created rnmercer
18-APR-00 Modifications to remove TYP.PROCESS_FLOW from joins rnmercer
20-APR-00 Included Source Type in headers rnmercer
15-JUN-00 Changed ATO to OPT and corrected some reference info rnmercer
28-JUN-00 Added Information for Invoicing rnmercer
12-DEC-00 Major modifications to the look of the report including rnmercer
abreviated column names for flags, etc. A legend cross
referencing the abreviations is included in the output.
Got rid of the need to input the org_id. It is set via
derived data from the header_id.
Eliminated the need to type in the header_id if there is
only one order using the order number in question.
13-FEB-01 Replaced Request_date for Promise_date rnmercer
16-FEB-01 Added ROLE/USER information to workflow error statement rnmercer/sgpadman
24-APR-01 Output all lines that reside in the RA Interface Table
o Those linked to LINES on the SALES ORDER/DISCOUNTS
o Those linked to FREIGHT at the LINE Level
o Those linked to FREIGHT at the HEADER Level rnmercer
27-APR-01 Fixed the MTL_TXN_REQUEST_LINES_V to use the rnmercer
TXN_SOURCE_LINE_ID against the OE_ORDER_LINES_ALL;
BO_QTY goes away, and included Backorder as status
08-MAY-01 Modified the MVT_STATUS and RELEASE_STAT to add missing rnmercer
references
17-MAY-01 Fix the line number to display all 5 numbers rnmercer
line.shipment.option.component.service
01-JUN-01 Added Order and Line Notification Information rnmercer
07-JUN-01 Enable the ability to output CREATION_DATE,
LAST_UPDATE_DATE and REQUEST_ID by Replacing a string rnmercer
11-JUN-01 Performance tuned the MTL_MATERIAL_TRANSACTIONS (TRN) rnmercer
query.
21-JUN-01 Included information on the following tables rnmercer
o OE_PRICE_ADJUSTMENTS
o WSH_FREIGHT_COSTS
o WSH_EXCEPTIONS
o OE_PROCESSING_MSGS(commented out until index created)
29-JUN-01 Removed Delivery Detail Id from the Move Orders because rnmercer
it was returning multiple rows.
Included information on the following tables
o RCV_SHIPMENT_LINES
o RCV_TRANSACTIONS_INTERFACE
30-JUN-01 Added the ORG_ID and Operating Unit Name rnmercer
Added the output for the ORDER and LINE HOLDS
o OE_ORDER_HOLDS, OE_HOLD_RELEASES, etc
Added information to output INTERNAL SALES ORDER to
REQUISITION, REQ WORKFLOW, and RECEIVING tables
Added information to output
o MTL_SUPPLY
12-JUL-01 Included the userenv(LANG) for NLS compatibility, and rnmercer
misc formatting changes
13-AUG-01 Output the workflow process status for the Error Item rnmercer
Type
29-AUG-01 Include the LOCK_STOP_ID of the WSH_TRIP_STOPS table rnmercer
07-SEP-01 Added output for the WSH_DELIVERY_DETAILS for CONTAINERS rnmercer
27-SEP-01 Added the OE_ORDER_LINES_HISTORY information rnmercer
05-DEC-01 Changed format of output filename as part of the iTar
automated scripting initiative. rodavid
05-JUN-02 Modified to handle SQL exceptions if user enters bad
data. rodavid
13-JUN-02 Modified to change version numbering to match Repository rodavid
02-APR-04 Modified to create HTML output file and included basic
14-APR-04 verifications to help the Analysis rijames
14-APR-04 include table ONT_WF_SKIP_LOG on header and lines rijames
12-MAY-04 modified to include Support changes listed below rijames
18-JUN-03 Added code to handle ATO Buy items. MTL_RESERVATION
record is used to derive the PO and REQ information. As
such this data is only displayed as long as the record
remains in the MTL_RESERVATION table. rodavid
18-JUN-03 Modified to display the MTL_MATERIAL_TRANSACTION data
for PO receipt for drop ship and internal orders rodavid
NOT 30-JUL-03 Modified to display data in ONT_WF_SKIP_LOG per new
NOT functionality in patch 2979522 rodavid
==> This table has been already included on the 14-APR-04 changes
31-JUL-03 Modified to prevent ORA-01722: invalid number in
MTL_SALES_ORDERS display section ilunandy
01-AUG-03 Modified WORKFLOW LINE STATUS to display record in
WF_ITEM_ACTIVITY_STATUSES_H ilunandy
12-AUG-03 Removed join on WF_NOTIFICATIONS in section
'WORKFLOW LINE STATUS ERRORS' to improve performance.
This information is already displayed in the next
section 'WF Activity Status for Line Error Process' ilunandy
21-AUG-03 Removed invalid 'column PRICE format A11'
before PROMPT PO_REQUISITIONS_INTERFACE_ALL (RQI) ilunandy
13-MAY-04 Added DataBase identification and user/time of script run rijames
03-JUN-04 Change reference naming for Order Lines, Delivery Details
Split Shipped Qty/Status Validations and Messages WDD
error list rijames
08-JUN-04 Change sorting order for Order_lines query to line_no rijames
21-SEP-04 Add additional 'end-table' after PL-SQL blocks, as if rijames
rdbms_put limit (1 million chars) is reached the table
is not closed.
01-NOV-04 Change column name SRC_REQUESTED_QUANTITY_UOM for rijames
DET.REQUESTED_QUANTITY_UOM on WDD main query
12-NOV-04 Add exception to handle null value on Org_id and Trans_id rijames
15-AUG-05 Include additional data from wshord11i.sql in MTI rijames
16-AUG-05 Include access to WSH_Serial_Numbers table. rijames
14-SEP-05 Add where clause (STATUS <> 'NO_ACTION_REQUIRED') to rijames
query on WSH_EXCEPTIONS
14-SEP-05 Add Column Description for table WSH_SERIAL_NUMBERS rijames
14-SEP-05 Add Column Description for MTL and RA tables rijames
14-SEP-05 Add column ORDERED_ITEM_ID to OE_ORDER_LINES_ALL query rijames
15-SEP-05 Supress output for table WSH_EXCEPTION by Dev. request rijames
to improve performance.
12-JAN-06 Add changes requested by Development. rijames
1. MTL_TRANSACTIONS_INTERFACE:
Add condition 'and DET.DELIVERY_DETAIL_ID = TMP.PICKING_LINE_ID'
in both queries of union. This will fix the issue of extra
delivery details showing up in the output
2. WSH_DELIVERY_DETAILS:
Remove MVT_STATUS(DET.MVT_STAT_STATUS), MVMT_ID(DET.MOVEMENT_ID )
and replace them with TRX_TEMP_ID(DET.TRANSACTION_TEMP_ID),
TOP_MO_LIN(DET.TOP_MODEL_LINE_ID) after SERIAL, SHP_MC respectively
3. MTL_TXN_REQUEST_LINES_V:
Add "9, '9=Cncld Source'" for decode of MV_LINE_STAT
4. Add 'Order by DET.SOURCE_LINE_ID, DET.DELIVERY_DETAIL_ID'
in w_del_details cursor
5. WSH_DELIVERY_ASSIGNMENTS:
Remove 'ASG.ACTIVE_FLAG ACTIVE'
6. WSH_NEW_DELIVERIES:
Remove
DEL.BOOKING_NUMBER BOOKING_NUM,
DEL.ACCEPTANCE_FLAG ACCEPTED
and replace with
DEL.SHIP_METHOD_CODE
DEL.CONFIRM_DATE
7. WSH_DELIVERY_LEGS:
Remove "LEG.LOAD_TENDER_STATUS LOAD_TENDER_STAT "
8. WSH_TRIP_STOPS:
Add "to_char(STP.ACTUAL_DEPARTURE_DATE,'DD-MON-RR_HH24:MI:SS')"
9. WSH_DELIVERY_DETAILS (CONTAINERS):
Modify
(select ASG1.DELIVERY_ID
from WSH_DELIVERY_ASSIGNMENTS ASG1,
WSH_DELIVERY_DETAILS DET1,
OE_ORDER_LINES_ALL LIN1
where DET1.SOURCE_LINE_ID = LIN1.LINE_ID AND
DET1.DELIVERY_DETAIL_ID = ASG1.DELIVERY_DETAIL_ID AND
LIN1.HEADER_ID = nvl('&header_id_selected',:v_header_id) AND
NVL('&line_id_selected',0) in (0,LIN1.LINE_ID,
LIN1.TOP_MODEL_LINE_ID,
LIN1.ATO_LINE_ID,
LIN1.LINK_TO_LINE_ID,
LIN1.REFERENCE_LINE_ID,
LIN1.SERVICE_REFERENCE_LINE_ID))
to
(select ASG1.DELIVERY_ID
from WSH_DELIVERY_ASSIGNMENTS ASG1,
WSH_DELIVERY_DETAILS DET1,
OE_ORDER_LINES_ALL LIN1
where DET1.SOURCE_LINE_ID = LIN1.LINE_ID AND
DET1.DELIVERY_DETAIL_ID = ASG1.DELIVERY_DETAIL_ID AND
DET1.SOURCE_CODE = 'OE' AND
...
This will fix the issue of extra containers showing up in omse11i.sql output
10. WSH_DELIVERY_DETAILS (CONTAINERS):
Add "DET.LPN_ID LPN_ID"
11. MTL_TRANSACTIONS_INTERFACE:
Add 'TMP.CONTENT_LPN_ID LPN_ID"
12. Modify
If dd.SRQ_Q*(100+nvl(dd.STA,0))/100 < dd.SHP_Q then
to
If (dd.SRQ_Q*(100+nvl(dd.STA,0))/100 < dd.SHP_Q) AND (dd.SRQ_U = dd.REQ_U) then
This will fix the issue of wrong validation when Order UOM quantity < Primary UOM quantity
13. Change
if dd.REL_STATUS_C not in ('C','D') -- not shipped or cancelled
and (dd.INI <> 'N'
or dd.OMI <> 'N') then -- but interfaced INV or OM
:r_flag := :r_flag || '25 ';
msg(25).flag := '1';
end if;
to
...
and (dd.INI not in ( 'N','X')
...
This will fix the issue of wrong validation for non-transactable lines when released_status
is 'X' and Inv_interfaced_flag is 'X'
13-JAN-06 Add more changes requested by Development. rijames
14. Add a new Block 'Organization Parameters' just above 'WSH_DELIVERY_DETAILS'
and show list of all organization_ids (as per the criteria for a line/all
lines in an order), WMS enabled(Y/N), OPM enabled(Y/N).
Use functions WSH_UTIL_VALIDATE.CHECK_WMS_ORG(organization_id) and
INV_GMI_RSV_BRANCH.PROCESS_BRANCH(organization_id)
15. oe_order_lines_all.actual_shipment_date, oe_order_lines_all.creation_date
under Order Lines
16. wsh_delivery_details.creation_date under WSH_DELIVERY_DETAILS and
WSH_DELIVERY_DETAILS(CONTAINERS)
16-JAN-06 Add more changes requested by Development. rijames
Change the following query
select count(*) -- count Released status 'R' but OM or INV interfaced
into :r_wdd
from WSH_Delivery_Details
where SOURCE_LINE_ID = I.LINE_I
and RELEASED_STATUS <> 'C'
and (nvl(INV_INTERFACED_FLAG,'N') <> 'N'
or nvl(OE_INTERFACED_FLAG,'N') <> 'N');
to
...
and (nvl(INV_INTERFACED_FLAG,'N') not in ( 'N','X')
...
so that the error(25. Associated Delivery Details have invalid INTERFACE status
(Not Shipped but Interfaced to OM or INV).) doesn't show up for non-transactable items.
19-JAN-06 Add more changes requested by Development. rijames
1. Change the following query
select sum(decode(nvl(OE_INTERFACED_FLAG,'N'),'N',1,0)), sum(decode(nvl(INV_INTERFACED_FLAG,'N'),'N',1,0))
into r_omint, r_invint
from WSH_Delivery_Details
where SOURCE_LINE_ID = I.LINE_I
group by SOURCE_LINE_ID;
to
...
where SOURCE_LINE_ID = I.LINE_I
and SOURCE_CODE = 'OE'
and released_status <> 'D'
group by SOURCE_LINE_ID;
The change will ignore cancelled details and container lines.
2. Add condition
source_code = 'OE'
in the following queries
a. If i.si = 'N' then -- Non interfaced to WSH
b. select count(*) -- count if any associated Delivery Detail has been OM interfaced
c. -- Check if exist associated Delivery Detail id regardles of line status and SI flag.
d. select count(*) -- count total wdd for this line
e. select count(*) -- Invalid Release flag status
f. select count(*) -- count Released status 'R' but OM or INV interfaced
g. -- count if any associated Delivery Detail has been interfaced to OM or INV
01-NOV-07 Changed output function from DBMS_OUTOUT for UTL_FILE, rijames Ver. 7.5
due to size limitations on DBMS_OUTPUT function.
Output file will now be located on DB Server under 'utl_file_dir'.
Script will printout the filename and location.
Added some columns as requested by Support.
03-JAN-08 Added option to ask customer which sections to print rijames Ver. 7.5.1
OM and WSH are mandatory, QP, PO, REC, WIP, INV and AR
are optional, parameters query shows the selected options.
Included information for OPM customers (from ordtl.sql),
added columns to Order Headers, Order Lines and Delivery
Details, two new queries are viewable only if OPM is active.
Modified MTL queries to NOT use Delivery Detail, so that
transactions from Bill-Only with Inventory Flows are visible.
Modified Order Headers and Order Lines to have outer-joins
with all related tables.
Added APPS Release version on DataBase Identification query.
Added QTY columns to 'Line_id's Associated with this Order' query.
Added script completion time as the last printout.
14-JAN-08 Corrected conditions for sections to use UPPER() when rijames
answer is provided on lower case.
04-FEB-08 Move INVentory and OM Interface Flags after the Release rijames Ver. 7.5.2
status field.
Correction on decode of the Hold Entity Code, label for
code 'W' is changed to 'Warehouse' (from 'Workflow').
06-FEB-08 Add outer join to MTL_SALES_ORDERS to print when Order rijames
Type definition has been deleted.
Move Parameters summary to top of output.
Modify to handle Orders with no lines, only Header
information will be printed.
07-FEB-08 Add Hostname on printout for ouput location. rijames
#
#========================================================================================
References
----------
For documentation and white papers on the Order Management product suite go to Metalink,
[url]http://metalink.us.oracle.com[/url], click on Top Tech Docs button -> E-Business Suite: ERP ->
Distribution/Supply Chain -> OM Suite: Order Management.
*/
/*
TITLE HTMomse11i.sql
SUPPORT NOTES
Script to Report the data shown on tables and columns on "Simplified ERD of main
Order Management / Drop Shipment" related tables and schematic representation of
columns connections for a given Sales Order number.
EXECUTION
This script requires a header_id to be passed in as a parameter ONLY if there are multiple header_id's
for the order requested. If there is only one header_id returned after inputting the order number, simply
hit enter and this header_id will be assumed.
Entering a line_id is only required when you wish to have output for one order line. Otherwise, hit enter
without entering a line_id and all lines will be output in the script.
To enable outputting values for the CREATION_DATE, LAST_UPDATE_DATE and REQUEST_ID do the following:
1.) Copy ONTomse11i.sql to ONTomse11i_ts.sql
2.) Open ONTomse11i_ts.sql in wordpad and perform a replace as follows
Find What: --ENABLE_TIMESTAMP
Replace With:
3.) Choose Replace All
4.) Save
5.) Run ONTomse11i_ts in SQL*Plus same as ONTomse11i.sql
*/
variable v_order_num varchar2(100);
variable v_error number;
variable v_line_tot number;
variable v_line_cnt number;
variable sales_ord_id number;
variable v_op_unit number;
variable v_head_cnt number;
variable v_header_id number;
variable v_head_only varchar2(1);
variable r_line_t varchar2(100);
variable r_res_q number;
variable r_wdd number;
variable r_flag varchar2(100);
variable r_pro_na varchar2(100);
variable r_act_na varchar2(100);
variable r_result varchar2(100);
variable r_act_s varchar2(100);
variable r_error number;
variable is_opm varchar2(2);
set arraysize 4;
set pagesize 58;
set term on;
set linesize 145;
set underline =;
set verify off;
set serveroutput on size 1000000;
set feedback off;
-- OE_ORDER_HEADERS
column HEADER_ID format 99999999;
column ORGANIZATION_NAME format A30;
column TYPE_ID format 99999999;
column TYPE_NAME format A15;
column ORD_DATE_TYPE format a15;
column ORD_NUM format 999999999;
column VERS format 9999;
column ORD_TYPE_ID format 999999999;
column FLOW_CODE format a22;
column CUST_NAME format A20;
column CUST_ID format 999999999;
column SHIP_TO_ID format 999999999;
column SHIP_TO format A12;
column SOLD_TO_ID format 999999999;
column PO_NUMBER format A09;
column DATE_ORD format A15;
column PL_ID format 99999;
column PRICE_LIST_NAME format A15;
column PL_NAME format A15;
column SHIP_PART format A09;
column ORGANIZATION format 9999999;
column WH_ID format 99999;
column DEMAND_CLASS format A12;
column OP format A05;
column CN format A05;
column BK format A05;
column DRP_SHP format A07;
column CYCLE_ID format 99999999;
column ORD_SRC_ID format 99999999;
column TRANS_TYPE format A12;
column CATEGORY format A08;
column SP format A05;
column CAT format A06;
-- WF_NOTIFICATIONS
column TO_USER format a22;
column ORIG_RECIP format a10;
column RECIP_ROLE format a10;
column MAIL_STAT format a9;
column MESSAGE_NAME format a25;
column SUBJECT format a45;
-- OE_TRANSACTION_TYPES_ALL
column ORDER_TYPE_NAME format A25;
column TYPE_NAME format A15;
column CONSTANTOE format A15;
column SHIP_PRIORITY format A13;
column SYS_REQD format A08;
-- OE_ORDER_LINES_ALL
column LINE_ID format 99999999;
column LINE format A08;
column LINE_NUM format A08;
column LINE_CTG format A09;
column CONFIG_HDR_ID format B99999999;
column SSCHED format B9999;
column PRT_LINE_ID format B99999999;
column ATO_LINE_ID format B99999999;
column LNK_LINE_ID format B99999999;
column SHP_LINE_ID format B99999999;
column SRC_LINE_ID format B99999999;
column SRV_LINE_ID format B99999999;
column ITEM_ID format 99999999;
column ITEM format A17;
column ORD_Q format 999999.99;
column ORG_Q format 999999.99;
column REQ_Q format 999999.99;
column PRICE format $999999999.99;
column SHP_Q format 999999.99;
column Q_INC format 999999.99;
column SHN_Q format 999999.99;
column SHP_Q format 999999.99;
column FUL_Q format 999999.99
column RES_Q format 999999.99;
column INC_Q format 999999.99;
column CAN_Q format 999999.99;
column LN_SET_TY format A11;
column SH_SET_TY format A11;
column AR_SET_TY format A11;
column REQUEST_D format A15;
column SCHEDUL_D format A15;
column SOURCE_TYPE format A11;
column DEM_CLASS format A10;
column LINE_DETAIL_ID format 9999999999;
--column OPEN_FL format A07;
--column CANC_FL format A07;
--column BOOKED format A07;
column SH format A05;
column VD format A05;
column FF format A05;
column SUBINV format A10;
column SSC format A05;
column SI format A05;
column II format A05;
column INVC_INT_STAT format A14;
column SHIPPABLE format A08;
column TRANSACTABLE format A08;
column RESERVABLE format A08;
column RELEASED format A08;
column FOR_REVENUE format A08;
column DELIVERY format 9999999999;
column SET_TYPE format A15;
column SET_NAME format A10;
column STATUS format A06;
column SCH_SHP_DT format A10;
column SCH_ARV_DT format A10;
column CARRIER format A15;
column SHIP_METHOD format A15;
column wf_act_code format a11;
column wf_result format a9;
column hist_comments format a45;
column hist_type format a12;
-- column PRICE format $9999999999.99;
-- WORKFLOW TABLES
column ITEM_KEY format A08;
column FLOW_PROCESS format A22;
column RESULT_CODE format A15;
column RESULT format A15;
column ASSIGNED_USER format A10;
column ERROR_NAME format A19;
column PROCESS_NAME format A25;
column ACTIVITY_NAME format A25;
column ERROR_ACTIVITY_NAME format A31;
column ACT_STATUS format A10;
column HEAD_ID format A08;
column LIN_ID format A08;
column NOTIF_ID format 99999999;
column ERROR_NAME format A14;
column ERR_RETRY_ROLE format A14;
column ERR_RETRY_USER format A14;
column BEGIN_DATE format A18;
column END_DATE format A18;
column ORD_TYPE_NAME format A20;
column CONSTANTOE format A12;
column DEMAND_ID format 9999999999;
column PARDEM_ID format 9999999999;
column DS_HEADER_ID format 99999999;
column DS_LINE format A08;
column RSV_QTY format 9999999.99;
column SAT_QTY format 9999999.99;
column PND_QTY format 9999999.99;
column REQUIRD_D format A15;
column TY format 99999;
column UP format 99999.99;
column MRP format 99999.99;
column ATP format 99999.99;
column PICKING_HEADER_ID format 9999999999;
column PICK_SLIP format 9999999999;
column BATCH_ID format 99999999;
column STATUS_CODE format A11;
column BATCH_NAME format A30;
column PICKING_LINE_ID format 9999999999;
column CONFIRMED format A09;
column RA_INTERFACE format A12;
column IN_INTERFACE format A15;
column PICK_LN_DTL_ID format 9999999999;
column SUBINVENTORY format A12;
column REV format A05;
column LOT_NUMBER format A15;
column SERIAL_NUMBER format A20;
column LOCATION_ID format 9999999999;
column ORG format A05;
column ATO format A05;
column OPT format A05;
column CFG format A05;
column SHIP_MC format A07;
column SHIP_SET format 99999999;
column ITEM_TYPE format A09;
column LINE_TYPE format A09;
column ENTER format A10;
column CANCEL_ORD format A10;
column CANCEL_LIN format A10;
column CTG format A05;
column INCL format A05;
column CONF format A05;
column WIP_RSV format 9999999;
column WIP_COM format 9999999;
column COMP_CODE format A10;
column SRC_TYP format 9999999;
column DEM_TYP format 9999999;
column WIP_ID format B99999999;
column JOB_NAME format A15;
column JOB_STAT format A12;
column UOM format A5;
column REL_FL format A06;
column SHIP_FL format A07;
column FOR_REV format A07;
column AUTOSCH format A06;
column DEP_NAME format a10;
column ORG_ID format 99999;
column WSH_ID format 9999999;
column SOURCE_CD format a12;
column STAT_CODE format A11;
column CLOSED_DT format A09;
column FREIGHT format A09;
column PLN_DEP_DT format A10;
column ACT_DEP_DT format A10;
column PLN_DEP_ID format 9999999999;
column ACT_DEP_ID format 9999999999;
column LOAD_ORD_FLG format A12;
column DEL_SEL format A07;
column DEP_SEL format A07;
column REL_STAT format A08;
column LIN_STAT format A08;
column WIP_ENT_ID format 9999999999;
column CONFIRM_D format A09;
column INVC_STAT format A10;
column INVC_INTF format A10;
column SHIP_STAT format A10;
column SHIP_INTF format A10;
column LOT format A10;
column REV format A05;
column SERIAL_NUM format A10;
column LOC_ID format 99999999;
column RESV_FL format A07;
column TRAN_FL format A07;
column SCHED_STAT format A10;
column INVOICE_TRIGGER format A15;
column SOURCING_STATUS format A15;
column INVOICING_STATUS format A16;
column CANCELLED_FLAG format A14;
column OPEN_FLAG format A09;
column CONSOLIDATION_STATUS_CODE format A25;
column CONSOLIDATION_PREF_FLAG format A23;
column PURCHASE_ORDER_NUM format A18;
column DEL_ID format 9999999;
column DEL_NAME format A10;
-- PO_REQUISITION_INTERFACE_ALL
column AUTH_STATUS format A11;
column DEST_TYPE format A10;
column SRC_CODE format A11;
column SRC_TYPE_CODE format A13;
-- PO_REQUISITION_HEADERS_ALL
column REQ_NUMBER format A10;
column SUMMARY format A07;
column XFR_OE_FLAG format A11;
column REQ_TYPE format A11;
column ENABLED format A07;
column ITEM_DESC format A40;
column CANC format A05;
column ENC_FL format A06;
column RFQ format A05;
column SRC_TYPE format a09;
column SRC_ORG format 9999999;
-- PO_HEADERS
column PO_NUM format A06;
-- MTL_SUPPLY
column SUP_TYPE format a08;
-- MTL_RESERVATIONS
column SHIP_READY format A12;
column SS_TYPE_ID format A11;
-- WSH_TRIPS
column TRIP_ID format 9999999;
column TRIP_NAME format A10;
column PLND format A05;
column VEH_NUM format A10;
column CARR_ID format 9999999;
column DET_Q format 99999;
column DS_TYPE format 9999999;
column ROUTE_ID format 99999999;
column VEH_ORG_ID format 9999999999;
-- WSH_TRIP_STOPS
column STOP_ID format 99999999;
column SEQ_NUM format 9999999;
column STOP_LOC_ID format 99999999999;
column PLN_DEP_DATE format A15;
column PLN_ARV_DATE format A15;
column ACT_DEP_DATE format A15;
column ACT_ARV_DATE format A15;
column PEND_INTERF format A11;
-- WSH_DELIVERY_LEGS
column LEG_ID format 9999999;
column LOAD_TENDER_STAT format A15;
-- WSH_NEW_DELIVERIES DEL
column DEL_NAME format A12;
column BOOKING_NUM format A12;
column WAYBILL format A12;
column ACCEPTED format A15;
column PICKUP_DT format A15;
column DROPOFF_DT format A15;
-- WSH_DELIVERY_ASSIGNMENTS ASG
column ACTIVE format A06;
-- WSH_DELIVERY_DETAILS
column REL_STATUS format A17;
column STA format 99999;
column STB format 99999;
column HOLD_CODE format A09;
column SMC format A05;
column SUB format A09;
column CUR_SUB format A09;
column ORG_SUB format A09;
column REV format A05;
column LOT format A05;
column SERIAL format A10;
column LOC_ID format 99999999;
column SHIP_METH format A10;
column OMI format A05;
column INI format A05;
column MVT_STATUS format A10;
column INV_INT format A07;
column CONT_NAME format A15;
column CONT_TYPE format A10;
column UNIT_NUM format A08;
column CONTAINER format A09;
column REQ_DATE format A15;
column MVT_STAT format A08;
column DEL_DET_ID format 9999999999;
column RELEASE_STAT format A15;
column REQ_Q format 999999.99;
column DLV_Q format 999999.99;
column SRQ_Q format 999999.99;
column BO_Q format 999999.99;
--column CAN_QTY format 9999999;
column SHIP_TO_ID format 999999999;
column LINE_REQ_QTY format 999999999999;
column TYPE format A05;
column MO_LINE_ID format 9999999999;
column DELIV_ID format 999999999;
column SH_FROM_ID format 999999999;
column SH_TO_ID format 99999999;
--WSH_PICK_SLIP_V
column LINE_STAT format A11;
column FROM_SUB format A09;
column TO_SUB format A09;
column DETL_DATE format A15;
column LINE_STATUS format A11;
--MTL_TXN_REQUEST_LINES_V
column REQ_NUM format A10;
column MV_LINE_STAT format A14;
column MO_NUMBER format A09;
column MOVE_TYPE_NAME format A14;
column TRNS_SRC_TYPE format A13;
column TRNS_TYPE_NAME format A30;
column TRNS_ACTION format A15;
column LOT_NUM format A10;
--MTL_MATERIAL_TRANSACTIONS_TEMP
column ERROR_EXPL format A10;
-- RA_CUSTOMER_TRX
-- RA_CUSTOMER_TRX_LINES
column ORD_LINE_NUM format 999999999999;
column TRX_NUMBER format A10;
column CONTEXT format A12;
column Order_Num_1 format A11;
column Order_Type_2 format A12;
column Delivery_3 format A10;
column WayBill_4 format A09;
column Line_ID_5 format A09;
column Pick_Line_Id_7 format A14;
column Bill_Lading_8 format A13;
column Warehouse_10 format A12;
column SOURCE format A30;
column TAX_FL format A06;
column QTY_ORD format 9999999.99;
column QTY format 9999999.99;
column ORD_LINE_NUM format A12;
column HEAD_CAT format A8;
column CURR format A4;
column TAX_EX_FL format A09;
column TERR_SEG1 format A09;
column TERR_SEG2 format A09;
column TERR_SEG3 format A09;
column MESSAGE_TEXT format A145;
column INVALID_VALUE format A30;
column STATUS format a8;
column ERR_TYPE_KEY format a14;
column ASGND_USER format a10;
column ERR_PROCESS_NAME format a18;
column ERR_ACTIVITY_NAME format a22;
column hold_name format a22;
column hold_type format a12;
column WF_ACTIVITY format a15;
column ENTITY format a8;
column ENTITY2 format a8;
column HOLD_UNTIL format a18;
column RELEASE_REASON format a14;
column H_REL format a5;
column S_REL format a5;
column AF format A5;
column LIST_TYPE_CODE format a14;
column UA format A5;
column UF format A5;
column AP format A5;
column ARITH_OP format a8;
column TAX_CODE format a12;
column INF format A5;
column EF format A5;
column CHG_TY_CD format a9;
column AC format A5;
column PI format A5;
column CD format A5;
column LIST_LN_NO format a10;
column LK format 99999;
column PP format 99999;
column MOD_LVL format a7;
column PERC format 9999;
column STATUS format a6;
column msg_Source format a14;
column error format a30;
column ITEM_DESC format a25;
column PRICE format $999999999.99
column REQ_NUM_IK format A9;
column ACCEPT_REQD format A10;
column CLS_STAT format A08;
column CONF_ORD format A08;
column CURR_CODE format A05;
column ENABLED format A07;
column FROZEN format A06;
column SUMM format A05;
column TYPE format A10;
column ITEM_KEY format A09;
column SHIP_TO format 9999999;
column PO_LINE format 9999;
column FIRM format A05;
column ITEM_DESC format A40;
column PO_NUM_IK format A9;
column ERROR_N format A7;
column SRC_DOC format a9;
column SHP_LINE_STATUS format a15;
column RCV_Q format 99999;
column TRANS_TYPE format A10;
column INSPECT_STAT format A13;
column INTF_SRC format A11;
column SRC_DOC_CODE format A12;
column QTY format 9999;
column PROC_MODE format a10;
column DEST_TYPE format a9;
column INSP_STAT format a13;
column INSP_SRC format a8;
column PROC_STAT format a9;
column RCPT_SRC format a8;
column TRNS_STAT format a9;
column TRNS_TYPE format a9;
column error_wie format a100;
column status format a9;
column firm format 9999;
column WIP_SUP_TYPE format a13;
column JOB_NAME format a8;
column TRANS_TYPE format a19;
column SRC_CODE format a8;
column PHS format 99999;
column STAT_TY format 9999999;
column STAT format 9999;
column LOAD format 9999;
column status format a9;
column firm format 9999;
column WIP_SUP_TYPE format a13;
column JOB_NAME format a8;
column TRANS_TYPE format a19;
column SRC_CODE format a8;
column PHS format 99999;
column STAT_TY format 9999999;
column STAT format 9999;
column LOAD format 9999;
column STOP_DESCRIPTION format A58;
column GRS_WT format 999999;
column FL_PER format 999.99;
column VOL format 9999;
column VOL_UOM format a07;
column CF format A05;
column WT_UOM format a06;
column FOB_CODE format a11;
column FRT_TERMS format a9;
column LCK format 99999;
column PROCESS format 9999999;
column ERROR_CODE format A30;
column ERROR_EXPL format A60;
column FRT_NAME format a30;
column FRT_TYPE format a15;
column FRT_LEVEL format a15;
column QTY format 9999;
column PRM_Q format 99999;
column DLV_Q format 99999;
column DTL_Q format 99999;
column LCK format A05;
column PROCESS format A07;
column ERROR_CODE format A11;
column ERROR_EXPL format A30;
column TRANS_TYPE format A11;
column QTY format 9999;
column LINE_NUM format 99999999;
column BATCH_SOURCE format a20;
column SO_LIN format a06;
column AR_ID format 99999;
column IR_ID format 99999;
column WH_ID_10 format a08;
column PA_ID_11 format a08;
column C_RATE format 999999;
column TR format 9999;
column EF format A05;
column INTF_LINE_ID format 99999999999;
column CRD_Q format 99999;
column PRICE format $9999999.99;
column PO_NUMBER format A09;
column COMP_FL format a07;
column EXTD_AMT format $999,999.99;
column REV_AMT format $999,999.99;
column TF format A05;
column SOURCE format A11;
column INV_Q format 99999;
column SHIP_VIA format A12;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
Set heading on
prompt
accept order_number_selected prompt 'Please enter Sales Order number: '
prompt
begin
:v_error := 0;
:v_order_num := '&order_number_selected';
select count(*)
into :v_head_cnt
from oe_order_headers_all
where
order_number = :v_order_num;
if :v_head_cnt = 0 then
RAISE no_data_found;
end if;
if :v_head_cnt = 1 then
select header_id
into :v_header_id
from oe_order_headers_all
where order_number = :v_order_num;
end if;
exception
when no_data_found then
dbms_output.put_line('ERROR - Invalid order number entered');
dbms_output.put_line('ACTION - Script execution must be aborted');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
:v_error := 1;
raise;
when others then
dbms_output.put_line('ERROR - Unable to retrieve order due to error: '||SQLERRM);
dbms_output.put_line('ACTION - Script execution must be aborted');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
:v_error := 1;
raise;
end;
/
-- spool &out_file;
/* Display list of header_ids matching the order number entered */
select
ORD.HEADER_ID HEADER_ID,
TYP.NAME ORDER_TYPE_NAME,
ORD.ORDER_CATEGORY_CODE CATEGORY,
ORD.ORG_ID ORG_ID,
(select name
from hr_operating_units
where organization_id = nvl(ORD.ORG_ID,-99)) ORGANIZATION_NAME
from
OE_ORDER_HEADERS_ALL ORD,
OE_TRANSACTION_TYPES_TL TYP,
FND_LANGUAGES FLA
where
ORD.ORDER_NUMBER = :v_order_num
and TYP.LANGUAGE = FLA.LANGUAGE_CODE
and FLA.INSTALLED_FLAG = 'B'
and ORD.ORDER_TYPE_ID = TYP.TRANSACTION_TYPE_ID
and :v_error = 0;
prompt
accept header_id_selected prompt 'Please enter HEADER_ID from list above (optional): '
prompt
/* Set client info context based on the org_id from sales order */
begin
select org_id
into :v_op_unit
from oe_order_headers_all ord
where
ORD.HEADER_ID = nvl('&header_id_selected',:v_header_id);
fnd_client_info.set_org_context(to_char(:v_op_unit));
exception
when no_data_found then
dbms_output.put_line('ERROR - Invalid header_id entered');
dbms_output.put_line('ACTION - Script execution must be aborted');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
:v_error := 1;
raise;
when others then
dbms_output.put_line('ERROR - Unable to set context due to error: '||SQLERRM);
dbms_output.put_line('ACTION - Script execution must be aborted');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
:v_error := 1;
raise;
end;
/
WHENEVER SQLERROR CONTINUE;
begin
select count(*)
into :v_line_tot
from oe_order_lines_all
where header_id = nvl('&header_id_selected',:v_header_id);
if :v_line_tot = 0 then
dbms_output.put_line('WARNING - Order selected does not have any lines');
dbms_output.put_line('WARNING - Please hit CTL-C to abort');
dbms_output.put_line('WARNING - If you chose to continue only Header information will be printed,');
dbms_output.put_line('WARNING - all additional entries will be ignored.');
:v_head_only := 'Y';
else
:v_head_only := 'N';
end if;
end;
/
/* Display all line_ids associated with this order number */
select
substr(LIN.LINE_ID,1,15) LINE_ID,
substr(to_char(LIN.line_number) ||
decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
decode(LIN.component_number, null, null,
decode(LIN.option_number, null, '.',null)||
'.'||to_char(LIN.component_number))||
decode(LIN.service_number,null,null,
decode(LIN.component_number, null, '.' , null) ||
decode(LIN.option_number, null, '.', null ) ||
'.'|| to_char(LIN.service_number)),1,10) LINE_NUM,
ITM.SEGMENT1 ITEM,
substr(LIN.LINE_CATEGORY_CODE,1,10) lin_cat,
nvl(LIN.ORDERED_QUANTITY,0) Order_QTY,
LIN.ORDER_QUANTITY_UOM uom,
substr(LIN.FLOW_STATUS_CODE,1,20) Line_status,
nvl(LIN.SHIPPED_QUANTITY,0) shipped,
nvl(LIN.FULFILLED_QUANTITY,0) fulfilled,
nvl(LIN.INVOICED_QUANTITY,0) invoiced,
nvl(LIN.CANCELLED_QUANTITY,0) cancelled
from
OE_ORDER_LINES LIN,
MTL_SYSTEM_ITEMS ITM
where
LIN.HEADER_ID = nvl('&header_id_selected',:v_header_id)
and LIN.SHIP_FROM_ORG_ID = ITM.ORGANIZATION_ID(+)
and LIN.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID(+)
and LIN.OPTION_NUMBER IS NULL
and LIN.ITEM_TYPE_CODE <> 'INCLUDED'
order by
NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
NVL(LIN.SORT_ORDER, '0000'),
NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
NVL(LIN.SERVICE_REFERENCE_LINE_ID, LIN.LINE_ID),
LIN.LINE_ID;
/* Display any header or line sets associated with this order */
PROMPT
PROMPT OE_SETS (SET)
select ST1.SET_ID SET_ID,
ST1.SET_NAME SET_NAME,
ST1.SET_TYPE SET_TYPE,
ST1.HEADER_ID HEADER_ID,
ST1.INVENTORY_ITEM_ID ITEM_ID,
ST1.ORDERED_QUANTITY_UOM UOM,
ST1.LINE_TYPE_ID LINE_TYPE_ID,
nvl(LST.SYSTEM_REQUIRED_FLAG,'N') SYS_REQD,
ST1.SET_STATUS STATUS,
to_char(ST1.SCHEDULE_SHIP_DATE,'DD-MON-RR_HH24:MI:SS') SCH_SHP_DT,
to_char(ST1.SCHEDULE_ARRIVAL_DATE,'DD-MON-RR_HH24:MI:SS') SCH_ARV_DT,
ST1.SHIP_FROM_ORG_ID SHIP_FROM,
ST1.SHIP_TO_ORG_ID SHIP_TO_ID,
ST1.SHIPMENT_PRIORITY_CODE SHIP_PRIORITY,
ST1.FREIGHT_CARRIER_CODE CARRIER,
ST1.SHIPPING_METHOD_CODE SHIP_METHOD,
ST1.SHIP_TOLERANCE_ABOVE STA,
ST1.SHIP_TOLERANCE_BELOW STB
from OE_SETS ST1,
OE_LINE_SETS LST
where ST1.SET_ID = LST.SET_ID(+)
and ST1.HEADER_ID = nvl('&header_id_selected',:v_header_id);
prompt
accept line_id_selected prompt 'Please enter LINE_ID from list above(leave blank for all lines): '
begin
if :v_line_tot > 0 then -- check for line_id
begin
select count(*)
into :v_line_cnt
from oe_order_lines_all
where header_id = nvl('&header_id_selected',:v_header_id)
and nvl('&line_id_selected',0) in (0,line_id);
if :v_line_cnt = 0 then
RAISE no_data_found;
end if;
exception
when no_data_found then
dbms_output.put_line('ERROR - Invalid line_id entered');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
dbms_output.put_line('.');
:v_error := 1;
raise;
when others then
dbms_output.put_line('ERROR - Unable to retrieve order line due to error: '||SQLERRM);
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
dbms_output.put_line('.');
:v_error := 1;
raise;
end;
end if;
end;
/
WHENEVER SQLERROR CONTINUE;
prompt
accept do_analysis prompt 'Do you want validation performed on this order (Default=Y): '
prompt
prompt
accept prt_wf prompt 'Do you want WorkFlow information printed? (Default=Y): '
accept prt_price prompt 'Do you want Pricing information printed? (Default=Y): '
accept prt_po prompt 'Do you want Purchasing information printed? (Default=Y): '
accept prt_rec prompt 'Do you want Receiving information printed? (Default=Y): '
accept |
