OM Line Defaulting Rules Query

select
d.database_object_display_name  OBJECT,
d.attribute_display_name ATTRIBUTE,
d.precedence,
d.display_name CONDITION,
d.system_flag SEEDED,
d.enabled_flag ENABLED,
k.sequence_no,
l.meaning SOURCE_TYPE,
decode (src_type, 'API', src_api_pkg || '.' || src_api_fn, 'CONSTANT', src_constant_value, 'SYSTEM', src_system_variable_expr, 'SEQUENCE', src_sequence_name, 'DATABASE',
src_database_object_name || '.' || src_attribute_code, 'WAD_ATTR', ' ', 'WAD_OBJATTR',' ', src_constant_value) DEFAULT_SOURCE_VALUE
--src_type,
--k.attribute_code
from oe_def_attr_condns_v d,
oe_lookups l,
oe_def_attr_rules_v k
where
d.database_object_name = k.database_object_name and
d.attr_def_condition_id = k.attr_def_condition_id and
d.condition_id = k.condition_id and
d.attribute_code = k.attribute_code and
k.src_type not in ('RELATED_RECORD','SAME_RECORD','PROFILE_OPTION') and
l.lookup_type = 'DEFAULTING_SOURCE_TYPE' and
(k.src_type = l.lookup_code or
(k.src_type = k.attribute_code and l.lookup_code = 'CONSTANT'))
and d.database_object_name = 'OE_AK_ORDER_LINES_V'
UNION
select
d.database_object_display_name,
d.attribute_display_name,
d.precedence,
d.display_name,
d.system_flag,
d.enabled_flag,
k.sequence_no,
l.meaning ,
a.object_name || '.' || a.name source
--src_type,
--k.attribute_code
from oe_def_attr_condns_v d,
oe_lookups l,
oe_def_attr_rules_v k,
ak_object_attributes_vl a
where
d.database_object_name = k.database_object_name and
d.attr_def_condition_id = k.attr_def_condition_id and
d.condition_id = k.condition_id and
d.attribute_code = k.attribute_code and
a.database_object_name (+) = k.src_database_object_name and
a.attribute_code (+) = k.src_attribute_code and
l.lookup_type = 'DEFAULTING_SOURCE_TYPE' and
k.src_type = l.lookup_code and
k.src_type = 'RELATED_RECORD'
and d.database_object_name = 'OE_AK_ORDER_LINES_V'
union
select
d.database_object_display_name,
d.attribute_display_name,
d.precedence,
d.display_name,
d.system_flag,
d.enabled_flag,
k.sequence_no,
l.meaning ,
a.name source
--src_type,
--k.attribute_code
from oe_def_attr_condns_v d,
oe_lookups l,
oe_def_attr_rules_v k,
ak_object_attributes_vl a
where
d.database_object_name= k.database_object_name and
d.attr_def_condition_id = k.attr_def_condition_id and
d.condition_id = k.condition_id and
d.attribute_code = k.attribute_code and
a.database_object_name (+) = k.database_object_name and
a.attribute_code (+) = k.src_attribute_code and
l.lookup_type = 'DEFAULTING_SOURCE_TYPE' and
k.src_type = l.lookup_code and
k.src_type = 'SAME_RECORD'
and d.database_object_name = 'OE_AK_ORDER_LINES_V'
union
select
d.database_object_display_name,
d.attribute_display_name,
d.precedence,
d.display_name,
d.system_flag,
d.enabled_flag,
k.sequence_no,
l.meaning ,
a.user_profile_option_name source
--src_type,
--k.attribute_code
from oe_def_attr_condns_v d,
oe_lookups l,
oe_def_attr_rules_v k,
fnd_profile_options_vl a
where
d.database_object_name = k.database_object_name and
d.attr_def_condition_id = k.attr_def_condition_id and
d.condition_id = k.condition_id and
a.profile_option_name (+) = k.src_profile_option and
l.lookup_type = 'DEFAULTING_SOURCE_TYPE' and
k.src_type = l.lookup_code and
k.src_type = 'PROFILE_OPTION'
and d.database_object_name = 'OE_AK_ORDER_LINES_V'

OM Header Defaulting Rules Query

select
d.database_object_display_name  OBJECT,
d.attribute_display_name ATTRIBUTE,
d.precedence,
d.display_name CONDITION,
d.system_flag SEEDED,
d.enabled_flag ENABLED,
k.sequence_no,
l.meaning SOURCE_TYPE,
decode (src_type, 'API', src_api_pkg || '.' || src_api_fn, 'CONSTANT', src_constant_value, 'SYSTEM', src_system_variable_expr, 'SEQUENCE', src_sequence_name, 'DATABASE',
src_database_object_name || '.' || src_attribute_code, 'WAD_ATTR', ' ', 'WAD_OBJATTR',' ', src_constant_value) DEFAULT_SOURCE_VALUE
--src_type,
--k.attribute_code
from oe_def_attr_condns_v d,
oe_lookups l,
oe_def_attr_rules_v k
where
d.database_object_name = k.database_object_name and
d.attr_def_condition_id = k.attr_def_condition_id and
d.condition_id = k.condition_id and
d.attribute_code = k.attribute_code and
k.src_type not in ('RELATED_RECORD','SAME_RECORD','PROFILE_OPTION') and
l.lookup_type = 'DEFAULTING_SOURCE_TYPE' and
(k.src_type = l.lookup_code or
(k.src_type = k.attribute_code and l.lookup_code = 'CONSTANT'))
and d.database_object_name = 'OE_AK_ORDER_HEADERS_V'
UNION
select
d.database_object_display_name,
d.attribute_display_name,
d.precedence,
d.display_name,
d.system_flag,
d.enabled_flag,
k.sequence_no,
l.meaning ,
a.object_name || '.' || a.name source
--src_type,
--k.attribute_code
from oe_def_attr_condns_v d,
oe_lookups l,
oe_def_attr_rules_v k,
ak_object_attributes_vl a
where
d.database_object_name = k.database_object_name and
d.attr_def_condition_id = k.attr_def_condition_id and
d.condition_id = k.condition_id and
d.attribute_code = k.attribute_code and
a.database_object_name (+) = k.src_database_object_name and
a.attribute_code (+) = k.src_attribute_code and
l.lookup_type = 'DEFAULTING_SOURCE_TYPE' and
k.src_type = l.lookup_code and
k.src_type = 'RELATED_RECORD'
and d.database_object_name = 'OE_AK_ORDER_HEADERS_V'
union
select
d.database_object_display_name,
d.attribute_display_name,
d.precedence,
d.display_name,
d.system_flag,
d.enabled_flag,
k.sequence_no,
l.meaning ,
a.name source
--src_type,
--k.attribute_code
from oe_def_attr_condns_v d,
oe_lookups l,
oe_def_attr_rules_v k,
ak_object_attributes_vl a
where
d.database_object_name= k.database_object_name and
d.attr_def_condition_id = k.attr_def_condition_id and
d.condition_id = k.condition_id and
d.attribute_code = k.attribute_code and
a.database_object_name (+) = k.database_object_name and
a.attribute_code (+) = k.src_attribute_code and
l.lookup_type = 'DEFAULTING_SOURCE_TYPE' and
k.src_type = l.lookup_code and
k.src_type = 'SAME_RECORD'
and d.database_object_name = 'OE_AK_ORDER_HEADERS_V'
union
select
d.database_object_display_name,
d.attribute_display_name,
d.precedence,
d.display_name,
d.system_flag,
d.enabled_flag,
k.sequence_no,
l.meaning ,
a.user_profile_option_name source
--src_type,
--k.attribute_code
from oe_def_attr_condns_v d,
oe_lookups l,
oe_def_attr_rules_v k,
fnd_profile_options_vl a
where
d.database_object_name = k.database_object_name and
d.attr_def_condition_id = k.attr_def_condition_id and
d.condition_id = k.condition_id and
a.profile_option_name (+) = k.src_profile_option and
l.lookup_type = 'DEFAULTING_SOURCE_TYPE' and
k.src_type = l.lookup_code and
k.src_type = 'PROFILE_OPTION'
and d.database_object_name = 'OE_AK_ORDER_HEADERS_V'

PO Supplier Extract Query

SELECT   pv.vendor_id,
                   pvs.org_id,
                   pvs.vendor_site_id,
                   pv.VENDOR_NAME,
                   pv.segment1 SUPPLIER_NUMBER,
                   pvs.VENDOR_SITE_CODE SITE_NAME,
                   hou.name OPEARTING_UNIT,
                   pvs.ADDRESS_LINE1,
                   pvs.ADDRESS_LINE2,
                   pvs.ADDRESS_LINE3,
                   pvs.CITY,
                   pvs.STATE,
                   pvs.ZIP,
                   pvs.PROVINCE,
                   pvs.COUNTRY,
                   pv.START_DATE_ACTIVE,
                   pv.end_DATE_ACTIVE,
                   pvs.purchasing_site_flag MANAGE_SITE_PUR,
                   pvs.rfq_only_site_flag MANAGE_SITE_RFQ,
                   pvs.pay_site_flag MANAGE_SITE_PAY,
                   qrslt.pay_flag ADDRESS_PURPOSE_PAY_FLAG,
                   qrslt.pur_flag ADDRESS_PURPOSE_PUR_FLAG,
                   qrslt.rfq_flag ADDRESS_PURPOSE_RFQ_FLAG,
                   fl.MEANING HEADER_PAYMENT_METHOD,
                   pv.INVOICE_CURRENCY_CODE HEADER_INVOICE_CURRENCY_CODE,
                   pv.PAYMENT_CURRENCY_CODE HEADER_PAYMENT_CURRENCY_CODE,
                   (SELECT   name
                      FROM   ap_terms
                     WHERE   term_id = pv.terms_id)
                      HEADER_TERM_NAME,
                   fl.MEANING SITE_PAYMENT_METHOD,
                   pvs.INVOICE_CURRENCY_CODE SITE_INVOICE_CURRENCY_CODE,
                   pvs.PAYMENT_CURRENCY_CODE SITE_PAYMENT_CURRENCY_CODE,
                   (SELECT   name
                      FROM   ap_terms
                     WHERE   term_id = pvs.terms_id)
                      SITE_TERM_NAME
            --INACTIVE_DATE
            FROM   ap_suppliers pv,
                   ap_supplier_sites_all pvs,
                   hr_operating_units hou,
                   fnd_lookups fl,
                   fnd_lookups fl1,
                   (SELECT   hps.party_site_id,
                             hps.party_site_name,
                             DECODE (pay.site_use_type, NULL, 'N', 'Y')
                                AS pay_flag,
                             DECODE (pur.site_use_type, NULL, 'N', 'Y')
                                AS pur_flag,
                             DECODE (rfq.site_use_type, NULL, 'N', 'Y')
                                AS rfq_flag,
                             hps.last_update_date,
                             hps.end_date_active,
                             hps.start_date_active,
                             p_notes.notes,
                             hcp1.phone_area_code,
                             hcp1.phone_number,
                             hcp1.contact_point_id AS phone_contact_id,
                             hcp1.object_version_number
                                AS phone_object_version_number,
                             hcp2.email_address AS hcp_email,
                             hcp2.contact_point_id AS email_contact_id,
                             hcp2.object_version_number
                                AS email_object_version_number,
                             hcp3.object_version_number
                                AS fax_object_version_number,
                             hcp3.phone_area_code AS fax_area_code,
                             hcp3.phone_number AS fax_number,
                             hcp3.contact_point_id AS fax_contact_id,
                             hzl.address1,
                             hzl.address2,
                             hzl.address3,
                             hzl.address4,
                             hzl.city,
                             hzl.state,
                             hzl.province,
                             hzl.county,
                             hzl.country,
                             hzl.postal_plus4_code,
                             hzl.postal_code,
                             hzl.location_id,
                             hps.party_id AS party_id,
                             hps.status AS status
                      FROM   hz_party_sites hps,
                             hz_party_site_uses pay,
                             hz_party_site_uses pur,
                             hz_party_site_uses rfq,
                             pos_address_notes p_notes,
                             hz_contact_points hcp1,
                             hz_contact_points hcp2,
                             hz_contact_points hcp3,
                             hz_locations hzl
                     WHERE   hps.location_id = hzl.location_id
                             AND NVL (hps.end_date_active, SYSDATE) >=
                                   SYSDATE
                             AND pay.party_site_id(+) = hps.party_site_id
                             AND pur.party_site_id(+) = hps.party_site_id
                             AND rfq.party_site_id(+) = hps.party_site_id
                             -- and hps.party_site_id=1488182
                             AND p_notes.party_site_id(+) = hps.party_site_id
                             AND pay.status(+) = 'A'
                             AND pur.status(+) = 'A'
                             AND rfq.status(+) = 'A'
                             AND NVL (pay.end_date(+), SYSDATE) >= SYSDATE
                             AND NVL (pur.end_date(+), SYSDATE) >= SYSDATE
                             AND NVL (rfq.end_date(+), SYSDATE) >= SYSDATE
                             AND NVL (pay.begin_date(+), SYSDATE) <= SYSDATE
                             AND NVL (pur.begin_date(+), SYSDATE) <= SYSDATE
                             AND NVL (rfq.begin_date(+), SYSDATE) <= SYSDATE
                             AND pay.site_use_type(+) = 'PAY'
                             AND pur.site_use_type(+) = 'PURCHASING'
                             AND rfq.site_use_type(+) = 'RFQ'
                             AND hcp1.owner_table_id(+) = hps.party_site_id
                             AND hcp1.CONTACT_POINT_TYPE(+) = 'PHONE'
                             AND hcp1.phone_line_type(+) = 'GEN'
                             AND hcp1.status(+) = 'A'
                             AND hcp1.owner_table_name(+) = 'HZ_PARTY_SITES'
                             AND hcp1.primary_flag(+) = 'Y'
                             AND hcp2.owner_table_id(+) = hps.party_site_id
                             AND hcp2.CONTACT_POINT_TYPE(+) = 'EMAIL'
                             AND hcp2.status(+) = 'A'
                             AND hcp2.owner_table_name(+) = 'HZ_PARTY_SITES'
                             AND hcp2.primary_flag(+) = 'Y'
                             AND hcp3.owner_table_id(+) = hps.party_site_id
                             AND hcp3.CONTACT_POINT_TYPE(+) = 'PHONE'
                             AND hcp3.phone_line_type(+) = 'FAX'
                             AND hcp3.status(+) = 'A'
                             AND hcp3.owner_table_name(+) = 'HZ_PARTY_SITES')
                   QRSLT
           WHERE       pv.vendor_id = pvs.vendor_id
                   AND QRSLT.party_site_id = pvs.party_site_id
                   AND pvs.org_id = hou.organization_id
                   AND fl.LOOKUP_TYPE = 'OKL_AP_PAYMENT_METHOD'
                   AND pv.PAYMENT_METHOD_LOOKUP_CODE = fl.LOOKUP_CODE(+)
                   AND fl1.LOOKUP_TYPE = 'OKL_AP_PAYMENT_METHOD'
                   AND pvs.PAYMENT_METHOD_LOOKUP_CODE = fl1.LOOKUP_CODE(+)
                   AND NVL (pv.end_DATE_ACTIVE, SYSDATE) >= SYSDATE--and pv.end_DATE_ACTIVE <= nvl(sysdate, pv.end_DATE_ACTIVE)
--  AND pv.SEGMENT1 = '156336'
--and pvs.org_id =111
        

Difference between Value sets and Lookups

1. Value sets can be attached to parameters of a concurrent program, whereas Lookups can't.
2. Certain types of Lookups are maintainable by the users too, for example HR Users will maintain "Ethnic Minority" lookups. Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators.
3. Value sets can contain values that are a result of an SQL Statement. Hence it is possible to make Value Set list of values dynamic. On the contrary, Lookup Codes are Static list of values.

BOM Tables?

BOM_BILL_OF_MATERIALS
BOM_INVENTORY_COMPONENTS
BOM_OPERATIONAL_ROUTINGS
BOM_OPERATION_SEQUENCES
BOM_OPERATION_RESOURCES

`Query to findout the cancelled WIP Jobs

select
hou.name "OPERATING_UNIT",
ood.ORGANIZATION_CODE,
wdj.WIP_ENTITY_NAME "JOB",
wdj.JOB_TYPE_MEANING "JOB_TYPE", 
msi.segment1 "ASSEMBLY",
msi.DESCRIPTION "ASSEMBLY_DESCRIPTION",
wdj.CLASS_CODE,
wdj.NET_QUANTITY,
wdj.STATUS_TYPE_DISP,
wdj.ATTRIBUTE_CATEGORY "CONTEXT",
wdj.ATTRIBUTE1 "SCHEDULE_GROUP",
wdj.ATTRIBUTE2 "PRODUCTION_LINE",
wdj.ATTRIBUTE3 "Printed",
wdj.ATTRIBUTE10 "SALES_ORDER",
wdj.ATTRIBUTE11 "SALES_ORDER",
wdj.ATTRIBUTE12 "CUSTOMER_NAME",
wdj.ATTRIBUTE15 "ENVS_OPEN_QUANTITY"
 from WIP_DISCRETE_JOBS_V wdj,
 mtl_system_items_b msi,
 org_organization_definitions ood,
 hr_operating_units hou
  where wdj.PRIMARY_ITEM_ID  = msi.INVENTORY_ITEM_ID
  and wdj.ORGANIZATION_ID = msi.ORGANIZATION_ID
  and wdj.ORGANIZATION_ID = ood.ORGANIZATION_ID
  and msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
   and ood.OPERATING_UNIT = hou.ORGANIZATION_ID
 -- and wdj.WIP_ENTITY_NAME ='100476'    
  and wdj.STATUS_TYPE =7

link between mtl_system_items_b and mtl_item_revisions_vl

select
ood.organization_code,
msb.SEGMENT1 ITEM_NUMBER,
msb.DESCRIPTION,
msb.inventory_item_status_code ITEM_STATUS,
mir.REVISION_LABEL,
mir.EFFECTIVITY_DATE,
mir.IMPLEMENTATION_DATE,
mir.DESCRIPTION  REVISION_DESCRIPTION
from mtl_system_items_b msb,
mtl_item_revisions_vl mir,
org_organization_definitions ood
where msb.INVENTORY_ITEM_ID = mir.INVENTORY_ITEM_ID
and msb.organization_id = mir.organization_id
and msb.organization_id = ood.organization_id
and mir.organization_id = ood.organization_id
--and msb.INVENTORY_ITEM_ID =31333
--and  msb.organization_id=143
and mir.IMPLEMENTATION_DATE  = (select max(mirv.IMPLEMENTATION_DATE) from  mtl_item_revisions_vl mirv where mirv.INVENTORY_ITEM_ID = mir.INVENTORY_ITEM_ID
                                and  mir.organization_id =mirv.organization_id )                          
order by ood.organization_code,
msb.SEGMENT1