BEGIN

DBMS_APPLICATION_INFO.set_client_info (1703);
END;

Query1: /* Item MasterExtract*/

SELECT base.segment1 item_number, NULL template_name,

flv1.meaning atp_components, mar.rule_name atp_rules,
base.segment1 base_model,
DECODE (base.material_billable_flag,
'M', 'MATERIAL',
'E', 'EXPENSE',
'L', 'LABOR',
NULL
) billing_type,
base.bulk_picked_flag bulk_picked,
base.mrp_calculate_atp_flag calculate_atp,
micg.segment1 catalog_group_name, base.atp_flag check_atp,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7 cogs_account,
DECODE (base.allowed_units_lookup_code,
1, 'STANDARD',
2, 'ITEM SPECIFIC',
3, 'BOTH',
NULL
) conversion,
base.creation_date, base.cycle_count_enabled_flag cyc_count_enabled,
hr.full_name buyer, NULL default_lot_status,
NULL default_serial_status, mp.organization_code default_shipping_wh,
base.defect_tracking_on_flag defect_track_enabled,
base.description item_desc, base.attribute4 drawing_number,
base.planning_exception_set exception_set,
gcc1.segment1
|| '-'
|| gcc1.segment2
|| '-'
|| gcc1.segment3
|| '-'
|| gcc1.segment4
|| '-'
|| gcc1.segment5
|| '-'
|| gcc1.segment6
|| '-'
|| gcc1.segment7 expense_acc,
base.attribute6 export_sen_flag, base.fixed_days_supply,
base.fixed_lead_time,
base.fixed_lot_multiplier fixed_lot_size_multiplier,
base.fixed_order_quantity,
DECODE (base.ato_forecast_control,
1, 'Consume Forecast',
2, 'Consume and Derive Forecast',
3, 'None',
NULL
) forecast_control,
base.inspection_required_flag inspection_required,
DECODE (base.inventory_planning_code,
1, 'Reorder Point',
2, 'Min-Max',
3, 'Not Planned',
NULL
) inv_planning_method,
base.inventory_item_status_code item_status, base.last_update_date,
base.lead_time_lot_size leadtimelotsize,
base.list_price_per_unit list_price,
DECODE (base.location_control_code,
1, 'NO CONTROL',
2, 'PRESPECIFIED',
3, 'DYNAMIC ENTRY',
NULL
) locator_control,
NULL long_desc,
DECODE (base.lot_control_code,
1, 'NO CONTROL',
2, 'FULL CONTROL',
NULL
) lot_control,
DECODE (base.shelf_life_code,
1, 'NO SHELF LIFE CONTROL',
2, 'SHELF LIFE DAYS',
3, 'USER-DEFINED',
NULL
) lot_exp_control,
base.planning_make_buy_code make_or_buy, cic.material_cost,
base.maximum_order_quantity max_order_quantity,
base.min_minmax_quantity min_min_max_quantity,
base.max_minmax_quantity max_min_max_quantity,
base.minimum_order_quantity min_order_quantity,
DECODE (base.mrp_planning_code,
3, 'MRP planning',
4, 'MPS planning',
6, 'Not planned',
7, 'MRP and MPP planning',
8, 'MPS and MPP planning',
9, 'MPP planning',
NULL
) mrp_planning_method,
mp.organization_code,
DECODE (base.end_assembly_pegging_flag,
'A', 'Soft Pegging',
'B', 'End Assembly/ Soft Pegging',
'I', 'Hard Pegging',
'N', 'None',
'X', 'End Assembly/ Hard Pegging',
'Y', 'End Assembly Pegging',
 NULL
) pegging,
base.planner_code,
DECODE (base.planning_time_fence_code,
1, 'Cumulative total lead time',
2, 'Cumulative manufacturing lead time',
3, 'Item total lead time',
4, 'User-defined time fence',
NULL
) planning_time_fence,
DECODE (base.planning_time_fence_days,
'', NULL,
base.planning_time_fence_days
) planning_time_fence_days,
base.postprocessing_lead_time post_proc_lead_time,
base.preprocessing_lead_time prepros_lead_time,
base.primary_unit_of_measure,
base.postprocessing_lead_time proc_lead_time,
base.comms_activation_reqd_flag provisionable,
base.attribute5 rec_spare_indicator,
DECODE (base.recovered_part_disp_code,
'M', 'Fast Return',
'N', 'No Return',
'S', 'Slow Return',
NULL
) rec_part_disposition,
DECODE (base.release_time_fence_code,
1, 'Cumulative total lead time',
2, 'Cumulative manufacturing lead time',
3, 'Item total lead time',
4, 'User-defined time fence',
5, 'Do not autorelease',
6, 'Kanban Item (Do Not Release)',
NULL
) release_time_fence,
base.release_time_fence_days,
NVL ((SELECT MAX (mir.revision)
FROM mtl_item_revisions mir
WHERE base.inventory_item_id = mir.inventory_item_id
AND base.organization_id = mir.organization_id),
NULL
) revision,
NULL safety_stock_method, base.mrp_safety_stock_percent,
gcc2.segment1
|| '-'
|| gcc2.segment2
|| '-'
|| gcc2.segment3
|| '-'
|| gcc2.segment4
|| '-'
|| gcc2.segment5
|| '-'
|| gcc2.segment6
|| '-'
|| gcc2.segment7 sales_account,
DECODE (base.serial_number_control_code,
1, 'NO CONTROL',
5, 'AT RECEIPT',
 6, 'AT SALES ORDER ISSUE',
2, 'PREDEFINED',
NULL
) serial_number_control,
NULL serial_status_enabled,
base.serviceable_product_flag servicable_product,
base.serv_billing_enabled_flag ser_bill_enabled,
DECODE (base.serv_req_enabled_code,
'D', 'Disabled',
'E', 'Enabled',
'I', 'Inactive',
NULL
) ser_req_enabled,
base.shelf_life_days, mp.organization_code source_organization,
base.source_subinventory source_subinv,
DECODE (base.source_type,
1, 'Inventory',
2, 'Supplier',
3, 'Subinventory',
NULL
) source_type,
base.std_lot_size standard_lot_size, NULL support_services,
base.unit_of_issue, base.unit_volume, base.unit_weight,
base.must_use_approved_vendor_flag use_approved_supplier,
base.variable_lead_time, base.volume_uom_code, base.weight_uom_code,
mil.segment1
|| '.'
|| mil.segment2
|| '.'
|| mil.segment3
|| '.'
|| mil.segment4 wip_supplier_locator,
base.wip_supply_subinventory wip_supplier_subinventory,
base.attribute6 po_bom_print_flg,
DECODE (base.receiving_routing_id,
1, 'Standard Receipt',
2, 'Inspection Required',
3, 'Direct Delivery',
NULL
) receipt_routing
FROM apps.mtl_system_items_b base,
apps.cst_item_costs cic,
apps.mtl_atp_rules mar,
apps.fnd_lookup_values flv1,
apps.mtl_item_catalog_groups micg,
apps.gl_code_combinations gcc,
apps.gl_code_combinations gcc1,
apps.gl_code_combinations gcc2,
apps.hr_employees hr,
apps.mtl_parameters mp,
apps.mtl_item_locations mil
WHERE mp.organization_code = 'POR'
AND cic.organization_id = base.organization_id
AND cic.inventory_item_id = base.inventory_item_id
AND cic.cost_type_id = 1
--AND base.segment1 IN ('GX2-B195', '24000-342-6')
AND base.atp_rule_id = mar.rule_id(+)
AND flv1.lookup_type = 'ATP_FLAG'
AND NVL (flv1.enabled_flag, 'N') = 'Y'
 AND flv1.LANGUAGE = USERENV ('LANG')
AND TRUNC (SYSDATE) BETWEEN NVL (flv1.start_date_active, TRUNC (SYSDATE))
AND NVL (flv1.end_date_active, TRUNC (SYSDATE))
AND base.atp_components_flag = flv1.lookup_code
AND flv1.view_application_id = 3
AND base.item_catalog_group_id = micg.item_catalog_group_id(+)
AND NVL (gcc.enabled_flag, 'N') = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (gcc.start_date_active, TRUNC (SYSDATE))
AND NVL (gcc.end_date_active, TRUNC (SYSDATE))
AND base.cost_of_sales_account = gcc.code_combination_id(+)
AND base.buyer_id = hr.employee_id(+)
AND base.organization_id = mp.organization_id
AND base.expense_account = gcc1.code_combination_id(+)
AND NVL (gcc1.enabled_flag, 'N') = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (gcc1.start_date_active, TRUNC (SYSDATE))
AND NVL (gcc1.end_date_active, TRUNC (SYSDATE))
AND NVL (gcc2.enabled_flag, 'N') = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (gcc2.start_date_active, TRUNC (SYSDATE))
AND NVL (gcc2.end_date_active, TRUNC (SYSDATE))
AND base.sales_account = gcc2.code_combination_id(+)
AND TRUNC (SYSDATE) BETWEEN NVL (mil.start_date_active, TRUNC (SYSDATE))
AND NVL (mil.end_date_active, TRUNC (SYSDATE))
AND base.wip_supply_locator_id = mil.inventory_location_id(+)

/*(Not able to find these values*/

/
*template_name,long_desc,SAFETY_STOCK_METHOD,SERIAL_STATUS_ENABLED,SUPPORT_SERVICES
) */

Query2: /*On Hand Balances*/

SELECT msib.segment1 item_number, mp.organization_code, moq.subinventory_code,

msib.primary_unit_of_measure, moq.transaction_quantity, mil.segment1,
mil.segment2, mil.segment3, mil.segment4, mil.segment19 project,
mil.segment20 task, msn.serial_number, mln.lot_number,
mln.expiration_date
FROM apps.mtl_system_items_b msib,
apps.mtl_parameters mp,
apps.mtl_onhand_quantities moq,
apps.mtl_item_locations mil,
apps.mtl_lot_numbers mln,
apps.mtl_serial_numbers msn
WHERE msib.organization_id = mp.organization_id
AND mp.organization_code = 'POR'
AND msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id
AND mil.inventory_location_id = moq.locator_id
AND mil.organization_id = moq.organization_id
AND mil.organization_id = mp.organization_id
AND msib.organization_id = mln.organization_id(+)
AND msib.inventory_item_id = mln.inventory_item_id(+)
AND msib.organization_id = msn.current_organization_id(+)
AND msib.inventory_item_id = msn.inventory_item_id(+);

Query3: /*Item Categories*/

SELECT mp.organization_code, msib.segment1, mcst.category_set_name,
mcbk.concatenated_segments
FROM apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.mtl_item_categories mic,
apps.mtl_category_sets_tl mcst,
apps.mtl_categories_b_kfv mcbk
WHERE msib.organization_id = mp.organization_id
AND mp.organization_code = 'POR'
AND msib.inventory_item_id = mic.inventory_item_id
AND msib.organization_id = mic.organization_id
AND mp.organization_id = mic.organization_id
AND mic.category_set_id = mcst.category_set_id
AND mcst.LANGUAGE = 'US'
AND mic.category_id = mcbk.category_id;

Query4: /*Item Catalog*/

SELECT msib.segment1 item_number, micg.segment1 catalog_group_name,

mde.element_name, mdev.element_value
FROM apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.mtl_descriptive_elements mde,
apps.mtl_descr_element_values mdev,
apps.mtl_item_catalog_groups micg
WHERE msib.organization_id = mp.organization_id
AND mp.organization_code = 'POR'
AND msib.inventory_item_id = mdev.inventory_item_id
AND msib.item_catalog_group_id = micg.item_catalog_group_id
AND micg.item_catalog_group_id = mde.item_catalog_group_id
AND mde.element_name = mdev.element_name
AND mde.element_sequence = mdev.element_sequence;

Query5: /*Item Attachments*/

/* Item Attachment*/
SELECT msib.segment1 item_number, ad.seq_num sequence_number,
mp.organization_code item_organization, dct.user_name category_name,
dt.description document_description, dat.user_name datatype_name,
det.user_entity_name entity_name, dt.LANGUAGE LANGUAGE,
d.security_type security_type, d.publish_flag, d.usage_type,
fdlt.long_text, fdst.short_text
FROM apps.fnd_document_datatypes dat,
apps.fnd_document_entities_tl det,
apps.fnd_documents_tl dt,
apps.fnd_documents d,
apps.fnd_document_categories_tl dct,
apps.fnd_attached_documents ad,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.fnd_documents_long_text fdlt,
apps.fnd_documents_short_text fdst
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dt.LANGUAGE = USERENV ('LANG')
AND dct.category_id = d.category_id
AND dct.LANGUAGE = USERENV ('LANG')
AND d.datatype_id = dat.datatype_id
 AND dat.LANGUAGE = USERENV ('LANG')
AND ad.entity_name = det.data_object_code
AND det.LANGUAGE = USERENV ('LANG')
AND ad.pk1_value = TO_CHAR (msib.organization_id)
AND ad.pk2_value = TO_CHAR (msib.inventory_item_id)
AND msib.organization_id = mp.organization_id
AND mp.organization_code = 'TOL'
--AND msib.inventory_item_id = 806124
AND dt.media_id = fdlt.media_id(+)
AND dt.media_id = fdst.media_id(+)
ORDER BY det.user_entity_name, ad.seq_num;

Query6: /*Safety Stock */

SELECT msb.segment1 item, mp.organization_code ORGANIZATION,

mss.effectivity_date effectivity_date,
mss.safety_stock_quantity quantity
FROM apps.mtl_safety_stocks mss,
apps.mtl_parameters mp,
apps.mtl_system_items_b msb
WHERE mp.organization_id = mss.organization_id
AND mp.organization_code = 'POR'
AND mp.organization_id = msb.organization_id
AND mss.inventory_item_id = msb.inventory_item_id;

Query7: /*Item Forecast*/ -- needs to changed --

SELECT mp.organization_code organization_code,

mfd.forecast_designator forecast_designator,
msb.segment1 item,
mfd.bucket_type bucket_type,
mdt.forecast_date forecast_start_date,
mdt.rate_end_date forecast_end_date,
mdt.original_forecast_quantity quantity,
pp.name project_name,
pt.task_number task_number
FROM apps.mrp_forecast_designators mfd,
apps.mtl_parameters mp,
apps.mtl_system_items_b msb,
apps.mrp_forecast_dates mdt,
apps.org_organization_definitions ord,
apps.pa_projects pp,
apps.pa_tasks pt
WHERE mp.organization_code = 'POR'
AND ord.organization_id = mp.organization_id
AND mfd.disable_date IS NULL
AND mfd.organization_id = mp.organization_id
AND msb.organization_id = mp.organization_id
AND mdt.inventory_item_id = msb.inventory_item_id
AND mdt.organization_id = msb.organization_id
AND mdt.forecast_designator(+) = mfd.forecast_designator
--AND msb.inventory_item_id = 116629
AND pp.project_id(+) = mdt.project_id
AND pp.org_id = ord.operating_unit
AND pp.project_id = pt.project_id
AND pt.task_id(+) = mdt.task_id;
Query8: /*Bills - Product Definition*/

Query9: /*BOM Extract*/

SELECT msib1.segment1 assembly_item_number,

NVL ((SELECT MAX (mir.revision)
FROM mtl_item_revisions mir
WHERE msib1.inventory_item_id = mir.inventory_item_id
AND msib1.organization_id = mir.organization_id),
NULL
) revision,
bbom.alternate_bom_designator alternate,
msib2.segment1 component_item_number, bic.item_num,
bic.component_quantity,
DECODE (bic.optional, 1, 'Y', 2, 'N', NULL) option_value,
bic.effectivity_date,
brd.component_reference_designator comp_ref_designator,
bic.attribute1 rec_spare_part_ind,
bic.operation_seq_num operation_sequence, bic.disable_date,
bic.component_remarks comments,
bic.include_on_ship_docs include_on_ship_docs,
bic.wip_supply_type supply_type, bic.supply_subinventory sub_inventory,
mil.segment1
|| '.'
|| mil.segment2
|| '.'
|| mil.segment3
|| '.'
|| mil.segment4 sub_locator,
bic.attribute2 child_find_no, bic.attribute3 child_controlled_part,
'IMPORT' bom_flag
FROM apps.bom_inventory_components bic,
apps.bom_bill_of_materials bbom,
apps.mtl_system_items_b msib1,
apps.mtl_system_items_b msib2,
apps.bom_reference_designators brd,
apps.mtl_item_locations mil
WHERE bic.bill_sequence_id = bbom.bill_sequence_id
AND msib1.organization_id = 1704
AND msib1.inventory_item_id = bbom.assembly_item_id
AND bbom.organization_id = msib1.organization_id
AND msib2.organization_id = msib1.organization_id
AND msib2.inventory_item_id = bic.component_item_id
AND bic.component_sequence_id = brd.component_sequence_id(+)
AND TRUNC (SYSDATE) BETWEEN NVL (mil.start_date_active, TRUNC (SYSDATE))
AND NVL (mil.end_date_active, TRUNC (SYSDATE))
AND bic.supply_locator_id = mil.inventory_location_id(+);

Query10: /*Routings*/

SELECT msb.segment1 assembly_item_number,

bor.alternate_routing_designator routing_alternate,
bor.attribute1 drawing_rev,
bor.attribute2 routing_qty,
bor.attribute3 bom_alternate,
bor.attribute4,
 mrt.process_revision routing_revision,
mp.organization_code organization_code,
bos.operation_seq_num operation_seq_num,
bso.operation_code std_operation_code,
bos.reference_flag referenced,
bd.department_code department_code,
brc.resource_seq_num resource_sequence,
br.resource_code resource_code,
brc.basis_type basis,
brc.usage_rate_or_amount usage_rate,
bor.completion_subinventory completion_subinventory,
( mil.segment1
|| '.'
|| mil.segment2
|| '.'
|| mil.segment3
|| '.'
|| mil.segment4
|| '.'
|| mil.segment19
|| '.'
|| mil.segment20
) completion_locator,
bos.option_dependent_flag option_dependent_flag,
bos.operation_description routing_comments,
DECODE (bos.count_point_type, 1, 'Y', 2, 'N', NULL)
count_point_flag,
DECODE (brc.schedule_flag, 1, 'Y', 2, 'Y', NULL) schedule_flag,
DECODE (bos.count_point_type, 3, 'N', 'Y') auto_charge_flag,
DECODE (bos.backflush_flag, 1, 'Y', 2, 'N', NULL) backflush_flag,
bos.operation_lead_time_percent lead_time_percent,
bos.last_update_date last_update_date,
bos.creation_date creation_date,
1 process_flag,
bos.effectivity_date effectivity_date
FROM apps.mtl_system_items_b msb,
apps.bom_operational_routings bor,
apps.mtl_parameters mp,
apps.bom_operation_sequences bos,
apps.bom_operation_resources brc,
apps.bom_departments bd,
apps.mtl_rtg_item_revisions mrt,
apps.bom_standard_operations bso,
apps.bom_resources br,
apps.mtl_item_locations mil
WHERE msb.organization_id = mp.organization_id
AND msb.inventory_item_id = bor.assembly_item_id
AND msb.organization_id = bor.organization_id
AND mp.organization_code = 'POR'
AND bos.routing_sequence_id = bor.routing_sequence_id
AND brc.operation_sequence_id(+) = bos.operation_sequence_id
AND bos.department_id(+) = bd.department_id
AND mp.organization_id = bd.organization_id
AND mrt.inventory_item_id(+) = msb.inventory_item_id
AND mrt.organization_id = mp.organization_id
AND bso.standard_operation_id(+) = bos.standard_operation_id
AND br.resource_id = brc.resource_id
AND mil.inventory_item_id(+) = msb.inventory_item_id;
-- AND msb.inventory_item_id = 1451640
Query11: /* GL Balances */
SELECT hou.NAME company, gjl.period_name, gjh.currency_code,
gcc.concatenated_segments account_flex_field, gjl.entered_dr,
gjl.entered_cr, gjl.accounted_dr, gjl.accounted_cr,
gjs.user_je_source_name, gjc.user_je_category_name, gjl.description
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_code_combinations_kfv gcc,
gl_je_sources gjs,
gl_je_categories gjc,
apps.hr_operating_units hou,
apps.org_organization_definitions ood
WHERE gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjs.user_je_source_name = gjh.je_source
AND gjc.user_je_category_name = gjh.je_category
AND gjh.set_of_books_id = hou.set_of_books_id
AND hou.set_of_books_id = ood.set_of_books_id
AND hou.NAME = 'VLVS_USA_OU'
AND ood.organization_code = 'POR';

Query12: /*Open AR Invoices*/

--For Line Information

SELECT c.NAME trx_source, a.trx_number, a.trx_date,

e.site_number ship_to_site, d.site_number bill_to_site,
a.trx_date invoice_date, ship_date_actual date_item_shipped,
a.ship_via shiped_now, f.NAME trx_type_code, term.NAME pay_term,
b.extended_amount, a.comments, b.unit_selling_price,
b.quantity_invoiced, a.invoice_currency_code, a.exchange_rate,
b.sales_order REFERENCE, NULL sales_per_num, a.purchase_order,
a.waybill_number, NULL sales_per_split, NULL rev_acc, NULL rec_acc,
NULL tax_acc, NULL frt_acc, NULL ora_party_id, hr.NAME ou,
b.line_number, memo.NAME memo_name, b.description, tax.tax_code,
b.vat_tax_id
FROM apps.ra_customer_trx a,
apps.ra_customer_trx_lines b,
apps.ra_batch_sources c,
apps.ar_addresses_v d,
apps.ar_addresses_v e,
apps.ra_cust_trx_types f,
apps.hr_operating_units hr,
apps.ra_terms term,
apps.ar_memo_lines memo,
apps.ar_vat_tax_vl tax
WHERE a.customer_trx_id = b.customer_trx_id
AND b.line_type = 'LINE'
AND a.batch_source_id = c.batch_source_id
AND a.bill_to_customer_id = d.customer_id
AND a.ship_to_customer_id = e.customer_id
AND a.cust_trx_type_id = f.cust_trx_type_id
AND a.term_id = term.term_id
AND a.org_id = hr.organization_id
and hr.organization_id = 1703
AND b.memo_line_id = memo.memo_line_id(+)
AND b.vat_tax_id = tax.vat_tax_id(+)
AND c.NAME = 'ORDER MANAGEMENT-VLVS'
 --AR Frieght Infomration
SELECT a.trx_number, b.extended_amount frieght_amount
FROM apps.ra_customer_trx a,
apps.ra_customer_trx_lines b,
apps.ra_batch_sources c,
apps.ra_customers d,
apps.ra_customers e,
apps.ra_cust_trx_types f,
apps.hr_operating_units hr,
apps.ra_terms term,
apps.ar_memo_lines memo,
apps.ar_vat_tax_vl tax
WHERE a.customer_trx_id = b.customer_trx_id
AND b.line_type = 'FREIGHT'
AND a.batch_source_id = c.batch_source_id
AND a.bill_to_customer_id = d.customer_id
AND a.ship_to_customer_id = e.customer_id
AND a.cust_trx_type_id = f.cust_trx_type_id
AND a.term_id = term.term_id
AND a.org_id = hr.organization_id
and hr.organization_id = 1703
AND b.memo_line_id = memo.memo_line_id(+)
AND b.vat_tax_id = tax.vat_tax_id(+)
AND c.NAME = 'ORDER MANAGEMENT-VLVS'

-- AR Tax Information
SELECT a.trx_number, b.extended_amount tax_amount
FROM apps.ra_customer_trx a,
apps.ra_customer_trx_lines b,
apps.ra_batch_sources c,
apps.ra_customers d,
apps.ra_customers e,
apps.ra_cust_trx_types f,
apps.hr_operating_units hr,
apps.ra_terms term,
apps.ar_memo_lines memo,
apps.ar_vat_tax_vl tax
WHERE a.customer_trx_id = b.customer_trx_id
AND b.line_type = 'TAX'
AND a.batch_source_id = c.batch_source_id
AND a.bill_to_customer_id = d.customer_id
AND a.ship_to_customer_id = e.customer_id
AND a.cust_trx_type_id = f.cust_trx_type_id
AND a.term_id = term.term_id
AND a.org_id = hr.organization_id
and hr.organization_id = 1703
AND b.memo_line_id = memo.memo_line_id(+)
AND b.vat_tax_id = tax.vat_tax_id(+)
AND c.NAME = 'ORDER MANAGEMENT-VLVS'

Query13: /*Open Sales Orders*/

SELECT ooh.orig_sys_document_ref legacy_order_number, hrau.NAME org_name,

qlh.NAME price_list_name, rt.NAME payment_terms,
'ORDER_PARTS MEX_VLVS' order_type, 'NO SALES CREDIT' salesrep_emp_id,
ooh.ship_to_org_id ship_to_customer_ref,
ooh.invoice_to_org_id bill_to_customer_ref,
 rc.last_name customer_contact, ooh.cust_po_number customer_po_number,
ooh.ordered_date order_date, ooh.freight_terms_code frieght_terms,
ooh.fob_point_code fob, ooh.shipping_method_code shipping_method,
ooh.request_date, ooh.flow_status_code order_status,
ooh.transactional_curr_code currency, oos.NAME order_source,
ooh.orig_sys_document_ref orig_sys_document_ref,
ooh.conversion_type_code conversion_type, ooh.conversion_rate,
ooh.conversion_rate_date conversion_date
FROM apps.hr_all_organization_units hrau,
apps.oe_order_headers ooh,
apps.qp_list_headers qlh,
apps.ra_terms rt,
apps.ra_contacts rc,
apps.oe_order_sources oos
WHERE ooh.org_id = hrau.organization_id
AND ooh.org_id = 1703
AND ooh.price_list_id = qlh.list_header_id(+)
AND ooh.payment_term_id = rt.term_id(+)
AND ooh.sold_to_contact_id = rc.contact_id(+)
AND ooh.order_source_id = oos.order_source_id;

Query14: /*Employee Migration*/

SELECT ppf.attribute1 employee_number, ppf.last_name last_name,

ppf.middle_names middle_name, ppf.first_name first_name,
ppf.title prefix, ppf.suffix suffix,
ppf.effective_start_date hire_date, ppf.date_of_birth dob,
ppf.national_identifier ssn, ppf.email_address email_address,
ppf.sex gender, ppf.effective_end_date effective_end_date,
hou.NAME organization_name, hl.location_code location_name,
pj.NAME job_title, ppf1.full_name NAME,
ppf1.employee_number supervisor_number,
pa.assignment_type assignment_type, ppf.attribute3 pay_group,
ppf.employee_number oracle_employee_number, ppf.attribute4 attribute4,
ppf.attribute5 attribute5, ppf.attribute6 attribute6,
ppf.attribute7 attribute7
FROM per_people_f ppf,
per_people_f ppf1,
hr_organization_units hou,
hr_locations hl,
per_all_assignments pa,
per_jobs_tl pj
WHERE hou.business_group_id = ppf.business_group_id
AND hou.NAME = 'POR_VLVS_USA'
AND hou.TYPE = 'HR'
AND hou.location_id = hl.location_id
AND ppf.person_id = pa.person_id(+)
AND pa.job_id = pj.job_id(+)
AND pa.supervisor_id = ppf1.person_id(+);

/*Kindly note that i was not able to extract the expense account information
which was specified in
the data file.*/

Query15: /*Suppliers*/

--Supplier header
SELECT pv.vendor_name, pv.segment1 legacy_system_vendor_number,
 pv.vendor_type_lookup_code, pv.num_1099 tax_payer_id, pv.enabled_flag,
pv.summary_flag,
-- SIC_CODE ,
pv.small_business_flag, pv.women_owned_flag,
pv.minority_group_lookup_code, pv.federal_reportable_flag,
pv.type_1099 income_tax_type, pv.state_reportable_flag,
pv.tax_reporting_name, pv.organization_type_lookup_code,
pv.enforce_ship_to_location_code, pv.receiving_routing_id,
pv.qty_rcv_tolerance, pv.qty_rcv_exception_code,
pv.days_early_receipt_allowed, pv.days_late_receipt_allowed,
pv.receipt_days_exception_code, pv.allow_substitute_receipts_flag,
pv.allow_unordered_receipts_flag, pv.inspection_required_flag,
pv.receipt_required_flag, pv.payment_priority,
pv.pay_date_basis_lookup_code, pv.terms_date_basis,
pv.payment_method_lookup_code, pv.employee_id, pv.attribute2,
-- UPDATE FLAG ,
pv.allow_awt_flag naming_standard_flag
FROM apps.po_vendors pv;

--supplier sites
SELECT pvs.org_id emr_company_name, pv.vendor_name,
pv.segment1 legacy_system_vendor_number, pvs.vendor_site_code,
pvs.address_line1, pvs.address_line2, pvs.address_line3, pvs.city,
pvs.state, pvs.province, pvs.county, pvs.zip, pvs.country,
pvs.purchasing_site_flag, pvs.rfq_only_site_flag, pvs.pay_site_flag,
pvs.supplier_notif_method, pvs.email_address, pvs.area_code, pvs.phone,
pvs.fax_area_code, pvs.fax,
pvs.accts_pay_code_combination_id supplier_liability_account,
pvs.prepay_code_combination_id supplier_pre_payment_account,
pvs.terms_id payment_terms, pvs.pay_group_lookup_code,
pvs.payment_priority, pvs.pay_date_basis_lookup_code,
pvs.terms_date_basis, pvs.payment_method_lookup_code,
pvs.invoice_currency_code, pvs.payment_currency_code,
pvs.bank_account_num supplier_bank_acct_no, pvs.vat_code tax_code,
pvs.tax_reporting_site_flag, pvs.ship_to_location_id,
pvs.bill_to_location_id, pvs.freight_terms_lookup_code,
pvs.fob_lookup_code, pvs.match_option, pvs.ship_via_lookup_code,
pvs.bank_number supplier_bank_number,
--SUPPLIER BRANCH NUMBER
pvs.vat_registration_num tax_registration_number
--Naming Standards over-ride flag
--Ship Method
FROM apps.po_vendor_sites_all pvs, apps.po_vendors pv
WHERE pvs.vendor_id = pv.vendor_id AND pvs.org_id = 1703;

Query16: /*Open Purchase Orders */

-- PO Headers
SELECT pha.comments header_description, hou.NAME organization_name,
--Action
--Document Type Code
pha.currency_code, pha.agent_id, pv.vendor_name, pvs.vendor_site_id,
pvs.vendor_site_code,
--Legacy Vendor Number
hl1.location_code ship_to_location,
hl2.location_code bill_to_location, apt.NAME payment_terms,
pha.fob_lookup_code fob, pha.freight_terms_lookup_code freight_terms,
-- Authorization Status
 pha.vendor_contact_id, pha.segment1 po_number,
pha.ship_via_lookup_code carrier,
--Vendor Number
pha.note_to_receiver,
pha.note_to_vendor, pha.creation_date,
--Amount Agreed
--Effective from
--Effective To
pha.amount_limit
FROM apps.po_headers_all pha,
apps.hr_locations hl1,
apps.hr_locations hl2,
apps.ap_terms apt,
apps.hr_operating_units hou,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs
WHERE pha.type_lookup_code = 'STANDARD'
AND pha.org_id = hou.organization_id
AND pha.org_id = 6720
AND hl1.location_id = pha.ship_to_location_id
AND hl2.location_id = pha.bill_to_location_id
AND apt.term_id = pha.terms_id
AND pvs.vendor_id = pv.vendor_id
and pha.vendor_id = pv.vendor_id
and pha.vendor_site_id = pvs.vendor_site_id
-- and trunc(pha.creation_date) > trunc(sysdate - 6)
-- and pha.segment1 like '%-T'

-- PO Lines
SELECT pha.segment1 po_number, pla.line_num line_number,
plla.shipment_num shipment_number, plt.line_type line_type,
msi.segment1 item_number, pla.item_description item_description,
pla.unit_meas_lookup_code unit_of_measure, plla.quantity quantity,
plla.need_by_date, pla.unit_price unit_price,
plla.taxable_flag tax_flag, plla.tax_name tax_name, rrh.routing_name,
--plla.INVOICE_MATCHING,
--pla.SUPPLIER_REF_NUMBER Supplier_Item_Number,
plla.attribute3 freight_fwder, plla.attribute9 ship_method,
ood.organization_code inventory_organization,
hl.location_code ship_to_location
FROM apps.po_line_locations_all plla,
apps.po_lines_all pla,
apps.po_headers_all pha,
apps.mtl_system_items msi,
apps.org_organization_definitions ood,
apps.hr_locations hl,
apps.po_line_types_tl plt,
apps.rcv_routing_headers rrh
WHERE plla.po_header_id = pla.po_header_id
AND pla.po_header_id = pha.po_header_id
and plla.po_header_id = pha.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.ship_to_organization_id = msi.organization_id
AND pla.item_id = msi.inventory_item_id
AND msi.organization_id = 6721
AND plla.ship_to_location_id = hl.location_id
AND msi.organization_id = ood.organization_id
AND pla.line_type_id = plt.line_type_id
AND pha.type_lookup_code = 'STANDARD'
 AND pha.org_id = 6720
--and plla.last_updated_by in (29015)
AND pla.line_type_id = plt.line_type_id
AND plt.source_lang = 'US'
AND rrh.routing_header_id = plla.receiving_routing_id
and trunc(pha.creation_date) > trunc(sysdate - 7)
and pha.segment1 like '%-T'

-- PO Distributions
SELECT phl.segment1 po_number, pll.line_num, pda.distribution_num,
pov.vendor_name, (SELECT full_name
FROM apps.per_people_x
WHERE person_id = phl.agent_id) agent_name,
pvs.vendor_site_code, phl.freight_terms_lookup_code, pll.quantity,
pll.quantity_committed, pll.committed_amount, pda.quantity_ordered,
phl.currency_code, phl.status_lookup_code, phl.note_to_vendor,
phl.note_to_receiver, pov.segment1 vendor_number, phl.fob_lookup_code,
phl.amount_limit, phl.approved_flag, phl.start_date_active,
phl.end_date_active,
(SELECT NAME
FROM apps.ap_terms_tl
WHERE term_id = phl.terms_id
AND LANGUAGE = USERENV ('lang')) pay_terms,
(SELECT line_type
FROM apps.po_line_types
WHERE line_type_id = pll.line_type_id) line_type,
(SELECT segment1
FROM apps.mtl_system_items_b
WHERE inventory_item_id = pll.item_id AND ROWNUM = 1) item_number,
pll.item_description, pll.unit_price, pll.taxable_flag, pll.tax_name,
pll.matching_basis, unit_meas_lookup_code uom, destination_type_code,

-- (select segment1 from pa_projects_all where project_id= pda.project_id)

proj_num,
(SELECT location_code
FROM apps.hr_locations_all
WHERE location_id =
(SELECT ship_to_location_id
FROM apps.po_line_locations_all
WHERE po_header_id = phl.po_header_id
AND po_line_id = pll.po_line_id
AND ROWNUM = 1
AND org_id =
((SELECT organization_id
FROM apps.hr_all_organization_units hrorg
WHERE hrorg.NAME = 'VLVS_USA_OU'))))
ship_to_location,
(SELECT location_code
FROM apps.hr_locations_all hr
WHERE location_id = phl.bill_to_location_id
AND ROWNUM = 1) bill_to_location,
(SELECT location_code
FROM apps.hr_locations_all
WHERE location_id = pda.deliver_to_location_id) deliver_to_location,
(SELECT segment1
|| segment2
|| segment3
|| segment4
|| segment5
 || segment6
|| segment7
FROM apps.gl_code_combinations
WHERE code_combination_id = pda.accrual_account_id) accrual_acnt,
(SELECT segment1
|| segment2
|| segment3
|| segment4
|| segment5
|| segment6
|| segment7
FROM apps.gl_code_combinations
WHERE code_combination_id = pda.code_combination_id) charge_acnt,
phl.ship_via_lookup_code ship_method, expenditure_type,
expenditure_item_date,
(SELECT organization_name
FROM apps.org_organization_definitions
WHERE organization_id = expenditure_organization_id)
exp_organization
FROM apps.po_lines_all pll,
apps.po_distributions_all pda,
apps.po_headers_all phl,
apps.po_vendors pov,
apps.po_vendor_sites_all pvs
WHERE pda.po_line_id = pll.po_line_id
AND phl.org_id =
(SELECT organization_id
FROM apps.hr_all_organization_units hrorg
WHERE hrorg.NAME = 'VLVS_USA_OU') --3835 --add org_id here
AND pll.org_id = phl.org_id
AND pll.po_header_id = phl.po_header_id
AND pda.po_header_id = phl.po_header_id
AND pov.vendor_id = phl.vendor_id
AND pov.vendor_id = pvs.vendor_id
AND pvs.vendor_site_id = phl.vendor_site_id;

Query17: /*Open Quotes */

--QUOTE HEADERS
SELECT 1111 record_id, pha.comments header_description,
hou.NAME organization_name, 'ORIGINAL' action,
'QUOTATION' document_type_code, pha.currency_code,
pha.rate currency_rate, pha.agent_id, pv.vendor_name,
pvs.vendor_site_code, hl1.location_code ship_to_location,
hl2.location_code bill_to_location, apt.NAME payment_terms,
pha.fob_lookup_code fob, pha.freight_terms_lookup_code freight_terms,
pha.ship_via_lookup_code carrier, 'ACTIVE' authorization_status,
pha.vendor_contact_id, pha.segment1 quotation_number,
--Legacy Vendor Number
--Note From Supplier
pha.note_to_vendor, NULL effective_from_date, NULL effective_to_date,
pha.status_lookup_code status
FROM apps.po_headers_all pha,
apps.hr_locations hl1,
apps.hr_locations hl2,
apps.ap_terms apt,
apps.hr_operating_units hou,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs
WHERE pha.type_lookup_code = 'QUOTATION'
 AND pha.org_id = hou.organization_id
AND pha.org_id = pvs.org_id
AND pha.org_id = 1703
AND hl1.location_id = pha.ship_to_location_id
AND hl2.location_id = pha.bill_to_location_id
AND apt.term_id = pha.terms_id
AND pvs.vendor_id = pv.vendor_id
AND pha.vendor_id = pvs.vendor_id
AND pha.vendor_site_id = pvs.vendor_site_id;

--Quote Lines
SELECT 2222 record_id, pla.line_num, plla.shipment_num, plt.line_type,
msi.segment1 item_number, pla.item_revision, NULL category_segments,
pla.item_description, pla.unit_meas_lookup_code unit_of_measure,
plla.quantity, pla.unit_price,
pla.supplier_ref_number supplier_item_number,
ood.organization_code inventory_organization,
hl.location_code ship_to_location, pha.segment1 quote_number,
NULL lead_time, NULL lead_time_uom_days
FROM apps.po_line_locations_all plla,
apps.po_lines_all pla,
apps.po_headers_all pha,
apps.mtl_system_items msi,
apps.org_organization_definitions ood,
apps.hr_locations hl,
apps.po_line_types_tl plt
WHERE plla.po_header_id = pla.po_header_id
AND pla.po_header_id = pha.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.ship_to_organization_id = msi.organization_id
AND pla.item_id = msi.inventory_item_id
AND msi.organization_id = 1704
AND plla.ship_to_location_id = hl.location_id
AND msi.organization_id = ood.organization_id
AND pha.type_lookup_code = 'QUOTATION'
AND pha.org_id = 1703
AND pla.line_type_id = plt.line_type_id
AND plt.source_lang = 'US';

Query18: /*Approved Supplier List (with intransit lead time)*/

-- Approver Supplier List
SELECT msib.segment1 item_number,
pas.vendor_business_type vendor_business_type,
pv.vendor_name vendor_name, mp.organization_code asl_org_id,
pas.category_id category_id, pvs.vendor_site_code vendor_site_code,
pas.attribute2 contract_number, pas.attribute3 expiration_date,
pas.attribute4 freight_forwarder,
pa.release_generation_method rel_gen_method,
ppf.attribute1 scheduler_name,
pa.processing_lead_time processing_lead_time,
pa.min_order_qty min_order_qty,
pa.fixed_lot_multiple fixed_lot_multiple,
pa.enable_plan_schedule_flag plan_schedule_flag,
pa.enable_ship_schedule_flag ship_schedule_flag,
pa.enable_autoschedule_flag autoschedule_flag,
cbp1.bucket_pattern_name plan_bucket_pattern,
cbp2.bucket_pattern_name ship_bucket_pattern,
pa.plan_schedule_type plan_schedule_type,
pa.ship_schedule_type ship_schedule_type,
 pa.purchasing_unit_of_measure purchasing_unit_of_measure,
pa.attribute2 in_transit_ld_time
FROM apps.po_approved_supplier_list pas,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.po_asl_attributes pa,
apps.po_vendors pv,
apps.per_all_people_f ppf,
apps.chv_bucket_patterns cbp1,
apps.chv_bucket_patterns cbp2,
apps.po_vendor_sites pvs
WHERE mp.organization_id = pa.using_organization_id
AND msib.inventory_item_id = pa.item_id
AND msib.organization_id = pa.using_organization_id
AND mp.organization_code = 'POR'
AND pa.asl_id = pas.asl_id
AND pa.vendor_id = pv.vendor_id
AND pa.scheduler_id = ppf.person_id(+)
AND NVL (ppf.effective_end_date, SYSDATE) >= SYSDATE
AND pa.plan_bucket_pattern_id = cbp1.bucket_pattern_id(+)
AND pa.ship_bucket_pattern_id = cbp2.bucket_pattern_id(+)
AND pa.vendor_site_id = pvs.vendor_site_id
ORDER BY item_number;

Query19: /*Sourcing Rule Creation*/

SELECT mp.organization_code organization_code,

mrs.sourcing_rule_name sourcing_rule_name, mrs.description description,
msr.effective_date from_date, msr.disable_date TO_DATE,
DECODE (UPPER (mss.source_type),
1, 'TRANSFER FROM',
2, 'MAKE AT',
3, 'BUY FROM',
NULL
) sourcing_rule_type,
mp1.organization_code shipping_org, pv.vendor_name vendor_name,
pvs.vendor_site_code vendor_site_code,
mss.allocation_percent allocation, mss.RANK RANK,
mss.ship_method shipping_method
FROM apps.mtl_parameters mp,
apps.mrp_sourcing_rules mrs,
apps.mrp_sr_receipt_org msr,
apps.mrp_sr_source_org mss,
apps.mtl_parameters mp1,
apps.po_vendors pv,
apps.po_vendor_sites pvs
WHERE mrs.organization_id = mp.organization_id
AND mp.organization_code = 'POR'
AND mrs.sourcing_rule_id = msr.sourcing_rule_id
AND mrs.organization_id = msr.receipt_organization_id
AND msr.sr_receipt_id = mss.sr_receipt_id
AND mss.source_organization_id = mp1.organization_id(+)
AND mss.vendor_id = pv.vendor_id(+)
AND mss.vendor_site_id = pvs.vendor_site_id(+);

Query20: /*Sourcing Rule Assignment*/

SELECT mas.assignment_set_name assignment_set_name,

 mas.description description, msa.assignment_type assignment_type,
mp.organization_code organization_code,
msa.sourcing_rule_type item_category,
msr.sourcing_rule_name src_rule_type, msib.segment1 src_rule_name
FROM apps.mrp_sr_assignments msa,
apps.mrp_sourcing_rules msr,
apps.mrp_assignment_sets mas,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib
WHERE msa.sourcing_rule_id = msr.sourcing_rule_id
AND msa.assignment_set_id = mas.assignment_set_id
AND msa.organization_id = mp.organization_id
AND mp.organization_code = 'POR'
AND msa.inventory_item_id = msib.inventory_item_id
AND msa.organization_id = msib.organization_id;

Query21: /*Open Work Order*/

SELECT mp.organization_code organization_code, we.wip_entity_name job_name,

msib.segment1 assembly_item_number, wdj.job_type job_type,
wdj.class_code accounting_class,
wdj.scheduled_completion_date completion_date,
wdj.scheduled_start_date start_date, wdj.start_quantity start_quantity,
wdj.net_quantity net_quantity, ml.meaning status,
wsg.schedule_group_name schedule_group,
wdj.build_sequence build_sequence, pa.NAME project,
pt.task_number task_name, flv.description demand_class
FROM apps.wip_entities we,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.wip_discrete_jobs wdj,
apps.mfg_lookups ml,
apps.wip_schedule_groups wsg,
apps.pa_projects pa,
apps.pa_tasks pt,
apps.fnd_lookup_values flv
WHERE we.organization_id = mp.organization_id
AND mp.organization_code = 'POR'
AND we.primary_item_id = msib.inventory_item_id
AND we.organization_id = msib.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND we.organization_id = wdj.organization_id
AND wdj.status_type = ml.lookup_code
AND ml.lookup_type = 'WIP_JOB_STATUS'
AND wdj.organization_id = wsg.organization_id
AND wdj.schedule_group_id = wsg.schedule_group_id
AND wdj.project_id = pa.project_id(+)
AND wdj.organization_id = pa.org_id(+)
AND wdj.project_id = pt.project_id(+)
AND wdj.task_id = pt.task_id(+)
AND flv.lookup_type(+) = 'DEMAND_CLASS'
AND wdj.demand_class = flv.lookup_code(+)
AND flv.LANGUAGE(+) = 'US';

Query22: /*RESOURCE MIGRATION: */

SELECT mp.organization_code, br.resource_code, br.description,

br.resource_type, br.unit_of_measure,
DECODE (br.autocharge_type,
 1, 'WIP move',
2, 'Manual',
3, 'PO receipt',
4, 'PO move',
NULL
) autocharge_type,
DECODE (br.default_basis_type,
1, 'Item',
2, 'Lot',
3, 'Resource Units',
4, 'Resource Value',
5, 'Total Value',
6, 'Activity',
NULL
) default_basis_type,

-- br.expenditure_type,
(SELECT expenditure_type
FROM apps.pa_expenditure_types
WHERE expenditure_type = br.expenditure_type) expenditure_type,
br.attribute1 queue_resource,
DECODE (br.cost_code_type,
1, 'Material',
2, 'Material Overhead',
3, 'Resource',
4, 'Outside Processing',
5, 'Overhead',
NULL
) cost_code_type,
msib.segment1 osp_item,
DECODE (br.allow_costs_flag, 1, 'Y', 2, 'N', NULL) allow_costs_flag,

-- br.default_activity_id,
(SELECT activity
FROM apps.cst_activities ca
WHERE activity_id = br.default_activity_id) activity,
DECODE (br.standard_rate_flag,
1, 'Y',
2, 'N',
NULL
) standard_rate_flag,
gcc1.segment1
|| '-'
|| gcc1.segment2
|| '-'
|| gcc1.segment3
|| '-'
|| gcc1.segment4
|| '-'
|| gcc1.segment5
|| '-'
|| gcc1.segment6
|| '-'
|| gcc1.segment7 absorption_account,
gcc2.segment1
|| '-'
|| gcc2.segment2
|| '-'
|| gcc2.segment3
 || '-'
|| gcc2.segment4
|| '-'
|| gcc2.segment5
|| '-'
|| gcc2.segment6
|| '-'
|| gcc2.segment7 rate_variance_account
FROM apps.bom_resources br,
apps.mtl_parameters mp,
apps.gl_code_combinations gcc1,
apps.gl_code_combinations gcc2,
apps.mtl_system_items_b msib
WHERE br.organization_id = mp.organization_id
AND mp.organization_code = 'SZK'
AND br.rate_variance_account = gcc2.code_combination_id(+)
AND br.absorption_account = gcc1.code_combination_id(+)
AND msib.inventory_item_id(+) = br.purchase_item_id
AND msib.organization_id(+) = br.organization_id;

Query23: /*Assignment of resources to departments migration*/

SELECT mp.organization_code, bd.department_code, br.resource_code,
bdr.available_24_hours_flag, bdr.share_capacity_flag,
bdr.capacity_units units, bdr.ctp_flag, bdr.resource_group_name,
bdr.schedule_to_instance, bdr.exception_set_name, bdr.atp_rule_id,
bdr.utilization, bdr.efficiency, brs.shift_num,
brs.capacity_units shift_units
FROM apps.bom_resources br,
apps.bom_department_resources bdr,
apps.bom_resource_shifts brs,
apps.bom_departments bd,
apps.mtl_parameters mp
WHERE bdr.department_id = brs.department_id
AND bdr.resource_id = brs.resource_id
AND bdr.department_id = bd.department_id
AND bd.organization_id = mp.organization_id
AND br.organization_id = mp.organization_id
AND br.resource_id = bdr.resource_id
AND mp.organization_code = 'SZK';

Query24: /* Stock Locators */

SELECT mp.organization_code, mil.subinventory_code, mil.segment1,
mil.segment2, mil.segment3, mil.segment4
FROM apps.mtl_item_locations mil, apps.mtl_parameters mp
WHERE mil.organization_id = mp.organization_id
AND mp.organization_code = 'SZK';

Query25: /* Collection Plans*/

Query26: /* Bank Accounts*/

Query27: /* Additional Specs (Definition & Assignment) */

-- Add Specs - Definition (Master Migration)

-- ==========================================

SELECT spec_type,
spec_number,
spec_name,
spec_revision,
spec_status status,
replaced_by_spec_number replaced_by,
spec_details SPECIFICATION,
spec_url url
FROM apps.xxinv_vlvs_add_spec_details;

-- Add Specs - Item Assignment

-- ============================

SELECT mp.organization_code,
DECODE ((SELECT COUNT (1)
FROM xxinv_vlvs_item_add_spec
WHERE spec_number = xia.spec_number
AND inv_item_id = xia.inv_item_id
AND organization_id = xia.organization_id
AND xia.organization_id <> mp.master_organization_id),
0, 'N',
'Y'
) common_flag,
msb.segment1 item_number,
xia.spec_number spec_no,
xia.po_print_flag p_o_t,
xia.wo_print_flag w_o_t,
work_order_document_flag w_o_d,
NULL add_update_delete
FROM apps.xxinv_vlvs_item_add_spec xia,
apps.xxinv_vlvs_add_spec_details xad,
apps.mtl_parameters mp,
apps.mtl_system_items_b msb
WHERE xia.organization_id = mp.organization_id
AND xia.spec_type = xad.spec_type
AND xia.spec_number = xad.spec_number
AND mp.organization_code = 'SZK'
AND msb.inventory_item_id = xia.inv_item_id
AND msb.organization_id = xia.organization_id;