Help to build query for Order Management

[lukman]

I'm trying to build a report for order management module. I could get the customer's name for ship to and bill to field if those 2 fields, whether the numbers are same or not, if the customer are the same. For example :

Customer A (which had 2 sites, site 1 and site 2) bought an item and he/she want the item send to site 2 but want the invoice sent to site 1.

but I couldn't get it if the customer's name are different. For example :

Customer A bought an item and he/she want the item sent directly to Customer B (which already had a relationship with Customer A) but want the invoice sent to Customer A, so the ship to field filled with Customer B's number and bill to field filled with Customer A's number.

I already tried to modify the script but still couldn't solve it. The form script is OE_ORDER_HEADERS_V and from that script I found that ship to and bill to field get the data from same column which is in table HZ_CUST_SITE_USES_ALL column LOCATION.

Anyone can help? Thanks.

[ahmadbilal]

Hi Yudi,

What i understand from you question is you want to get print customer and bill to ship to along in both case if order is directly ship to customer ship to location or drop ship order.

Solution

Write a function which will Return Customer Name
Write a function which will return ship to and bill to


or you can use this query
SELECT rc.customer_id, hp.party_id, rc.customer_number, hp.party_number,
su.LOCATION, su.site_use_code, rc.customer_name,
hp.address1 || hp.address2 || hp.address3 || hp.address4, hp.city,
hp.country
FROM hz_cust_site_uses_all su,
hz_cust_acct_sites_all a,
hz_cust_accounts c,
hz_parties hp,
ra_customers rc
WHERE c.party_id = hp.party_id
-- and su.site_use_code = 'BILL_TO'
AND a.cust_acct_site_id = su.cust_acct_site_id
AND c.cust_account_id = a.cust_account_id
AND rc.party_id = hp.party_id
<b> -- AND su.org_id = :p_org_id
-- AND rc.customer_number = '1203'</b>

Hope this will help you

[lukman]

sorry, I don't understand. can you give me an example?

[lukman]

Hi all, I managed to solve my problem. Here's the query in case someone need it. Thanks

SELECT party.party_name "Send to" (SELECT party.party_name
FROM hz_cust_site_uses_all bill_su,
hz_cust_acct_sites_all bill_cas,
hz_party_sites bill_ps,
hz_locations bill_loc,
hz_parties party,
oe_order_headers_all h
WHERE h.invoice_to_org_id = bill_su.site_use_id
AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id
AND bill_cas.party_site_id = bill_ps.party_site_id
AND bill_loc.location_id = bill_ps.location_id
AND bill_cas.party_site_id = bill_ps.party_site_id
AND bill_ps.party_id = party.party_id
AND h.order_number = '<i>your_order_number</i>') "Invoice to"
FROM oe_order_headers_all h,
hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps,
hz_locations ship_loc,
hz_parties party
WHERE h.ship_to_org_id = ship_su.site_use_id
AND ship_cas.party_site_id = ship_ps.party_site_id
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id
AND ship_cas.party_site_id = ship_ps.party_site_id
AND ship_ps.party_id = party.party_id
AND ship_loc.location_id = ship_ps.location_id
AND h.order_number = '<i>your_order_number</i>';

just remember that <i>your_order_number </i> for Invoice to and Ship to must be the same. Thanks

[lukman]

Hi all

I have another questions about order management report. I tried to create a report for warehouse people to pick items. Using inventory responsibility, navigate to move orders - transact move orders and select pick number. I can create the report successfully if the item doesn't have lot/serial number.

Suppose that pick number : 200 had 2 items : A and B where A doesn't has lot/serial number and B has lot number. Because B has lot number, it's detail are B1 and B2 (where quantity of B1 + B2 = B but B1 & B2 each had lot number).

Please look at my script below :

SELECT mtrh.request_number "Pick Order Number",
mtrh.organization_id "Warehouse", ROWNUM "No.",
msif.segment1 "Item's Name", msif.description,
msif.lot_control_code, msif.serial_number_control_code,
(CASE msif.lot_control_code
WHEN 1
THEN mtrl.quantity
WHEN 2
THEN mtlt.transaction_quantity
END
) "Qty"
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_system_items_fvl msif,
mtl_transaction_lots_temp mtlt,
mtl_material_transactions_temp mmtt
WHERE mtrh.request_number = '200'
AND mtrh.header_id = mtrl.header_id
AND mtrl.txn_source_id = mmtt.transaction_source_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtrl.inventory_item_id = msif.inventory_item_id
AND mtrl.organization_id = msif.organization_id;

Using the script above I get result :

A
B1
A
B2

What I want to do is create report that give me result the details of :

A
B1
B2

Can someone help? thanks.