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
)
No comments:
Post a Comment