How to convert comma separated values into column in PL SQL

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;

 

Differentiate ship set and arrival set?

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.

Move order Tables?

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).

What is the difference between purchased and purchasable flag for an item?

 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.

Query to find out Bill_To and Ship_To Customer from Contract

 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

How to call one Form to another Form in Oracle APPS

DECLARE
   other_params                  VARCHAR2 (255);
BEGIN
   other_params :=
           'p_param1='
        || NAME_IN ('OKS_HEADER.id')
        || '&'
        || 'p_param2='
        || 'SC';
   fnd_function.EXECUTE (function_name      => 'CONTRACT_ESTIMATION'--Function Name
                       , open_flag          => 'Y'
                       , session_flag       => 'N'
                       , other_params       => other_params
                        );
END;

Query to find Contract Number from Order Number

 SELECT ooha.order_number

, okhb.contract_number

, okhb.contract_number_modifier

FROM oe_order_headers_all ooha

, okc_k_rel_objs rel

, okc_k_headers_all_b okhb

WHERE rel.chr_id = okhb.ID

AND rel.jtot_object1_code = 'OKX_ORDERHEAD'

AND rel.object1_id1 = ooha.header_id

AND ooha.org_id = okhb.org_id

AND ooha.order_number = '111111111'--Order_Number