Master Data (Item, Supplier, Customer, Bank, Item-Stock, Formula, Routing, Resource, Operation & Activity) fetching Queries from Oracle Apps tables

/*************** Item Master Fetching query **************************/

SELECT msi.segment1 icode, msi.description, mst.long_description,
msi.primary_uom_code
FROM mtl_system_items_b msi, mtl_system_items_tl mst
WHERE msi.organization_id = 82
AND ( msi.inventory_item_id = mst.inventory_item_id
AND msi.organization_id = mst.organization_id
)
ORDER BY msi.segment1, msi.description


/*************** Supplier Master Fetching query *********************/

SELECT ap.segment1 vendor_code, ap.vendor_name, aps.vendor_site_code, aps.address_line1,
aps.address_line2, aps.address_line3, aps.country, aps.city,
aps.state, aps.zip, jat.PAN_NO, jcv.ST_REG_NO, jcv.CST_REG_NO, jcv.VAT_REG_NO, jcv.SERVICE_TAX_REGNO
FROM ap_suppliers ap, ap_supplier_sites_all aps, JAI_CMN_VENDOR_SITES jcv, jai_ap_tds_vendor_hdrs jat
WHERE ap.vendor_id = aps.vendor_id
and (aps.VENDOR_ID = jcv.VENDOR_ID(+) and aps.VENDOR_SITE_ID = jcv.VENDOR_SITE_ID(+))
and (aps.VENDOR_ID = jat.VENDOR_ID(+) and aps.VENDOR_SITE_ID = jat.VENDOR_SITE_ID(+))
ORDER BY ap.SEGMENT1, ap.vendor_name


/*************** Customer Master Fetching query *********************/

select ac.CUSTOMER_NUMBER, ac.CUSTOMER_NAME, decode(hl.ADDRESS2, null, hl.ADDRESS1, hl.ADDRESS1||','||hl.ADDRESS2) address, hl.CITY,
hl.STATE, hl.COUNTRY, hl.POSTAL_CODE
from ar_customers ac, hz_cust_site_uses_all hcu, hz_cust_acct_sites_all hac,
hz_locations hl
where
ac.CUSTOMER_ID = hac.CUST_ACCOUNT_ID and hac.CUST_ACCT_SITE_ID = hcu.CUST_ACCT_SITE_ID and hcu.ORIG_SYSTEM_REFERENCE = hl.ORIG_SYSTEM_REFERENCE
group by ac.CUSTOMER_NUMBER, ac.CUSTOMER_NAME, hl.ADDRESS1, hl.ADDRESS2, hl.CITY,
hl.STATE, hl.COUNTRY, hl.POSTAL_CODE
order by ac.CUSTOMER_NUMBER, ac.CUSTOMER_NAME, hl.ADDRESS1 asc

/************** Item-Stock Master Fetching query ********************/

SELECT msi.segment1 icode, msi.description item, ood.ORGANIZATION_NAME ||' - '||ood.ORGANIZATION_CODE Inv_org,
mln.lot_number lot_no,
mmt.subinventory_code subinv_code, mmt.transaction_quantity tran_qty,
mmt.actual_cost rate
FROM mtl_material_transactions mmt,
mtl_system_items_b msi,
mtl_transaction_lot_numbers mln,
org_organization_definitions ood
WHERE mmt.transaction_type_id = 42
AND mmt.transaction_date = TO_CHAR ('30-jun-2011')
AND ( mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
)
AND ( mmt.inventory_item_id = mln.inventory_item_id
AND mmt.organization_id = mln.organization_id
AND mmt.transaction_id = mln.transaction_id
)
and mmt.ORGANIZATION_ID = ood.ORGANIZATION_ID

/************** Bank Master Fetching Query ***************************/

SELECT b.bank_name, b.bank_branch_name, a.bank_account_num,
a.bank_account_name
FROM ce_bank_accounts a, ce_bank_branches_v b
WHERE (a.bank_id = b.bank_party_id AND a.bank_branch_id = b.pk_id)

/************** Formula Master Fetching Query ************************/

SELECT ffm.formula_no, ffm.formula_desc1 formula_name,
DECODE (fmd.line_type, 1, 'Product', 'Ingredient') TYPE,
msi.segment1 icode, ood.organization_code org, fmd.qty,
fmd.detail_uom,
DECODE (ffm.formula_status, 700, 'Active', 'New') status
FROM fm_matl_dtl fmd,
fm_form_mst ffm,
mtl_system_items_b msi,
org_organization_definitions ood
WHERE fmd.formula_id = ffm.formula_id
AND (fmd.inventory_item_id = msi.inventory_item_id(+)
AND fmd.organization_id = msi.organization_id(+))
AND fmd.organization_id = ood.organization_id
ORDER BY formula_no, DECODE (fmd.line_type, 1, 'Product', 'Ingredient') DESC

/************** Routing Master Fetching Query ***********************/

Select Fr.Routing_No, Fr.Routing_Desc, go.Oprn_No, Go.Oprn_Desc, Go.Process_qty_uom
From fm_rout_hdr Fr, FM_ROUT_DTL Rd , GMD_OPERATIONS_VL go
where fr.Routing_Id = Rd.Routing_id
and Rd.Oprn_id= Go.Oprn_id order by fr.ROUTING_NO, fr.ROUTING_DESC

/************** Operation Master Fetching Query *********************/

SELECT go.oprn_no, go.oprn_desc, ga.activity, gl.activity_desc
FROM gmd_operations_vl go,
gmd_operation_activities ga,
gmd_activities_vl gl
WHERE go.oprn_id = ga.oprn_id AND ga.activity = TRIM (gl.activity)
ORDER BY go.oprn_no, ga.activity DESC

/************** Resource Master Fetching Query **********************/

SELECT rs.resources, rs.resource_desc, rs.std_usage_uom
FROM cr_rsrc_mst_vl rs
ORDER BY rs.resources, rs.resource_desc

/*************** Activity Master Fetching Query *********************/

SELECT t.activity, t.activity_desc, b.cost_analysis_code
FROM gmd_activities_tl t, gmd_activities_b b
WHERE b.activity = t.activity AND t.LANGUAGE = USERENV ('LANG')
ORDER BY t.activity, t.ACTIVITY_DESC