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

Oracle Apps R12 TCA Customer Information Extract

SELECT hou.name source_ou, hp.party_type, hp.party_name customer_name, hp.known_as, --Account Information -- hca.account_number customer_number, hca.account_name, NULL orig_system_customer_ref, hca.customer_type, hcsu.location, hcsu.site_use_code, hcsub.location bill_to_loc_shipto, hcsu.primary_flag site_primary_flag, hcsu.status site_status, hps.party_site_name, hps.party_site_number, hcsu.primary_flag primary_site_use_flag, NULL orig_system_address_ref, --Site Address Information -- hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.state, hl.postal_code, --hl.country, ftv.territory_short_name, hcsu.gl_id_rec gl_id_rec_acnt_code, hcsu.gl_id_rev gl_id_rev_acnt_code, hcsu.tax_code, hcpc.name customer_profile_class_name, ac.name collector_name, rt.name payment_terms, hps.addressee, hcsu.ship_sets_include_lines_flag, hcsu.arrivalsets_include_lines_flag, hcsu.sched_date_push_flag, hcsu.freight_term, hcsu.fob_point, hcsu.ship_via, hcsu.ship_partial, hcsu.gsa_indicator, hcp.credit_checking, hcp.discount_terms, hcp.dunning_letters, hcp.interest_charges, hcp.send_statements, hcp.credit_balance_statements, hcp.credit_hold, hcp.standard_terms, hcp.interest_period_days, hcp.statement_cycle_id, hcp.tax_printing_option, hcp.auto_rec_incl_disputed_flag FROM hz_parties hp, hz_party_sites hps, hz_cust_accounts_all hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, hz_cust_site_uses_all hcsub, hz_customer_profiles hcp, hz_cust_profile_classes hcpc, hz_locations hl, fnd_territories_vl ftv, ra_terms rt, ar_collectors ac, hr_operating_units hou WHERE hp.party_id = hca.party_id AND hca.cust_account_id = hcas.cust_account_id AND hps.party_site_id = hcas.party_site_id AND hcp.site_use_id (+) = hcsu.site_use_id AND hps.location_id = hl.location_id AND hcsub.site_use_id (+) = hcsu.bill_to_site_use_id AND hp.status = 'A' AND hcas.status = 'A' AND hca.status = 'A' AND hcsu.status = 'A' AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id AND rt.term_id (+) = hcsu.payment_term_id AND hcp.profile_class_id = hcpc.profile_class_id (+) AND hcsu.org_id = hou.organization_id AND ac.collector_id (+) = hcp.collector_id AND ftv.territory_code (+) = hl.country AND hou.name = :P_OU_NAME