Oracle EBS Useful Links

Oracle EBS Useful Links

Metalink
Oracle Tables(etrm)
ETRM Live
Oracle Stuff
Apex Blog

Item Category Creation

Useful Metalink Notes:

How to create a Category Set and Assign Items to Categories (Doc ID 423551.1)

Implement Locator in Existing Subinventories - Stock Exists

 

Change a existing Sub-Inventory to Locator Subinventory in Oralce EBS R12:

The cause is that on-hand quantity or pending transactions or uncosted transactions exist in the subinventory S99.
You can confirm if records exist by the following queries

To implement the solution, please execute the following steps first in a test environment.

Onhand Quantity:

SELECT count(*)
FROM mtl_onhand_quantities_detail
WHERE organization_id = 106
AND subinventory_code = 'S99';

Pending Transactions:

SELECT count(*)
FROM mtl_material_transactions_temp
WHERE organization_id = 106
AND (subinventory_code = 'S99' or transfer_subinventory = 'S99');

Uncosted Transactions:

SELECT count(*)
FROM mtl_material_transactions
WHERE organization_id = 106
AND subinventory_code = 'S99'
AND costed_flag in ('N','E');

ACTION PLAN
------------------------------------

Issue all associated on-hand quantities from the S99 subinventory or move to a temporary subinventory before trying to update the locator control.
Also process any pending transactions or uncosted transactions that are associated to S99 subinventory.

Once the subinventory has 0 on-hand quantities and no pending transactions and no pending uncosted transactions you can update the Locator Control setup from 'None' to 'Prespecified'.

Query to find ISO Shipment Details in EBS R12

 /*Query to find ISO Shipment Details in EBS R12*/

SELECT (SELECT name
        FROM   hr_organization_units hou
        WHERE  hou.organization_id = ooha.org_id)
       From_Organization,
       (SELECT Max(transfer_organization_id)
        FROM   mtl_material_transactions mmt
        WHERE  mmt.transaction_type_id = 62 --Int Order Intr Ship
               AND mmt.transaction_action_id = 21 --Intransit shipment
               AND mmt.transaction_source_type_id = 8 --Internal order
               AND mmt.source_code = 'ORDER ENTRY'
               AND mmt.shipment_number = wnd.name
               AND mmt.trx_source_delivery_id = wnd.delivery_id
               AND mmt.organization_id = wnd.organization_id)
       TRANSFER_ORGANIZATION_ID,
       (SELECT pla.organization_id
        FROM   po_location_associations_all pla,
               hz_cust_acct_sites_all hcas,
               hz_cust_site_uses_all hcsua
        WHERE  pla.address_id = hcas.cust_acct_site_id
               AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
               AND ooha.ship_to_org_id = hcsua.site_use_id)
       TRANSFER_ORGANIZATION_ID2,
       ooha.order_number,
       ooha.ordered_date,
       (SELECT hp.party_name
        FROM   hz_parties hp,
               hz_cust_accounts hca
        WHERE  hp.party_id = hca.party_id
               AND hca.cust_account_id = ooha.sold_to_org_id)
       CUSTOMER_NAME,
       --wdd.DELIVERY_DETAIL_ID ,   wda.delivery_detail_id,
       wnd.delivery_id
       shipment_number,
Nvl(Nvl(oola.actual_shipment_date, oola.actual_fulfillment_date), wnd.confirm_date) shipping_date,
oola.ordered_item item,
msib.description,
oola.pricing_quantity Quantity,
oola.pricing_quantity_uom UOM,
oola.unit_selling_price,
(SELECT item_cst.item_cost item_average_cost
 FROM   cst_item_costs item_cst
 WHERE  item_cst.cost_type_id = 2 --(select cost_type.cost_type_id from cst_cost_types cost_type where cost_type.cost_type = 'Average')
        AND item_cst.inventory_item_id = msib.inventory_item_id
        AND item_cst.organization_id = msib.organization_id)
       avg_cost,
wdd.shipped_quantity,
(SELECT SUM(rcv.quantity)
 FROM   rcv_shipment_headers rsh,
        rcv_shipment_lines rsl,
        rcv_transactions rcv
 WHERE  rsh.shipment_header_id = rsl.shipment_header_id
        AND rcv.transaction_type = 'RECEIVE'
        AND rcv.shipment_line_id = rsl.shipment_line_id
        AND rcv.shipment_header_id = rsl.shipment_header_id
        AND rsl.item_id = msib.inventory_item_id
        AND rsl.from_organization_id = ooha.ship_from_org_id
        AND rsh.shipment_num = wnd.name)
       received_qty,
(SELECT rsh.receipt_num
 FROM   rcv_shipment_headers rsh
 WHERE  rsh.receipt_source_code = 'INTERNAL ORDER'
        AND rsh.shipment_num = wnd.name
        AND rsh.organization_id = ooha.ship_from_org_id)
       RECEIPT_NUM
FROM   oe_order_headers_all ooha,
       oe_order_lines_all oola,
       wsh_new_deliveries wnd,
       wsh_delivery_assignments wda,
       wsh_delivery_details wdd,
       mtl_system_items_b msib
WHERE  1 = 1
       AND oola.ordered_item = msib.segment1
       AND ooha.ship_from_org_id = msib.organization_id
       AND wdd.source_header_id = ooha.header_id
       AND wdd.source_line_id = oola.line_id
       AND ooha.header_id = oola.header_id
       AND wdd.delivery_detail_id = wda.delivery_detail_id
       AND wnd.delivery_id = wda.delivery_id
       AND ooha.order_source_id = (SELECT order_source_id
                                   FROM   oe_order_sources
                                   WHERE  name = 'Internal') --10
       --and ooha.ORDER_NUMBER='-------'
       AND wnd.delivery_id = <----->
; 

Oracle AR transaction Query for EBS R12

 Oracle AR transaction query

SELECT RCT.customer_trx_id,
       RCT.trx_number "TRX NUMBER",
       RCT.trx_date   "TRX DATE",
       RCG.gl_date,
       RCT.purchase_order,
       OOD.organization_id,
       OOD.organization_name,
       RCL.description,
       HCA.account_number,
       HP.party_name,
       RCL.line_type  AR_LINE_TYPE,
       SUM(( Decode(RCT.invoice_currency_code, 'INR', RCG.amount * 1,
                                               RCG.amount * RCT.exchange_rate) )
       )
                      TOTAL_INV_AMOUNT
FROM   org_organization_definitions OOD,
       hz_cust_accounts HCA,
       hz_parties HP,
       ra_customer_trx_all RCT,
       ra_customer_trx_lines_all RCL,
       ra_cust_trx_line_gl_dist_all RCG
WHERE  RCT.customer_trx_id = RCL.customer_trx_id
       AND RCL.customer_trx_line_id = RCG.customer_trx_line_id
       AND RCT.bill_to_customer_id = HCA.cust_account_id
       AND rct.interface_header_context = 'ORDER ENTRY'
       AND rcl.interface_line_context = 'ORDER ENTRY'
       AND HP.party_name = :p_customer_name
       AND RCT.trx_number = '-----------'
       AND HCA.party_id = HP.party_id
       AND To_number(RCT.interface_header_attribute10) = OOD.organization_id (+)
       AND RCT.trx_date BETWEEN :P_FROM_DATE AND :P_TO_DATE
GROUP  BY RCT.customer_trx_id,
          RCT.trx_number,
          RCT.cust_trx_type_id,
          RCT.trx_date,
          RCG.gl_date,
          RCT.customer_trx_id,
          RCT.purchase_order,
          OOD.organization_name,
          RCL.description,
          HCA.account_number,
          OOD.organization_id,
          HP.party_name,
          RCL.line_type;