GML Sales Order Acknowledgement

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Logistics
Source: Sales Order Acknowledgement Report (XML)
Short Name: ORAKRP_XML
DB package: GML_ORAKRP_XMLP_PKG
SELECT 
                  orgnmst.orgn_name Orgn_Name,  
	ordr_hdr.order_type Order_Type, 
                  ordr_hdr.release_no Release_no, 
                  ordr_hdr.order_date O_Date, 
                  ordr_hdr.order_id Order_id_hdr, 
                  ordr_hdr.order_no O_No, 
                  ordr_hdr.sched_shipdate Ship_Date,
                  ordr_hdr.custpo_no Cust_Po, 
                  ordr_hdr.text_code Text, 
                  ordr_hdr.billing_currency Bill_Curr,  
                  soldcust.cust_no Sold_Cust, 
                  soldcust.cust_name Sold_Name,
                  soldcust.addr_id Sold_id,  
                  billcust.cust_no Bill_No, 
                  billcust.cust_name Bill_Name,
                  billcust.mailaddr_id Bill_id,
                  shipcust.cust_name Ship_Name,
                  ordr_hdr.fob_code Fbc, 
                  ordr_hdr.frtbill_mthd Fbm,
                  ordr_hdr.terms_code Terms,  
                  ordr_hdr.shipcust_id Ship_id, 
                  ordr_hdr.shipaddr_id Ship_Add, 
	GML_ORAKRP_XMLP_PKG.udateformula(ordr_hdr.order_type,ordr_hdr.order_no,ordr_hdr.release_no) udate, 
	GML_ORAKRP_XMLP_PKG.head_chargescfformula(ordr_hdr.order_id) Header_chargesCF, 
	GML_ORAKRP_XMLP_PKG.head_discountscfformula(ordr_hdr.order_id) Header_discountsCF, 
	GML_ORAKRP_XMLP_PKG.ord_tot_formula(ordr_hdr.order_id) Ord_Total, 
	GML_ORAKRP_XMLP_PKG.terms_codeformula(ordr_hdr.terms_code) Terms_code
FROM         op_ordr_hdr ordr_hdr,                                                                                         
                         op_cust_mst soldcust,
                         op_cust_mst billcust, 
                         op_cust_mst shipcust, 
                         sy_orgn_mst orgnmst 
                WHERE ordr_hdr.orgn_code        =       :ORGN_CODE          
                AND  ordr_hdr.delete_mark       =       0                                                                          
                AND  ordr_hdr.order_status  != -2
                AND  ordr_hdr.completed_ind  != -1 
                AND  orgnmst.orgn_code          =       :ORGN_CODE
               AND  soldcust.cust_id  (+)         =       ordr_hdr.soldtocust_id                           
                AND  billcust.cust_id  (+)         =       ordr_hdr.billcust_id    
	  AND  shipcust.cust_id (+)           =       ordr_hdr.shipcust_id 
    and TRUNC(ordr_hdr.order_date) between  TRUNC(nvl(:from_order_date_1,ordr_hdr.order_date)) and
TRUNC(nvl(:to_order_date_1,ordr_hdr.order_date))
	 &Order_NumberCP
                   &Sold_ToCP  
	&Bill_ToCP
               &Exclude_PrintedCP
	 &Exclude_On_HoldCP 
ORDER BY 
        orgnmst.orgn_name,ordr_hdr.order_date,ordr_hdr.order_no,ordr_hdr.Order_id,ordr_hdr.shipaddr_id,ordr_hdr.shipcust_id,
ordr_hdr.order_type,
			   DECODE (:SORT_1,'1','ordr_hdr.order_no','2', 'ordr_hdr.order_date','3','soldcust.cust_no','4','billcust.cust_no')
 	 &Sort2_CP	
	 &Sort3_CP
Parameter Name SQL text Validation
User
 
Number
Company
 
LOV Oracle
ORGN_CODE_CHAR4
 
LOV Oracle
Sort Order 3
 
LOV Oracle
Sort Order 2
 
LOV Oracle
Sort Order 1
 
LOV Oracle
Exclude On Hold
 
LOV Oracle
To Bill Customer
 
LOV Oracle
From Bill Customer
 
LOV Oracle
To Sold Customer
 
LOV Oracle
From Sold Customer
 
LOV Oracle
To Order Date
 
Date
From Order Date
 
Date
To Order Number
 
LOV Oracle
From Order Number
 
LOV Oracle
Exclude Printed
 
LOV Oracle