BOM Query

SELECT msi.segment1 Item,msi.organization_id, msi.description item_Desc, bom.alternate_bom_designator Alternate_BOM,
msil.segment1 Component_Item, msil.description Component_Item_Desc,
BIC.component_quantity
FROM apps.MTL_SYSTEM_ITEMS MSI ,
apps.BOM_BILL_OF_MATERIALS BOM ,
apps.BOM_INVENTORY_COMPONENTS BIC,
apps.MTL_SYSTEM_ITEMS MSIL
WHERE BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND bom.bill_sequence_id = bic.bill_sequence_id
AND nvl(bic.disable_date, sysdate) >= SYSDATE
AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID
AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID
AND msi.segment1 = 'BOM_ITEM_TEST'
ORDER BY msi.segment1, bom.alternate_bom_designator, bic.item_num;

***************************************************************
For Online And Classroom Training on 
R12 SCM Functional
Fusion Procurement
Fusion SCM
Fusion Procure To Pay
Please Contact -
Gmail # rishitechnologies9@gmail.com
Cell # 9071883639

Query to find Details Of OM and Install Base

SELECT okh.contract_number         "Contract Number",cl.ATTRIBUTE6,
       okh.scs_code                "Code",
       ooh.order_number            "Order Number",
       TO_NUMBER(sl.line_number)   "SL Line",
       sl.lse_id                   "SL LSE ID",
       TO_NUMBER(cl.line_number)   "CL Line",
       cl.lse_id                   "CL LSE ID",
       TO_CHAR(cl.id)              "CL ID",
       msi.inventory_item_id,
       msi.segment1                "Item Number",
       cii.serial_number           "Serial Number",
       okh.start_date              "Contract Start Date",
       sl.start_date               "Line Start Date",
       cl.start_date               "Subline Start Date",
       cii.*
  FROM apps.okc_k_headers_all_b   okh,
       apps.okc_k_lines_b         sl,
       apps.okc_k_lines_b         cl,
       apps.okc_k_items           oki,
       apps.okc_k_rel_objs        rel,
       apps.csi_item_instances    cii,
       apps.mtl_system_items_b    msi,
       apps.oe_order_headers_all  ooh
 WHERE 1=1
   AND okh.id                   =  sl.chr_id
   AND cl.cle_id                =  sl.id
   AND sl.cle_id IS NULL
   AND cl.id                    =  oki.cle_id(+)
   AND oki.object1_id1          =  cii.instance_id
   AND cii.inventory_item_id    =  msi.inventory_item_id
   AND okh.inv_organization_id  =  msi.organization_id
   AND rel.chr_id               =  okh.id
   AND rel.jtot_object1_code    =  'OKX_ORDERHEAD'
   AND rel.object1_id1          =  ooh.header_id
   AND oki.object1_id2          =  '#'
   AND okh.contract_number = 'US000016348'
  -- AND cii.serial_number = 'FM1407CB00D'
 -- AND TO_CHAR(cl.id) = '97803583007615608399812980983947089161'
   -- AND okh.cust_po_number       =  ooh.cust_po_number
   -- AND okh.ship_to_site_use_id  =  ooh.ship_to_org_id
   /* if subline start date is earlier than line start date */
   -- AND sl.start_date > cl.start_date
   -- AND sl.line_number      = 1
 ORDER BY okh.contract_number,
       TO_NUMBER(sl.line_number),
       TO_NUMBER(cl.line_number);

How to cancel Scheduled Concurrent Programs

Use below query to cancel scheduled concurrent programs.

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N';

COMMIT;

R12 - Query to find Supplier, Sites and Contact Information

Hi Friends,

Today i am posting Query to find Supplier, Sites and Contact Information.

AP_SUPPLIERS                    
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

In R12, Contact person information is stored in HZ_PARTIES table not in ap_supplier_contacts.

SELECT asp.vendor_id ,
asp.segment1 "Supplier Num" ,
asp.vendor_name "Supplier Name" ,
ass.vendor_site_code "site name" ,
hou.name "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.email_address
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.org_party_site_id = ass.party_site_id
AND asp.vendor_name = 'LS Supplier';

Tips For SQL Query Tunning

Use below tips to increase sql query performance.
1. The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
EX: select * from table;
    select col1,col2,.... from table;  -- faster when compared to previous once
   
2. Try to avoid  HAVING clause to filter the records..
3. EXISTS is efficient when most of the filter criteria is in the main query.
4. IN is efficient when most of the filter criteria is in the sub-query.
5. Separate all words with a single space.
6. Use any harcoded values in last of the where clause. Because sql engine execute the statements from last.
EX: select a.*  from emp a, dept b
      where a.deptno = b.deptono
      and b.deptno = 40;

Query to Find Responsibilities for a Given Concurrent Programs

SELECT DISTINCT fcpl.user_concurrent_program_name
,          fcp.concurrent_program_name
,          fapp.application_name
,          frg.request_group_name
,          fnrtl.responsibility_name
FROM fnd_request_groups frg
,          fnd_application_tl fapp
,          fnd_request_group_units frgu
,          fnd_concurrent_programs fcp
,          fnd_concurrent_programs_tl fcpl
,          fnd_responsibility fnr
,          fnd_responsibility_tl fnrtl
WHERE frg.application_id =fapp.application_id
AND   frg.application_id = frgu.application_id
AND   frg.request_group_id = frgu.request_group_id
AND   frg.request_group_id = fnr.request_group_id
AND   frg.application_id = fnr.application_id
AND   fnr.responsibility_id = fnrtl.responsibility_id
AND   frgu.request_unit_id = fcp.concurrent_program_id
AND   frgu.unit_application_id = fcp.application_id
AND   fcp.concurrent_program_id = fcpl.concurrent_program_id
AND   fcpl.user_concurrent_program_name like 'Sales%Order%Ackn%'
AND   fnrtl.language = 'us'
AND   fapp.language = 'us'

Onhand Quantity On a Given Date

Recentlry i have worked on one report, The main purpose of the report is , based on item, oraganizarion and subiventory it need to display the onhand qty for a gievn date. i used below code in formula column of report to calculate onhand qty on a given perticular date.

FUNCTION CF_LAST_BALFormula RETURN NUMBER IS
   v_onhand_qty   NUMBER;
   v_last_qty     NUMBER;
   v_target_qty   NUMBER;
BEGIN
   SELECT SUM(transaction_quantity)
   INTO   v_onhand_qty
   FROM   mtl_onhand_quantities_detail
   WHERE  inventory_item_id = :inventory_item_id
   AND    organization_id   = :organization_id
   AND    subinventory_code = :Subinventory;
  
   SELECT NVL(SUM(Transaction_quantity),0)
   INTO   v_last_qty
   FROM   mtl_material_transactions
   WHERE  inventory_item_id = :inventory_item_id
   AND    organization_id   = :organization_id
   AND    subinventory_code = :Subinventory
   AND trunc(NVL(transaction_date,creation_date))>= TRUNC(TO_DATE(:p_last_date,'RRRR/MM/DD'));

      v_target_qty:=(v_onhand_qty)-(v_last_qty);

   RETURN(v_target_qty);
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

Link Between Purchase Order and Requisition

SELECT prh.segment1 req_number
      ,prh.authorization_status
      ,prl.line_num req_line_num
      ,prl.item_description req_item_description
      ,prl.unit_price req_unit_price
      ,prl.quantity req_quantity
      ,pd.req_header_reference_num
      ,pd.req_line_reference_num
      ,pl.line_num
      ,pl.item_description
      ,pl.quantity
      ,pl.amount
      ,ph.segment1 po_number
      ,prd.distribution_id
      ,pd.req_distribution_id
  FROM po_requisition_headers_all prh
      ,po_requisition_lines_all   prl
      ,po_req_distributions_all   prd
      ,po_distributions_all       pd
      ,po_line_locations_all      pll
      ,po_lines_all           pl
      ,po_headers_all             ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)