SELECT * FROM sales_records
SELECT REGEXP_SUBSTR(:p_string,
'[^,]+',
1,
LEVEL)
AS site
FROM DUAL
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(:p_string,
'[^,]*')) + 1;
SELECT REGEXP_SUBSTR('1001,1002,1003',
'[^,]+',
1,
LEVEL)
AS site
FROM DUAL
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('1001,1002,1003',
'[^,]*')) + 1;
In the ship set process, the user would like to ship group of order lines from same warehouse to same location. If Order Lines are grouped under ship set, then until all lines in the ship set satisfy the demand, you cannot ship the goods to customer.
In the arrival set process, the customer requests
specific order lines to arrive together.
MTL_TXN_REQUEST_HEADERS
Move order headers, this stores the move order number in column
(REQUEST_NUMBER). It has a status, but this is not used as much as the lines
status to drive functionality.
MTL_TXN_REQUEST_LINES
Move order lines, this is the one that drives most queries and status checks
for the move order as each line can be transacted individually.
MTL_MATERIAL_TRANSACTIONS_TEMP
Pending material transactions table also called the transaction temporary
table, this holds allocations that act like reservations on inventory. An
allocation is where you pick a specific item in inventory down to the lot,
locator, serial, revision to move, but you do not actually perform. The move
yet.
Field move_order_header_id and
move_order_line_id provided in this table, which related to
MTL_TXN_REQUEST_HEADERS and
MTL_TXN_REQUEST_LINES.
MTL_MATERIAL_TRANSACTIONS
This is the store of transact move order transactions. And there were some
queries related with it.
For some additional notes:
When a move order is allocated, a
corresponding record is inserted into the pending table
(MTL_MATERIAL_TRANSACTIONS_TEMP as well as lot/serial tables if required).
When the move order is transacted, the
record moves from the pending table to the history table
(MTL_MATERIAL_TRANSACTIONS).
If both purchased and purchasable attribute are enabled, we can create new purchasing documents and approve and receive the items.
If purchased is enabled and purchasable
is disabled, we cannot create purchasing documents and approve but we can
receive the item against approved purchasing documents.
We can enable purchasable only when
purchased is enabled.
SELECT
okh.org_id
,
okh.contract_number
,
okh.contract_number_modifier
,
okh.ship_to_site_use_id
,
ship_hps.party_site_number ship_to_site_number
,
ship_hcas.account_number ship_to_customer_number
,
okh.bill_to_site_use_id
,
bill_hps.party_site_number bill_to_site_number
,
bill_hcas.account_number bill_to_customer_number
FROM
okc_k_headers_all_b okh
,
hz_cust_site_uses_all ship_hcsua
,
hz_cust_acct_sites_all ship_hcasa
,
hz_party_sites ship_hps
,
hz_cust_accounts ship_hcas
,
hz_cust_site_uses_all bill_hcsua
,
hz_cust_acct_sites_all bill_hcasa
,
hz_party_sites bill_hps
,
hz_cust_accounts bill_hcas
WHERE 1 = 1
AND
okh.contract_number = '11346850'
AND
ship_hcsua.site_use_code = 'SHIP_TO'
AND
okh.ship_to_site_use_id = ship_hcsua.site_use_id
AND
ship_hcasa.cust_acct_site_id = ship_hcsua.cust_acct_site_id
AND
ship_hcasa.party_site_id = ship_hps.party_site_id
AND
ship_hps.party_id = ship_hcas.party_id
AND
ship_hcas.cust_account_id = ship_hcasa.cust_account_id
AND
bill_hcsua.site_use_code = 'BILL_TO'
AND
okh.bill_to_site_use_id = bill_hcsua.site_use_id
AND
bill_hcasa.cust_acct_site_id = bill_hcsua.cust_acct_site_id
AND
bill_hcasa.party_site_id = bill_hps.party_site_id
AND
bill_hps.party_id = bill_hcas.party_id
AND
bill_hcas.cust_account_id = bill_hcasa.cust_account_id
AND
ship_hcasa.status = 'A'
AND
ship_hcsua.status = 'A'
AND
ship_hcas.status = 'A'
AND
ship_hps.status = 'A'
AND
bill_hcasa.status = 'A'
AND
bill_hcsua.status = 'A'
AND
bill_hcas.status = 'A'
AND
bill_hps.status = 'A'
AND
okh.org_id = 111
AND
okh.scs_code = 'SUBSCRIPTION'
AND
okh.sts_code = 'ACTIVE'
ORDER BY okh.last_update_date DESC
