OPM Quality Plan Tables and API's

--Opm Quality

 gmd_specifications

gmd_specifications_b

 gmd_spec_tests

 gmd_qc_tests

 gmd_test_methods

gmd_test_classes

 gmd_units

 mtl_actions

 mtl_grades

 gmd_all_spec_vrs_vl

 gmd_inventory_spec_vrs

gmd_wip_spec_vrs

gmd_supplier_spec_vrs

 gmd_customer_spec_vrs

--OPM Quality Spec API's

 

gmd_spec_pub.CREATE_SPEC

gmd_spec_vrs_pub.CREATE_WIP_SPEC_VRS

gmd_spec_vrs_pub.CREATE_INVENTORY_SPEC_VRS

gmd_spec_vrs_pub.CREATE_CUSTOMER_SPEC_VRS

Oracle Process Manufacturung Tables and API's

Tabels:

 FM_form_mst

 FM_form_mst_b

 FM_matl_dtl

 fm_form_cls

gmd_status

 gmd_operations

 gmd_operation_activities

gmd_activities_vl

 gmd_operation_resources

cr_rsrc_dtl --plant resources

cr_rsrc_mst_vl --Grneric Resources

fm_oprn_cls

fm_rout_hdr

fm_rout_dtl

 fm_rout_cls

fm_rout_dep

gmd_recipes

 gmd_recipes_b

gmd_recipe_process_loss

gmd_recipe_validity_rules

gmd_recipe_step_materials

API's

gmd_formula_pub.INSERT_FORMULA

gmd_operations_pub.insert_operation

gmd_routings_pub.insert_routing

gmd_recipe_header.CREATE_RECIPE_HEADER

gmd_recipe_detail.CREATE_RECIPE_VR

gmd_recipe_detail.CREATE_RECIPE_MTL

Sales Order Conversion

Hi , recently I have done SO conversion, I like to share my knowledge...

This is only Psudeo Code and I hope this is helpfull...

before starting the sales order conversion we have know the some of the basic concepts.

STEP-1:
we have to create the staging tables  for sales order headers and order lines based on your data template.
after that we have to move the data by using the SQL * Loader or Direct TOAD.
once moving the data from flat file to staging tables.

STEP-2:
Creating the package in that we have to create two procedures, one is for validations and one for Loading
please find some of the validations in Sales order conversion.

-->Validate Customer Name
-->alidate Site BILL/SHIP Location
-->Customer Po number
-->Validate Operating unit
-->Validate Price List
-->Validate Order Type
-->validate Payment type
-->validate Organization Code
-->validate Currency on Order
-->validate Freight Terms
-->validate Sales Person Name
-->validate Conversion Type
-->Item Number
-->alidate UOM code


once all validations done in validation procedure same time all id's updated to staging tables after that
running the loading program the data is moving to the interface tables.
once the below interface tables are updated.

INTERFACE TABLES:

oe_headers_iface_all
oe_lines_iface_all

STEP-3

in the 3rd step we have to run the Order Import Program same time child program also running once the
status is completed normal after that checking the base tables in back end and same time we will see the
front end applications also. just we will  pass the sales order number and query.

Base Tables:

Oe_order_headers_all
Oe_order_lines_all.

Thanks a lot ......if we have any quires just give me comment...........

Validate Freight Terms

         SELECT lookup_code freight_terms_code
           FROM apps.oe_lookups
          WHERE     lookup_type = 'FREIGHT_TERMS'
                AND UPPER (lookup_code) = UPPER (p_freight_term)
             OR     UPPER (meaning) = UPPER (p_freight_term)
                AND enabled_flag = 'Y'
                AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active,TRUNC (gd_sysdate))
                      AND NVL (end_date_active,TRUNC (gd_sysdate));

Validation for Sales Person

      SELECT rs.salesrep_id
      from apps.per_all_people_f ppf,
           apps.RA_SALESREPS_ALL rs
     WHERE ppf.person_id =rs.person_id
     AND  UPPER (ppf.full_name) = UPPER (p_sales_person);

Validation for Organization Code

         SELECT organization_id
           FROM apps.org_organization_definitions
          WHERE NVL (disable_date, sysdate + 1) >= sysdate
            AND inventory_enabled_flag = 'Y'
            AND (   UPPER (organization_code) =UPPER (p_org_code)
                 OR UPPER (organization_name) = UPPER (p_org_code)
                );

Validation for Master Org  :
==================

     SELECT master_organization_id
     FROM apps.mtl_parameters
     WHERE organization_id = (SELECT organization_id FROM apps.org_organization_definitions
                              WHERE organization_code=p_org_code);
 

Validation for Order Type

SELECT DISTINCT oetl.transaction_type_id
                    FROM apps.oe_transaction_types_all oeta,
                         apps.oe_transaction_types_tl oetl
                   WHERE oeta.transaction_type_code = 'ORDER'
                     AND oetl.transaction_type_id = oeta.transaction_type_id
                     AND oeta.org_id =p_org_id
                     AND (   UPPER (oetl.NAME) = UPPER (p_order_type)
                          OR UPPER (oetl.description) = UPPER (p_order_type)
                         )
                     AND NVL (oeta.end_date_active, gd_sysdate + 1) >=gd_sysdate;

Validate Site BILL/SHIP Locations

SELECT site_use_id
           FROM apps.hz_parties hz,
                apps.hz_cust_accounts hzc,
                apps.hz_cust_acct_sites_all hzcs,
                apps.hz_cust_site_uses_all hzcsu,
                apps.hz_party_sites hps,
                apps.hz_locations hl
          WHERE hzc.party_id                = hz.party_id
            AND hzcs.cust_account_id        = hzc.cust_account_id
            AND hzcsu.cust_acct_site_id     = hzcs.cust_acct_site_id
            AND hl.location_id              = hps.location_id
            AND hps.party_site_id           = hzcs.party_site_id
            AND hps.party_id                = hz.party_id
            AND hz.status                   = 'A'
            AND hzc.status                  = 'A'
            AND hzcs.status                 = 'A'
            AND hzcsu.status                = 'A'
            AND hzcsu.org_id                = p_org_id
            AND hzc.cust_account_id         = p_customer_id
            AND UPPER (hzcsu.site_use_code) = UPPER (p_site_use_code)
            AND UPPER(hzcsu.location)       = UPPER (p_site_num);