BOM Routing Extract Query

  SELECT   DISTINCT ood.organization_code,
                    --ood.organization_id,
                    --bom.assembly_item_id,
                    msi.segment1,
                    msi.description,
                    bic.operation_seq_num,
                    bic.item_num,
                    --bic.component_item_id,
                    msi1.segment1 COMPONENT,
                    msi1.description COMPONENT_DESCRIPTION,
                    msi1.primary_uom_code,
                    bic.COMPONENT_QUANTITY,
                    bic.COMPONENT_YIELD_FACTOR,
                    --bos.OPERATION_SEQ_NUM,
                    bos.DEPARTMENT_CODE,
                    bos.EFFECTIVITY_DATE FROM_DATE,
                    bic.disable_date TO_DATE,
                    mlr.MEANING BASIS,
                    bos.OPERATION_DESCRIPTION
    --ml.meaning,
    --bic.wip_supply_type,
    -- bic.effectivity_date,
    -- bic.disable_date
    -- bic.*
    FROM   mtl_system_items_b msi,
           org_organization_definitions ood,
           bom_bill_of_materials bom,
           bom_inventory_components bic,
           mtl_system_items_b msi1,
           mfg_lookups ml,
           BOM_OPERATIONAL_ROUTINGS_V bor,
           BOM_OPERATION_SEQUENCES_V bos,
           BOM_OPERATION_RESOURCES_V borv,
           mfg_lookups mlr
   WHERE       bom.assembly_item_id = msi.inventory_item_id
           AND bom.bill_sequence_id = bic.bill_sequence_id
           AND msi.organization_id = ood.organization_id
           AND bom.organization_id = ood.organization_id
           AND msi1.inventory_item_status_code = 'Active'
           AND msi.inventory_item_status_code = 'Active'
           AND bic.component_item_id = msi1.inventory_item_id
           AND msi1.organization_id = ood.organization_id
           AND msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
           AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
           AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
           AND bic.operation_seq_num = bos.OPERATION_SEQ_NUM
           AND bos.operation_sequence_id = borv.operation_sequence_id
           AND borv.BASIS_TYPE = mlr.LOOKUP_CODE(+)
           AND mlr.lookup_type(+) = 'BOM_BASIS_TYPE'
           AND ml.lookup_code(+) = bic.wip_supply_type
           -- and bic.wip_supply_type =4
           AND NVL (bic.disable_date, SYSDATE) >= SYSDATE
           AND ml.lookup_type(+) = 'WIP_SUPPLY'
--and ood.organization_id = 143
--and  msi.segment1 in ('02010981')
--and  msi1.segment1 in ('0QP131155')
ORDER BY   ood.organization_code, msi.segment1, msi1.segment1

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