R12 BOM Get Resource Overheads

SELECT cro.* FROM bom_resources_v brv, mtl_parameters mp, cst_resource_overheads_v cro WHERE mp.organization_id = brv.organization_id AND cro.resource_id = brv.resource_id AND cro.organization_id = mp.organization_id -- AND mp.organization_code IN ( 'BGA' ) AND nvl(brv.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND brv.resource_code NOT IN ( SELECT brv1.resource_code FROM bom_resources_v brv1, mtl_parameters mp1 WHERE mp1.organization_id = brv1.organization_id AND mp1.organization_code IN ('ABC' ) ) ORDER BY brv.resource_code

R12 BOM Get Resource Rates

SELECT brv.resource_code, cst.cost_type_code, resource_rate FROM bom_resources_v brv, mtl_parameters mp, cst_resource_costs_v cst WHERE mp.organization_id = brv.organization_id AND cst.resource_id = brv.resource_id AND cst.organization_id = mp.organization_id -- AND mp.organization_code IN ( 'BGA' ) AND nvl(brv.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND brv.resource_code NOT IN ( SELECT brv1.resource_code FROM bom_resources_v brv1, mtl_parameters mp1 WHERE mp1.organization_id = brv1.organization_id AND mp1.organization_code IN ( 'ABC' ) ) ORDER BY brv.resource_code

R12 Get BOM Resource Data

SELECT brv.resource_code, brv.disable_date, brv.description, res_lkp.meaning resource_type, brv.unit_of_measure, charge_typ_lkp.meaning charge_type, ( SELECT meaning FROM apps.mfg_lookups_v WHERE lookup_type = 'BOM_BASIS_TYPE' AND lookup_code = brv.default_basis_type ) basis, expenditure_type, supply_subinventory, supply_locator_id, decode(brv.attribute1, 'Y', 'Yes', 'N', 'No', '') attribute1--"Display On Machine Shop Earned/Actual Hours Reports?" , brv.attribute2 --"Dispatch Printer?" --OUTSIDE PROCESSING---------- , decode(cost_code_type, 4, 'Yes', 3, 'No', '') outside_processing, ( SELECT segment1 FROM mtl_system_items_b msib WHERE msib.inventory_item_id = brv.purchase_item_id AND ROWNUM = 1 ) outside_process_item_code, ( SELECT segment1 FROM mtl_system_items_b msib WHERE msib.inventory_item_id = brv.billable_item_id AND ROWNUM = 1 ) billing_item_code -----COSTED------------------------------------------------- , decode(allow_costs_flag, 1, 'Yes', 2, 'No', '') costed, default_activity, decode(standard_rate_flag, 1, 'Yes', 2, 'No', '') standard_rate, ( SELECT ( segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.' || segment5 || '.' || segment6 ) x FROM gl_code_combinations WHERE code_combination_id = absorption_account ) absorption_account, ( SELECT ( segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.' || segment5 || '.' || segment6 ) x FROM gl_code_combinations WHERE code_combination_id = rate_variance_account ) rate_variance_account ------SKILLS--------a----------------------------------- , competence_id competence, '' skill_level, qualification_type_id qualification ----BATCHABLE------------------------------------------ , decode(batchable, 1, 'Yes', 2, 'No', '') batchable, min_batch_capacity, batch_window, max_batch_capacity, batch_capacity_uom, batch_window_uom FROM bom_resources_v brv, mtl_parameters mp, apps.mfg_lookups_v res_lkp, apps.mfg_lookups_v charge_typ_lkp WHERE mp.organization_id = brv.organization_id AND res_lkp.lookup_code = brv.resource_type AND res_lkp.lookup_type = 'BOM_RESOURCE_TYPE' -- AND charge_typ_lkp.lookup_code = brv.autocharge_type AND charge_typ_lkp.lookup_type = 'BOM_AUTOCHARGE_TYPE' -- AND mp.organization_code IN ( 'BGA' ) --AND brv.RESOURCE_CODE = 'ASSY' AND nvl(brv.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND NOT EXISTS ( SELECT 1 FROM bom_resources_v bdv, mtl_parameters mp--, -- BOM_DEPARTMENT_RESOURCES_V BDR1 WHERE mp.organization_id = bdv.organization_id AND mp.organization_code IN ( 'ABC' ) AND brv.resource_code = bdv.resource_code )

R12 Get Employee and Supervisor Information

SELECT DISTINCT level,e.* FROM ( SELECT DISTINCT papf.person_id, nvl(papf.employee_number, papf.attribute12) employee_number, papf.full_name "EMPLOYEE_FULL_NAME", papf.email_address, paaf.supervisor_id, papf1.employee_number "SUPERVISOR_EMP_NUMBER", papf1.full_name "SUPERVISOR_FULL_NAME" FROM apps.per_all_people_f papf, apps.per_all_assignments_f paaf, apps.per_all_people_f papf1, apps.per_person_types ppt WHERE papf.person_id = paaf.person_id AND papf1.person_id = paaf.supervisor_id AND papf.business_group_id = paaf.business_group_id AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND ppt.person_type_id = papf.person_type_id AND ppt.user_person_type <> 'Ex-employee' ) e CONNECT BY PRIOR person_id = supervisor_id START WITH employee_number = '1234567' ORDER BY employee_number select * from apps.per_all_people_f papf where papf.full_name ='Mr. James Bond'

R12 BOM Get Departments

SELECT mp.organization_code, bdv.department_code, description, disable_date, department_class_code, class_description, location_code, location_description, bdv.attribute1, bdv.attribute2 FROM bom_departments_v bdv, mtl_parameters mp WHERE mp.organization_id = bdv.organization_id AND mp.organization_code IN ( 'ABC','PQR' ) AND nvl(disable_date,(sysdate + 1)) > sysdate

R12 Generate Intended BOM

set serveroutput on size unlimited DECLARE err_msg VARCHAR2(5000); error_code NUMBER; grp_id NUMBER; session_id NUMBER; CURSOR c_get_items_for_BOM IS SELECT inventory_item_id, organization_id FROM mtl_system_items msi WHERE 1=1 AND msi.segment1 in ('ITEM_A','ITEM_B') ---- PUT YOUR ITEMS HERE AND organization_id =2; BEGIN SELECT bom_explosion_temp_s.NEXTVAL INTO grp_id FROM Dual ; SELECT bom_explosion_temp_session_s.NEXTVAL INTO session_id FROM Dual ; FOR r_get_items_for_BOM IN c_get_items_for_BOM LOOP bompexpl.exploder_userexit ( 0 --verify_flag IN NUMBER DEFAULT 0, ,r_get_items_for_BOM.organization_id -- org_id IN NUMBER, ,1 -- order_by IN NUMBER DEFAULT 1, ,grp_id -- grp_id unique value to identify current explosion -- use value from sequence bom_explosion_temp_s ,session_id -- session_id -- IN NUMBER DEFAULT 0, it is unique value to identify current session ,use value from bom_explosion_temp_session_s ,10 -- levels_to_explode IN NUMBER DEFAULT 1, ,1 -- bom_or_eng IN NUMBER DEFAULT 1, ,1 -- impl_flag IN NUMBER DEFAULT 1, ,2 -- plan_factor_flag IN NUMBER DEFAULT 2, 2 means NO ,2 -- explode_option IN NUMBER DEFAULT 2,explode_option 1 - All, 2 - Current,3 - Current and future ,2 -- module IN NUMBER DEFAULT 2, ,0 -- cst_type_id IN NUMBER DEFAULT 0, ,2 -- std_comp_flag IN NUMBER DEFAULT 0, ,1 -- expl_qty IN NUMBER DEFAULT 1, ,r_get_items_for_BOM.inventory_item_id --1681441 -- item_id IN NUMBER, ,'' -- alt_desg IN VARCHAR2 DEFAULT '', ,'' -- comp_code IN VARCHAR2 DEFAULT '', ,to_char(sysdate,'dd-mon-yy HH24:MI') -- rev_date IN VARCHAR2, ,'' --unit_number IN VARCHAR2 DEFAULT '', ,0 --release_option IN NUMBER DEFAULT 0, ,err_msg -- err_msg OUT NOCOPY VARCHAR2, ,error_code -- error_code OUT NOCOPY NUMBER ); IF err_msg is not null or error_code is not null then dbms_output.put_line(err_msg); dbms_output.put_line(error_code); END IF; END LOOP; dbms_output.put_line('Use grp_id = '||grp_id ' to query the table BOM_EXPLOSION_TEMP in this session only.'); dbms_output.put_line('session_id = '||session_id); END;