[Home] [Help]
1: PACKAGE BODY INV_MWB_QUERY_MANAGER AS
2: /* $Header: INVMWQMB.pls 120.97.12020000.7 2013/01/14 02:52:24 xzhixong ship $ */
3:
4: TYPE DateBindRec IS RECORD(bind_name VARCHAR2(50), bind_value DATE);
5: TYPE VarcharBindRec IS RECORD(bind_name VARCHAR2(50), bind_value VARCHAR2(255));
16: g_date_bind_index PLS_INTEGER;
17: g_varchar_bind_index PLS_INTEGER;
18: g_number_bind_index PLS_INTEGER;
19:
20: g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_QUERY_MANAGER';
21:
22: g_initialize BOOLEAN;
23:
24: g_onhand_where_index PLS_INTEGER;
467:
468: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
469:
470: IF inv_mwb_globals.g_detailed = 1 THEN
471: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
472: 'moqd.subinventory_code';
473: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
474: 'moqd.locator_id';
475: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
469:
470: IF inv_mwb_globals.g_detailed = 1 THEN
471: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
472: 'moqd.subinventory_code';
473: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
474: 'moqd.locator_id';
475: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
476: 'moqd.revision';
477:
471: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
472: 'moqd.subinventory_code';
473: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
474: 'moqd.locator_id';
475: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
476: 'moqd.revision';
477:
478: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
479: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
474: 'moqd.locator_id';
475: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
476: 'moqd.revision';
477:
478: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
479: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
480: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
481: END IF;
482:
475: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
476: 'moqd.revision';
477:
478: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
479: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
480: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
481: END IF;
482:
483: add_qf_where_onhand('ONHAND');
476: 'moqd.revision';
477:
478: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
479: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
480: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
481: END IF;
482:
483: add_qf_where_onhand('ONHAND');
484:
485: WHEN 'ORG' THEN
486:
487: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
488:
489: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
490: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
491: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
492: 'moqd.subinventory_code';
493: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
486:
487: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
488:
489: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
490: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
491: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
492: 'moqd.subinventory_code';
493: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
494:
487: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
488:
489: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
490: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
491: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
492: 'moqd.subinventory_code';
493: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
494:
495: add_qf_where_onhand('ONHAND');
489: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
490: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
491: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
492: 'moqd.subinventory_code';
493: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
494:
495: add_qf_where_onhand('ONHAND');
496:
497: WHEN 'MATLOC' THEN
496:
497: WHEN 'MATLOC' THEN
498:
499: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
500: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
501: 'moqd.subinventory_code';
502: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
503: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
504: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
498:
499: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
500: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
501: 'moqd.subinventory_code';
502: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
503: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
504: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
505: add_qf_where_onhand('ONHAND');
506:
499: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
500: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
501: 'moqd.subinventory_code';
502: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
503: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
504: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
505: add_qf_where_onhand('ONHAND');
506:
507: WHEN 'SUB' THEN
500: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
501: 'moqd.subinventory_code';
502: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
503: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
504: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
505: add_qf_where_onhand('ONHAND');
506:
507: WHEN 'SUB' THEN
508:
507: WHEN 'SUB' THEN
508:
509: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
510:
511: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
512: 'moqd.subinventory_code';
513: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
514: 'moqd.locator_id';
515:
509: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
510:
511: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
512: 'moqd.subinventory_code';
513: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
514: 'moqd.locator_id';
515:
516: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
517: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
512: 'moqd.subinventory_code';
513: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
514: 'moqd.locator_id';
515:
516: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
517: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
518:
519: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
520: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
513: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
514: 'moqd.locator_id';
515:
516: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
517: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
518:
519: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
520: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
521:
515:
516: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
517: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
518:
519: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
520: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
521:
522: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
523: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
516: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
517: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
518:
519: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
520: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
521:
522: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
523: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
524:
518:
519: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
520: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
521:
522: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
523: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
524:
525: add_qf_where_onhand('ONHAND');
526:
519: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
520: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
521:
522: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
523: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
524:
525: add_qf_where_onhand('ONHAND');
526:
527: WHEN 'LOC' THEN
526:
527: WHEN 'LOC' THEN
528: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
529:
530: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
531: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
532: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
533:
534: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
527: WHEN 'LOC' THEN
528: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
529:
530: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
531: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
532: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
533:
534: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
535: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
528: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
529:
530: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
531: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
532: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
533:
534: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
535: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
536: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
530: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
531: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
532: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
533:
534: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
535: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
536: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
537:
538: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
531: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
532: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
533:
534: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
535: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
536: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
537:
538: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
539: 'moqd.subinventory_code';
532: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
533:
534: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
535: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
536: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
537:
538: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
539: 'moqd.subinventory_code';
540: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
534: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
535: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
536: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
537:
538: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
539: 'moqd.subinventory_code';
540: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
541: 'moqd.locator_id';
542: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
536: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
537:
538: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
539: 'moqd.subinventory_code';
540: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
541: 'moqd.locator_id';
542: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
543: 'moqd.lpn_id';
544: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
538: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
539: 'moqd.subinventory_code';
540: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
541: 'moqd.locator_id';
542: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
543: 'moqd.lpn_id';
544: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
545: 'moqd.cost_group_id';
546:
540: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
541: 'moqd.locator_id';
542: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
543: 'moqd.lpn_id';
544: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
545: 'moqd.cost_group_id';
546:
547: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
548: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
543: 'moqd.lpn_id';
544: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
545: 'moqd.cost_group_id';
546:
547: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
548: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
549: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
550: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
551:
544: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
545: 'moqd.cost_group_id';
546:
547: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
548: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
549: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
550: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
551:
552: add_qf_where_onhand('ONHAND');
545: 'moqd.cost_group_id';
546:
547: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
548: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
549: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
550: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
551:
552: add_qf_where_onhand('ONHAND');
553:
546:
547: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
548: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
549: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
550: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
551:
552: add_qf_where_onhand('ONHAND');
553:
554: WHEN 'ITEM' THEN
561: FROM mtl_system_items
562: WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
563: AND organization_id = inv_mwb_globals.g_tree_organization_id;
564:
565: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
566: 'moqd.cost_group_id';
567: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
568: 'moqd.subinventory_code';
569: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
563: AND organization_id = inv_mwb_globals.g_tree_organization_id;
564:
565: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
566: 'moqd.cost_group_id';
567: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
568: 'moqd.subinventory_code';
569: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
570: 'moqd.locator_id';
571: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
565: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
566: 'moqd.cost_group_id';
567: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
568: 'moqd.subinventory_code';
569: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
570: 'moqd.locator_id';
571: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
572: 'moqd.lpn_id';
573:
567: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
568: 'moqd.subinventory_code';
569: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
570: 'moqd.locator_id';
571: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
572: 'moqd.lpn_id';
573:
574: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
575: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
570: 'moqd.locator_id';
571: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
572: 'moqd.lpn_id';
573:
574: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
575: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
576: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
577: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
578:
571: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
572: 'moqd.lpn_id';
573:
574: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
575: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
576: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
577: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
578:
579: -- Onhand Material Status Support
572: 'moqd.lpn_id';
573:
574: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
575: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
576: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
577: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
578:
579: -- Onhand Material Status Support
580: -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
573:
574: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
575: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
576: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
577: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
578:
579: -- Onhand Material Status Support
580: -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
581: if (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) then
592: end if;
593: end if;
594:
595: if (l_default_status_id is not null and l_serial_controlled = 0 and l_lot_controlled = 0) then
596: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
597: 'moqd.status_id';
598: inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
599: end if;
600: -- End Onhand Material Status Support
594:
595: if (l_default_status_id is not null and l_serial_controlled = 0 and l_lot_controlled = 0) then
596: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
597: 'moqd.status_id';
598: inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
599: end if;
600: -- End Onhand Material Status Support
601:
602: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
599: end if;
600: -- End Onhand Material Status Support
601:
602: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
603: inv_mwb_query_manager.add_where_clause(
604: 'moqd.lpn_id = :onh_tree_lpn_id' ,
605: 'ONHAND'
606: );
607: ELSE
604: 'moqd.lpn_id = :onh_tree_lpn_id' ,
605: 'ONHAND'
606: );
607: ELSE
608: inv_mwb_query_manager.add_where_clause(
609: 'moqd.lpn_id IS NULL' ,
610: 'ONHAND'
611: );
612: END IF;
611: );
612: END IF;
613:
614: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
615: inv_mwb_query_manager.add_where_clause(
616: 'moqd.locator_id = :onh_tree_loc_id' ,
617: 'ONHAND'
618: );
619: ELSE
616: 'moqd.locator_id = :onh_tree_loc_id' ,
617: 'ONHAND'
618: );
619: ELSE
620: inv_mwb_query_manager.add_where_clause(
621: 'moqd.locator_id IS NULL' ,
622: 'ONHAND'
623: );
624: END IF;
623: );
624: END IF;
625:
626: IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
627: inv_mwb_query_manager.add_where_clause(
628: 'moqd.subinventory_code = :onh_tree_sub_code' ,
629: 'ONHAND'
630: );
631: END IF;
630: );
631: END IF;
632:
633: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
634: inv_mwb_query_manager.add_bind_variable(
635: 'onh_tree_lpn_id',
636: inv_mwb_globals.g_tree_parent_lpn_id
637: );
638: END IF;
637: );
638: END IF;
639:
640: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
641: inv_mwb_query_manager.add_bind_variable(
642: 'onh_tree_loc_id',
643: inv_mwb_globals.g_tree_loc_id
644: );
645: END IF;
644: );
645: END IF;
646:
647: IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
648: inv_mwb_query_manager.add_bind_variable(
649: 'onh_tree_sub_code',
650: inv_mwb_globals.g_tree_subinventory_code
651: );
652: END IF;
651: );
652: END IF;
653:
654: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
655: inv_mwb_query_manager.add_where_clause(
656: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
657: 'ONHAND'
658: );
659: inv_mwb_query_manager.add_where_clause(
655: inv_mwb_query_manager.add_where_clause(
656: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
657: 'ONHAND'
658: );
659: inv_mwb_query_manager.add_where_clause(
660: 'moqd.organization_id = :onh_tree_organization_id' ,
661: 'ONHAND'
662: );
663:
661: 'ONHAND'
662: );
663:
664: IF l_rev_control = 2 THEN
665: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
666: 'moqd.revision';
667: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
668: END IF;
669:
663:
664: IF l_rev_control = 2 THEN
665: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
666: 'moqd.revision';
667: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
668: END IF;
669:
670: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
671: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
667: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
668: END IF;
669:
670: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
671: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
672: 'moqd.lot_number';
673: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
674: END IF;
675: add_qf_where_onhand('ONHAND');
669:
670: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
671: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
672: 'moqd.lot_number';
673: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
674: END IF;
675: add_qf_where_onhand('ONHAND');
676: inv_mwb_query_manager.add_bind_variable(
677: 'onh_tree_organization_id',
672: 'moqd.lot_number';
673: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
674: END IF;
675: add_qf_where_onhand('ONHAND');
676: inv_mwb_query_manager.add_bind_variable(
677: 'onh_tree_organization_id',
678: inv_mwb_globals.g_tree_organization_id
679: );
680: inv_mwb_query_manager.add_bind_variable(
676: inv_mwb_query_manager.add_bind_variable(
677: 'onh_tree_organization_id',
678: inv_mwb_globals.g_tree_organization_id
679: );
680: inv_mwb_query_manager.add_bind_variable(
681: 'onh_tree_inventory_item_id',
682: inv_mwb_globals.g_tree_item_id
683: );
684:
686:
687: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
688: add_qf_where_onhand('ONHAND');
689:
690: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
691: 'moqd.subinventory_code';
692: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
693: 'moqd.locator_id';
694: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
688: add_qf_where_onhand('ONHAND');
689:
690: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
691: 'moqd.subinventory_code';
692: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
693: 'moqd.locator_id';
694: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
695: 'moqd.lot_number';
696: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
690: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
691: 'moqd.subinventory_code';
692: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
693: 'moqd.locator_id';
694: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
695: 'moqd.lot_number';
696: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
697: 'moqd.cost_group_id';
698: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
692: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
693: 'moqd.locator_id';
694: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
695: 'moqd.lot_number';
696: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
697: 'moqd.cost_group_id';
698: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
699: 'moqd.lpn_id';
700: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
694: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
695: 'moqd.lot_number';
696: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
697: 'moqd.cost_group_id';
698: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
699: 'moqd.lpn_id';
700: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
701: 'moqd.revision';
702:
696: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
697: 'moqd.cost_group_id';
698: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
699: 'moqd.lpn_id';
700: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
701: 'moqd.revision';
702:
703: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
704: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
699: 'moqd.lpn_id';
700: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
701: 'moqd.revision';
702:
703: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
704: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
705: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
706: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
707: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
700: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
701: 'moqd.revision';
702:
703: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
704: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
705: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
706: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
707: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
708: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
701: 'moqd.revision';
702:
703: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
704: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
705: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
706: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
707: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
708: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
709:
702:
703: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
704: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
705: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
706: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
707: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
708: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
709:
710: -- Onhand Material Status Support
703: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
704: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
705: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
706: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
707: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
708: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
709:
710: -- Onhand Material Status Support
711: -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
704: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
705: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
706: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
707: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
708: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
709:
710: -- Onhand Material Status Support
711: -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
712: if (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) then
723: end if;
724: end if;
725:
726: if (l_default_status_id is not null and l_serial_controlled = 0 and l_lot_controlled = 0) then
727: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
728: 'moqd.status_id';
729: inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
730: end if;
731: -- End Onhand Material Status Support
725:
726: if (l_default_status_id is not null and l_serial_controlled = 0 and l_lot_controlled = 0) then
727: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
728: 'moqd.status_id';
729: inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
730: end if;
731: -- End Onhand Material Status Support
732:
733: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
730: end if;
731: -- End Onhand Material Status Support
732:
733: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
734: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
735: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
736: ELSE
737: inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
738: END IF;
731: -- End Onhand Material Status Support
732:
733: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
734: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
735: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
736: ELSE
737: inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
738: END IF;
739:
733: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
734: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
735: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
736: ELSE
737: inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
738: END IF;
739:
740: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
741: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
737: inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
738: END IF;
739:
740: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
741: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
742: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
743: ELSE
744: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
745: END IF;
738: END IF;
739:
740: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
741: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
742: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
743: ELSE
744: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
745: END IF;
746:
740: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
741: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
742: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
743: ELSE
744: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
745: END IF;
746:
747: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
748: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
743: ELSE
744: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
745: END IF;
746:
747: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
748: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
749: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_item_id' ,'ONHAND');
750: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
751:
744: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
745: END IF;
746:
747: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
748: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
749: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_item_id' ,'ONHAND');
750: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
751:
752: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
745: END IF;
746:
747: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
748: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
749: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_item_id' ,'ONHAND');
750: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
751:
752: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
753: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
746:
747: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
748: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
749: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_item_id' ,'ONHAND');
750: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
751:
752: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
753: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
754: inv_mwb_query_manager.add_bind_variable('onh_tree_item_id', inv_mwb_globals.g_tree_item_id);
748: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
749: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_item_id' ,'ONHAND');
750: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
751:
752: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
753: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
754: inv_mwb_query_manager.add_bind_variable('onh_tree_item_id', inv_mwb_globals.g_tree_item_id);
755: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
756:
749: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_item_id' ,'ONHAND');
750: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
751:
752: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
753: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
754: inv_mwb_query_manager.add_bind_variable('onh_tree_item_id', inv_mwb_globals.g_tree_item_id);
755: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
756:
757:
750: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
751:
752: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
753: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
754: inv_mwb_query_manager.add_bind_variable('onh_tree_item_id', inv_mwb_globals.g_tree_item_id);
755: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
756:
757:
758: WHEN 'LPN' THEN
751:
752: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
753: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
754: inv_mwb_query_manager.add_bind_variable('onh_tree_item_id', inv_mwb_globals.g_tree_item_id);
755: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
756:
757:
758: WHEN 'LPN' THEN
759:
757:
758: WHEN 'LPN' THEN
759:
760: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
761: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
762: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
763: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
764: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
765:
758: WHEN 'LPN' THEN
759:
760: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
761: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
762: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
763: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
764: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
765:
766: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
759:
760: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
761: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
762: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
763: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
764: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
765:
766: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
767: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
760: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
761: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
762: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
763: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
764: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
765:
766: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
767: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
768: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
762: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
763: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
764: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
765:
766: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
767: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
768: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
769: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
770: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
763: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
764: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
765:
766: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
767: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
768: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
769: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
770: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
771: 'moqd.subinventory_code';
764: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
765:
766: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
767: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
768: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
769: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
770: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
771: 'moqd.subinventory_code';
772: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
765:
766: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
767: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
768: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
769: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
770: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
771: 'moqd.subinventory_code';
772: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
773: 'moqd.locator_id';
766: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
767: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
768: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
769: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
770: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
771: 'moqd.subinventory_code';
772: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
773: 'moqd.locator_id';
774: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
768: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
769: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
770: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
771: 'moqd.subinventory_code';
772: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
773: 'moqd.locator_id';
774: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
775: 'moqd.lpn_id';
776: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
770: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
771: 'moqd.subinventory_code';
772: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
773: 'moqd.locator_id';
774: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
775: 'moqd.lpn_id';
776: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
777: 'moqd.cost_group_id';
778:
772: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
773: 'moqd.locator_id';
774: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
775: 'moqd.lpn_id';
776: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
777: 'moqd.cost_group_id';
778:
779: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
780: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
775: 'moqd.lpn_id';
776: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
777: 'moqd.cost_group_id';
778:
779: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
780: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
781: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
782: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
783:
776: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
777: 'moqd.cost_group_id';
778:
779: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
780: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
781: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
782: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
783:
784: add_qf_where_onhand('ONHAND');
777: 'moqd.cost_group_id';
778:
779: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
780: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
781: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
782: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
783:
784: add_qf_where_onhand('ONHAND');
785:
778:
779: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
780: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
781: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
782: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
783:
784: add_qf_where_onhand('ONHAND');
785:
786: WHEN 'LOT' THEN
785:
786: WHEN 'LOT' THEN
787:
788: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
789: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
790: 'moqd.revision';
791: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
792: 'moqd.subinventory_code';
793: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
787:
788: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
789: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
790: 'moqd.revision';
791: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
792: 'moqd.subinventory_code';
793: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
794: 'moqd.locator_id';
795: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
789: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
790: 'moqd.revision';
791: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
792: 'moqd.subinventory_code';
793: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
794: 'moqd.locator_id';
795: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
796: 'moqd.lpn_id';
797: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
791: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
792: 'moqd.subinventory_code';
793: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
794: 'moqd.locator_id';
795: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
796: 'moqd.lpn_id';
797: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
798: 'moqd.cost_group_id';
799: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
793: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
794: 'moqd.locator_id';
795: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
796: 'moqd.lpn_id';
797: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
798: 'moqd.cost_group_id';
799: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
800: 'moqd.lot_number';
801:
795: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
796: 'moqd.lpn_id';
797: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
798: 'moqd.cost_group_id';
799: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
800: 'moqd.lot_number';
801:
802: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
803: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
798: 'moqd.cost_group_id';
799: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
800: 'moqd.lot_number';
801:
802: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
803: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
804: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
805: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
806: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
799: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
800: 'moqd.lot_number';
801:
802: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
803: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
804: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
805: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
806: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
807: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
800: 'moqd.lot_number';
801:
802: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
803: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
804: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
805: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
806: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
807: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
808:
801:
802: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
803: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
804: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
805: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
806: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
807: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
808:
809: -- Onhand Material Status Support
802: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
803: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
804: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
805: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
806: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
807: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
808:
809: -- Onhand Material Status Support
810: -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
803: inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
804: inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
805: inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
806: inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
807: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
808:
809: -- Onhand Material Status Support
810: -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
811: if (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) then
818: end if;
819: end if;
820:
821: if (l_default_status_id is not null and l_serial_controlled = 0) then
822: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
823: 'moqd.status_id';
824: inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
825: end if;
826: -- End Onhand Material Status Support
820:
821: if (l_default_status_id is not null and l_serial_controlled = 0) then
822: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
823: 'moqd.status_id';
824: inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
825: end if;
826: -- End Onhand Material Status Support
827:
828: IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
825: end if;
826: -- End Onhand Material Status Support
827:
828: IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
829: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
830: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
831: END IF;
832:
833: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
826: -- End Onhand Material Status Support
827:
828: IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
829: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
830: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
831: END IF;
832:
833: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
834: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
830: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
831: END IF;
832:
833: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
834: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
835: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
836: ELSE
837: inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
838: END IF;
831: END IF;
832:
833: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
834: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
835: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
836: ELSE
837: inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
838: END IF;
839:
833: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
834: inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
835: inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
836: ELSE
837: inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
838: END IF;
839:
840: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
841: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
837: inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
838: END IF;
839:
840: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
841: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
842: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
843: ELSE
844: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
845: END IF;
838: END IF;
839:
840: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
841: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
842: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
843: ELSE
844: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
845: END IF;
846:
840: IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
841: inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
842: inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
843: ELSE
844: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
845: END IF;
846:
847: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
848: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
843: ELSE
844: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
845: END IF;
846:
847: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
848: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
849: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
850: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_num' ,'ONHAND');
851:
844: inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
845: END IF;
846:
847: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
848: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
849: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
850: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_num' ,'ONHAND');
851:
852: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
845: END IF;
846:
847: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
848: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
849: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
850: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_num' ,'ONHAND');
851:
852: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
853: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
846:
847: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
848: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
849: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
850: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_num' ,'ONHAND');
851:
852: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
853: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
854: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
848: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
849: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
850: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_num' ,'ONHAND');
851:
852: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
853: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
854: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
855: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_num', inv_mwb_globals.g_tree_node_value);
856:
849: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
850: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_num' ,'ONHAND');
851:
852: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
853: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
854: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
855: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_num', inv_mwb_globals.g_tree_node_value);
856:
857: add_qf_where_onhand('ONHAND');
850: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_num' ,'ONHAND');
851:
852: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
853: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
854: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
855: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_num', inv_mwb_globals.g_tree_node_value);
856:
857: add_qf_where_onhand('ONHAND');
858:
851:
852: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
853: inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
854: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
855: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_num', inv_mwb_globals.g_tree_node_value);
856:
857: add_qf_where_onhand('ONHAND');
858:
859: WHEN 'SERIAL' THEN
858:
859: WHEN 'SERIAL' THEN
860: -- Dummy code
861: INV_MWB_LOCATION_TREE.make_common_query_onhand('MOQD');
862: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND'); --Bug 7611434 click tree serial node will not show all items/qty
863: add_qf_where_onhand('ONHAND');
864: END CASE;
865:
866: l_query_str := l_query_str || build_query(
896: WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
897: AND organization_id = inv_mwb_globals.g_tree_organization_id;
898:
899: IF inv_mwb_globals.g_chk_onhand = 1 THEN
900: inv_mwb_query_manager.add_where_clause(
901: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
902: 'ONHAND'
903: );
904: inv_mwb_query_manager.add_where_clause(
900: inv_mwb_query_manager.add_where_clause(
901: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
902: 'ONHAND'
903: );
904: inv_mwb_query_manager.add_where_clause(
905: 'moqd.organization_id = :onh_tree_organization_id' ,
906: 'ONHAND'
907: );
908: IF l_rev_control = 2 THEN
905: 'moqd.organization_id = :onh_tree_organization_id' ,
906: 'ONHAND'
907: );
908: IF l_rev_control = 2 THEN
909: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
910: 'moqd.revision';
911: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
912: END IF;
913: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
907: );
908: IF l_rev_control = 2 THEN
909: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
910: 'moqd.revision';
911: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
912: END IF;
913: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
914: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
915: 'moqd.lot_number';
910: 'moqd.revision';
911: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
912: END IF;
913: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
914: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
915: 'moqd.lot_number';
916: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
917: END IF;
918:
912: END IF;
913: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
914: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
915: 'moqd.lot_number';
916: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
917: END IF;
918:
919: inv_mwb_query_manager.add_bind_variable(
920: 'onh_tree_organization_id',
915: 'moqd.lot_number';
916: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
917: END IF;
918:
919: inv_mwb_query_manager.add_bind_variable(
920: 'onh_tree_organization_id',
921: inv_mwb_globals.g_tree_organization_id
922: );
923: inv_mwb_query_manager.add_bind_variable(
919: inv_mwb_query_manager.add_bind_variable(
920: 'onh_tree_organization_id',
921: inv_mwb_globals.g_tree_organization_id
922: );
923: inv_mwb_query_manager.add_bind_variable(
924: 'onh_tree_inventory_item_id',
925: inv_mwb_globals.g_tree_item_id
926: );
927: END IF; -- onhand check
930:
931: WHEN 'ITEM' THEN
932:
933: INV_MWB_ITEM_TREE.make_common_queries('MOQD');
934: inv_mwb_query_manager.add_where_clause(
935: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
936: 'ONHAND'
937: );
938: inv_mwb_query_manager.add_bind_variable(
934: inv_mwb_query_manager.add_where_clause(
935: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
936: 'ONHAND'
937: );
938: inv_mwb_query_manager.add_bind_variable(
939: 'onh_tree_inventory_item_id',
940: inv_mwb_globals.g_tree_item_id
941: );
942: add_qf_where_onhand('ONHAND');
952: AND organization_id = inv_mwb_globals.g_tree_organization_id;
953:
954: INV_MWB_ITEM_TREE.make_common_queries('MOQD');
955:
956: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
957: 'moqd.revision';
958: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
959: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
960: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
954: INV_MWB_ITEM_TREE.make_common_queries('MOQD');
955:
956: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
957: 'moqd.revision';
958: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
959: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
960: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
961: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
962: IF l_lot_control = 2 THEN
955:
956: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
957: 'moqd.revision';
958: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
959: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
960: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
961: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
962: IF l_lot_control = 2 THEN
963: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
956: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
957: 'moqd.revision';
958: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
959: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
960: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
961: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
962: IF l_lot_control = 2 THEN
963: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
964: 'moqd.lot_number';
957: 'moqd.revision';
958: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
959: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
960: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
961: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
962: IF l_lot_control = 2 THEN
963: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
964: 'moqd.lot_number';
965: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
959: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
960: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
961: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
962: IF l_lot_control = 2 THEN
963: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
964: 'moqd.lot_number';
965: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
966: END IF;
967:
961: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
962: IF l_lot_control = 2 THEN
963: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
964: 'moqd.lot_number';
965: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
966: END IF;
967:
968: inv_mwb_query_manager.add_bind_variable(
969: 'onh_tree_organization_id',
964: 'moqd.lot_number';
965: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
966: END IF;
967:
968: inv_mwb_query_manager.add_bind_variable(
969: 'onh_tree_organization_id',
970: inv_mwb_globals.g_tree_organization_id
971: );
972: inv_mwb_query_manager.add_bind_variable(
968: inv_mwb_query_manager.add_bind_variable(
969: 'onh_tree_organization_id',
970: inv_mwb_globals.g_tree_organization_id
971: );
972: inv_mwb_query_manager.add_bind_variable(
973: 'onh_tree_inventory_item_id',
974: inv_mwb_globals.g_tree_item_id
975: );
976: inv_mwb_query_manager.add_bind_variable(
972: inv_mwb_query_manager.add_bind_variable(
973: 'onh_tree_inventory_item_id',
974: inv_mwb_globals.g_tree_item_id
975: );
976: inv_mwb_query_manager.add_bind_variable(
977: 'onh_tree_revision',
978: inv_mwb_globals.g_tree_rev
979: );
980: add_qf_where_onhand('ONHAND');
992:
993: INV_MWB_ITEM_TREE.make_common_queries('MOQD');
994:
995: IF l_rev_control = 2 THEN
996: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
997: 'moqd.revision';
998: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
999: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
1000: END IF;
994:
995: IF l_rev_control = 2 THEN
996: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
997: 'moqd.revision';
998: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
999: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
1000: END IF;
1001: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1002: 'moqd.lot_number';
995: IF l_rev_control = 2 THEN
996: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
997: 'moqd.revision';
998: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
999: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
1000: END IF;
1001: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1002: 'moqd.lot_number';
1003: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
997: 'moqd.revision';
998: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
999: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
1000: END IF;
1001: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1002: 'moqd.lot_number';
1003: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
1004:
1005: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
999: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
1000: END IF;
1001: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1002: 'moqd.lot_number';
1003: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
1004:
1005: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1006: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1007: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
1001: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1002: 'moqd.lot_number';
1003: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
1004:
1005: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1006: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1007: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
1008:
1009: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1002: 'moqd.lot_number';
1003: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
1004:
1005: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1006: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1007: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
1008:
1009: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1010: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1003: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
1004:
1005: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1006: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1007: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
1008:
1009: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1010: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1011: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
1005: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1006: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1007: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
1008:
1009: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1010: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1011: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
1012: IF l_rev_control = 2 THEN
1013: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
1006: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1007: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
1008:
1009: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1010: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1011: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
1012: IF l_rev_control = 2 THEN
1013: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
1014: END IF;
1007: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
1008:
1009: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1010: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1011: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
1012: IF l_rev_control = 2 THEN
1013: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
1014: END IF;
1015:
1009: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1010: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1011: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
1012: IF l_rev_control = 2 THEN
1013: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
1014: END IF;
1015:
1016: add_qf_where_onhand('ONHAND');
1017: WHEN 'SERIAL' THEN
1045: WHEN 'COST_GROUP' THEN
1046:
1047: INV_MWB_COST_GROUP_TREE.make_common_queries('MOQD');
1048:
1049: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1050: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1051:
1052: add_qf_where_onhand('ONHAND');
1053:
1046:
1047: INV_MWB_COST_GROUP_TREE.make_common_queries('MOQD');
1048:
1049: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1050: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1051:
1052: add_qf_where_onhand('ONHAND');
1053:
1054: WHEN 'ORG' THEN
1054: WHEN 'ORG' THEN
1055:
1056: INV_MWB_COST_GROUP_TREE.make_common_queries('MOQD');
1057:
1058: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1059: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1060: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1061: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1062:
1055:
1056: INV_MWB_COST_GROUP_TREE.make_common_queries('MOQD');
1057:
1058: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1059: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1060: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1061: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1062:
1063: add_qf_where_onhand('ONHAND');
1056: INV_MWB_COST_GROUP_TREE.make_common_queries('MOQD');
1057:
1058: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1059: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1060: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1061: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1062:
1063: add_qf_where_onhand('ONHAND');
1064:
1057:
1058: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1059: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1060: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1061: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1062:
1063: add_qf_where_onhand('ONHAND');
1064:
1065: WHEN 'ITEM' THEN
1065: WHEN 'ITEM' THEN
1066:
1067: INV_MWB_COST_GROUP_TREE.make_common_queries('MOQD');
1068:
1069: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1070: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1071: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1072: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1073: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1066:
1067: INV_MWB_COST_GROUP_TREE.make_common_queries('MOQD');
1068:
1069: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1070: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1071: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1072: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1073: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1074: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1067: INV_MWB_COST_GROUP_TREE.make_common_queries('MOQD');
1068:
1069: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1070: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1071: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1072: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1073: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1074: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1075:
1068:
1069: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1070: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1071: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1072: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1073: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1074: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1075:
1076: add_qf_where_onhand('ONHAND');
1069: inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
1070: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1071: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1072: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1073: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1074: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1075:
1076: add_qf_where_onhand('ONHAND');
1077:
1070: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1071: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
1072: inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
1073: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1074: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1075:
1076: add_qf_where_onhand('ONHAND');
1077:
1078: WHEN 'SERIAL' THEN
1106: WHEN 'ORG' THEN
1107:
1108: INV_MWB_LOT_TREE.make_common_queries('MOQD');
1109:
1110: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1111: 'moqd.lot_number';
1112: inv_mwb_query_manager.add_where_clause(
1113: 'moqd.organization_id = :onh_tree_organization_id' ,
1114: 'ONHAND'
1108: INV_MWB_LOT_TREE.make_common_queries('MOQD');
1109:
1110: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1111: 'moqd.lot_number';
1112: inv_mwb_query_manager.add_where_clause(
1113: 'moqd.organization_id = :onh_tree_organization_id' ,
1114: 'ONHAND'
1115: );
1116: inv_mwb_query_manager.add_group_clause(
1112: inv_mwb_query_manager.add_where_clause(
1113: 'moqd.organization_id = :onh_tree_organization_id' ,
1114: 'ONHAND'
1115: );
1116: inv_mwb_query_manager.add_group_clause(
1117: 'moqd.lot_number' ,
1118: 'ONHAND'
1119: );
1120: inv_mwb_query_manager.add_bind_variable(
1116: inv_mwb_query_manager.add_group_clause(
1117: 'moqd.lot_number' ,
1118: 'ONHAND'
1119: );
1120: inv_mwb_query_manager.add_bind_variable(
1121: 'onh_tree_organization_id',
1122: inv_mwb_globals.g_tree_organization_id
1123: );
1124:
1135: AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
1136:
1137: INV_MWB_LOT_TREE.make_common_queries('MOQD');
1138:
1139: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1140: 'moqd.lot_number';
1141: IF l_rev_control = 2 THEN
1142: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
1143: 'moqd.revision';
1138:
1139: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1140: 'moqd.lot_number';
1141: IF l_rev_control = 2 THEN
1142: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
1143: 'moqd.revision';
1144: inv_mwb_query_manager.add_group_clause(
1145: 'moqd.revision' ,
1146: 'ONHAND'
1140: 'moqd.lot_number';
1141: IF l_rev_control = 2 THEN
1142: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
1143: 'moqd.revision';
1144: inv_mwb_query_manager.add_group_clause(
1145: 'moqd.revision' ,
1146: 'ONHAND'
1147: );
1148: END IF;
1145: 'moqd.revision' ,
1146: 'ONHAND'
1147: );
1148: END IF;
1149: inv_mwb_query_manager.add_where_clause(
1150: 'moqd.organization_id = :onh_tree_organization_id' ,
1151: 'ONHAND'
1152: );
1153: inv_mwb_query_manager.add_where_clause(
1149: inv_mwb_query_manager.add_where_clause(
1150: 'moqd.organization_id = :onh_tree_organization_id' ,
1151: 'ONHAND'
1152: );
1153: inv_mwb_query_manager.add_where_clause(
1154: 'moqd.lot_number = :onh_tree_lot_number' ,
1155: 'ONHAND'
1156: );
1157: inv_mwb_query_manager.add_where_clause(
1153: inv_mwb_query_manager.add_where_clause(
1154: 'moqd.lot_number = :onh_tree_lot_number' ,
1155: 'ONHAND'
1156: );
1157: inv_mwb_query_manager.add_where_clause(
1158: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
1159: 'ONHAND'
1160: );
1161: inv_mwb_query_manager.add_group_clause(
1157: inv_mwb_query_manager.add_where_clause(
1158: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
1159: 'ONHAND'
1160: );
1161: inv_mwb_query_manager.add_group_clause(
1162: 'moqd.lot_number' ,
1163: 'ONHAND'
1164: );
1165: inv_mwb_query_manager.add_bind_variable(
1161: inv_mwb_query_manager.add_group_clause(
1162: 'moqd.lot_number' ,
1163: 'ONHAND'
1164: );
1165: inv_mwb_query_manager.add_bind_variable(
1166: 'onh_tree_organization_id',
1167: inv_mwb_globals.g_tree_organization_id
1168: );
1169: inv_mwb_query_manager.add_bind_variable(
1165: inv_mwb_query_manager.add_bind_variable(
1166: 'onh_tree_organization_id',
1167: inv_mwb_globals.g_tree_organization_id
1168: );
1169: inv_mwb_query_manager.add_bind_variable(
1170: 'onh_tree_lot_number',
1171: inv_mwb_globals.g_tree_lot_number
1172: );
1173: inv_mwb_query_manager.add_bind_variable(
1169: inv_mwb_query_manager.add_bind_variable(
1170: 'onh_tree_lot_number',
1171: inv_mwb_globals.g_tree_lot_number
1172: );
1173: inv_mwb_query_manager.add_bind_variable(
1174: 'onh_tree_inventory_item_id',
1175: inv_mwb_globals.g_tree_item_id
1176: );
1177:
1184: WHEN 'LOT' THEN
1185:
1186: INV_MWB_LOT_TREE.make_common_queries('MOQD');
1187:
1188: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1189: 'moqd.lot_number';
1190: inv_mwb_query_manager.add_where_clause(
1191: 'moqd.organization_id = :onh_tree_organization_id' ,
1192: 'ONHAND'
1186: INV_MWB_LOT_TREE.make_common_queries('MOQD');
1187:
1188: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1189: 'moqd.lot_number';
1190: inv_mwb_query_manager.add_where_clause(
1191: 'moqd.organization_id = :onh_tree_organization_id' ,
1192: 'ONHAND'
1193: );
1194: inv_mwb_query_manager.add_where_clause(
1190: inv_mwb_query_manager.add_where_clause(
1191: 'moqd.organization_id = :onh_tree_organization_id' ,
1192: 'ONHAND'
1193: );
1194: inv_mwb_query_manager.add_where_clause(
1195: 'moqd.lot_number = :onh_tree_lot_number' ,
1196: 'ONHAND'
1197: );
1198: inv_mwb_query_manager.add_group_clause(
1194: inv_mwb_query_manager.add_where_clause(
1195: 'moqd.lot_number = :onh_tree_lot_number' ,
1196: 'ONHAND'
1197: );
1198: inv_mwb_query_manager.add_group_clause(
1199: 'moqd.lot_number' ,
1200: 'ONHAND'
1201: );
1202: inv_mwb_query_manager.add_bind_variable(
1198: inv_mwb_query_manager.add_group_clause(
1199: 'moqd.lot_number' ,
1200: 'ONHAND'
1201: );
1202: inv_mwb_query_manager.add_bind_variable(
1203: 'onh_tree_organization_id',
1204: inv_mwb_globals.g_tree_organization_id
1205: );
1206: inv_mwb_query_manager.add_bind_variable(
1202: inv_mwb_query_manager.add_bind_variable(
1203: 'onh_tree_organization_id',
1204: inv_mwb_globals.g_tree_organization_id
1205: );
1206: inv_mwb_query_manager.add_bind_variable(
1207: 'onh_tree_lot_number',
1208: inv_mwb_globals.g_tree_lot_number
1209: );
1210:
2560:
2561: --Bug 14548684 add view mtl_system_items_vl to support multi-language
2562: IF inv_mwb_globals.g_inventory_item_id IS NULL
2563: AND inv_mwb_globals.g_item_description IS NOT NULL THEN
2564: inv_mwb_query_manager.add_from_clause('mtl_system_items_kfv msik', 'ONHAND');
2565: inv_mwb_query_manager.add_from_clause('mtl_system_items_vl msiv', 'ONHAND');
2566: add_where_clause('moqd.inventory_item_id = msik.inventory_item_id', 'ONHAND');
2567: add_where_clause('moqd.organization_id = msik.organization_id', 'ONHAND');
2568: add_where_clause('msik.inventory_item_id = msiv.inventory_item_id', 'ONHAND');
2561: --Bug 14548684 add view mtl_system_items_vl to support multi-language
2562: IF inv_mwb_globals.g_inventory_item_id IS NULL
2563: AND inv_mwb_globals.g_item_description IS NOT NULL THEN
2564: inv_mwb_query_manager.add_from_clause('mtl_system_items_kfv msik', 'ONHAND');
2565: inv_mwb_query_manager.add_from_clause('mtl_system_items_vl msiv', 'ONHAND');
2566: add_where_clause('moqd.inventory_item_id = msik.inventory_item_id', 'ONHAND');
2567: add_where_clause('moqd.organization_id = msik.organization_id', 'ONHAND');
2568: add_where_clause('msik.inventory_item_id = msiv.inventory_item_id', 'ONHAND');
2569: add_where_clause('msik.organization_id = msiv.organization_id', 'ONHAND');
2605: END IF;
2606:
2607: IF inv_mwb_globals.g_lpn_from_id IS NOT NULL
2608: OR inv_mwb_globals.g_lpn_to_id IS NOT NULL THEN
2609: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ','ONHAND');
2610: add_where_clause('wlpn.lpn_context IN (1,9,11)', 'ONHAND');
2611: add_where_clause('moqd.lpn_id = wlpn.lpn_id', 'ONHAND');
2612: END IF;
2613:
2674:
2675: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
2676: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
2677: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
2678: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', 'ONHAND');
2679: add_where_clause('moqd.lot_number = mln3.lot_number', 'ONHAND');
2680: add_where_clause('moqd.inventory_item_id = mln3.inventory_item_id', 'ONHAND'); -- Bug 9927569
2681: add_where_clause('moqd.organization_id = mln3.organization_id', 'ONHAND'); -- Bug 9927569
2682:
2785: if inv_mwb_globals.g_status_id is not null then
2786: if l_default_status_id is not null then
2787: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2788:
2789: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2790: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2791: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2792: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2793:
2786: if l_default_status_id is not null then
2787: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2788:
2789: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2790: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2791: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2792: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2793:
2794: add_where_clause('moqd.status_id = :onh_status_id', 'ONHAND');
2787: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2788:
2789: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2790: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2791: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2792: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2793:
2794: add_where_clause('moqd.status_id = :onh_status_id', 'ONHAND');
2795: add_bind_variable('onh_status_id', inv_mwb_globals.g_status_id);
2788:
2789: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2790: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2791: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2792: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2793:
2794: add_where_clause('moqd.status_id = :onh_status_id', 'ONHAND');
2795: add_bind_variable('onh_status_id', inv_mwb_globals.g_status_id);
2796: end if;
2797: else
2798: -- Bug 6060233
2799: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2800:
2801: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2802: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2803: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2804: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2798: -- Bug 6060233
2799: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2800:
2801: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2802: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2803: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2804: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2799: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2800:
2801: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2802: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2803: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2804: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2800:
2801: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2802: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2803: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2804: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2801: inv_mwb_query_manager.add_from_clause('mtl_system_items msit', 'ONHAND');
2802: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2803: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2804: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2802: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2803: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2804: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2803: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2804: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2804: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2812: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msi.organization_id', 'ONHAND');
2805: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msit.organization_id', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2812: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msi.organization_id', 'ONHAND');
2813: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2806: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = msit.inventory_item_id', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2812: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msi.organization_id', 'ONHAND');
2813: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2814: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2807: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mil.organization_id(+)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2812: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msi.organization_id', 'ONHAND');
2813: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2814: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2815: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2808: inv_mwb_query_manager.add_where_clause('moqd.locator_id = mil.inventory_location_id(+)', 'ONHAND');
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2812: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msi.organization_id', 'ONHAND');
2813: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2814: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2815: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2816:
2809: inv_mwb_query_manager.add_where_clause('moqd.organization_id = mln.organization_id(+)', 'ONHAND');
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2812: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msi.organization_id', 'ONHAND');
2813: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2814: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2815: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2816:
2817: inv_mwb_query_manager.add_where_clause('(msi.status_id = :msi_status_id' ||
2810: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2812: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msi.organization_id', 'ONHAND');
2813: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2814: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2815: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2816:
2817: inv_mwb_query_manager.add_where_clause('(msi.status_id = :msi_status_id' ||
2818: ' OR mil.status_id = :mil_status_id' ||
2811: inv_mwb_query_manager.add_where_clause('moqd.lot_number = mln.lot_number(+)', 'ONHAND');
2812: inv_mwb_query_manager.add_where_clause('moqd.organization_id = msi.organization_id', 'ONHAND');
2813: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2814: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2815: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2816:
2817: inv_mwb_query_manager.add_where_clause('(msi.status_id = :msi_status_id' ||
2818: ' OR mil.status_id = :mil_status_id' ||
2819: ' OR mln.status_id = :mln_status_id)', 'ONHAND');
2813: inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2814: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2815: inv_mwb_query_manager.add_where_clause('msit.serial_number_control_code IN (1, 6)', 'ONHAND');
2816:
2817: inv_mwb_query_manager.add_where_clause('(msi.status_id = :msi_status_id' ||
2818: ' OR mil.status_id = :mil_status_id' ||
2819: ' OR mln.status_id = :mln_status_id)', 'ONHAND');
2820:
2821: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2817: inv_mwb_query_manager.add_where_clause('(msi.status_id = :msi_status_id' ||
2818: ' OR mil.status_id = :mil_status_id' ||
2819: ' OR mln.status_id = :mln_status_id)', 'ONHAND');
2820:
2821: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2822: inv_mwb_query_manager.add_bind_variable('mil_status_id', inv_mwb_globals.g_status_id);
2823: inv_mwb_query_manager.add_bind_variable('mln_status_id', inv_mwb_globals.g_status_id);
2824: END IF;
2825: -- End Bug 6060233
2818: ' OR mil.status_id = :mil_status_id' ||
2819: ' OR mln.status_id = :mln_status_id)', 'ONHAND');
2820:
2821: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2822: inv_mwb_query_manager.add_bind_variable('mil_status_id', inv_mwb_globals.g_status_id);
2823: inv_mwb_query_manager.add_bind_variable('mln_status_id', inv_mwb_globals.g_status_id);
2824: END IF;
2825: -- End Bug 6060233
2826: end if;
2819: ' OR mln.status_id = :mln_status_id)', 'ONHAND');
2820:
2821: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2822: inv_mwb_query_manager.add_bind_variable('mil_status_id', inv_mwb_globals.g_status_id);
2823: inv_mwb_query_manager.add_bind_variable('mln_status_id', inv_mwb_globals.g_status_id);
2824: END IF;
2825: -- End Bug 6060233
2826: end if;
2827: end if;
2831: WHEN 'ONHAND_MSN' THEN
2832:
2833: IF inv_mwb_globals.g_status_id IS NOT NULL THEN
2834: if l_default_status_id is null then -- Onhand Material Status Support
2835: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2836: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2837: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2838: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mil.organization_id(+)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2832:
2833: IF inv_mwb_globals.g_status_id IS NOT NULL THEN
2834: if l_default_status_id is null then -- Onhand Material Status Support
2835: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2836: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2837: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2838: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mil.organization_id(+)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2833: IF inv_mwb_globals.g_status_id IS NOT NULL THEN
2834: if l_default_status_id is null then -- Onhand Material Status Support
2835: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2836: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2837: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2838: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mil.organization_id(+)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2834: if l_default_status_id is null then -- Onhand Material Status Support
2835: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2836: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2837: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2838: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mil.organization_id(+)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2835: inv_mwb_query_manager.add_from_clause('mtl_item_locations_kfv mil', 'ONHAND');
2836: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2837: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2838: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mil.organization_id(+)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2843: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = msi.organization_id', 'ONHAND');
2836: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
2837: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2838: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mil.organization_id(+)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2843: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = msi.organization_id', 'ONHAND');
2844: inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2837: inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
2838: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mil.organization_id(+)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2843: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = msi.organization_id', 'ONHAND');
2844: inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2845: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2838: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mil.organization_id(+)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2843: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = msi.organization_id', 'ONHAND');
2844: inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2845: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2846: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2839: inv_mwb_query_manager.add_where_clause('msn.current_locator_id = mil.inventory_location_id(+)', 'ONHAND');
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2843: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = msi.organization_id', 'ONHAND');
2844: inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2845: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2846: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2847:
2840: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = mln.organization_id(+)', 'ONHAND');
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2843: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = msi.organization_id', 'ONHAND');
2844: inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2845: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2846: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2847:
2848: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2841: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = mln.inventory_item_id(+)', 'ONHAND');
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2843: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = msi.organization_id', 'ONHAND');
2844: inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2845: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2846: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2847:
2848: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2849:
2842: inv_mwb_query_manager.add_where_clause('msn.lot_number = mln.lot_number(+)', 'ONHAND');
2843: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = msi.organization_id', 'ONHAND');
2844: inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = msi.secondary_inventory_name', 'ONHAND');
2845: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2846: inv_mwb_query_manager.add_where_clause('(msi.subinventory_type = 1 OR msi.subinventory_type IS NULL)', 'ONHAND');
2847:
2848: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2849:
2850: -- Bug 6060233
2847:
2848: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2849:
2850: -- Bug 6060233
2851: inv_mwb_query_manager.add_where_clause('(msi.status_id = :msi_status_id' ||
2852: ' OR mil.status_id = :mil_status_id' ||
2853: ' OR mln.status_id = :mln_status_id' ||
2854: ' OR msn.status_id = :msn_status_id)', 'ONHAND');
2855:
2853: ' OR mln.status_id = :mln_status_id' ||
2854: ' OR msn.status_id = :msn_status_id)', 'ONHAND');
2855:
2856: /*
2857: inv_mwb_query_manager.add_where_clause('msi.status_id = :msi_status_id', 'ONHAND');
2858: inv_mwb_query_manager.add_where_clause('mil.status_id = :mil_status_id', 'ONHAND');
2859: inv_mwb_query_manager.add_where_clause('mln.status_id = :mln_status_id', 'ONHAND');
2860: inv_mwb_query_manager.add_where_clause('msn.status_id = :msn_status_id', 'ONHAND');
2861: */
2854: ' OR msn.status_id = :msn_status_id)', 'ONHAND');
2855:
2856: /*
2857: inv_mwb_query_manager.add_where_clause('msi.status_id = :msi_status_id', 'ONHAND');
2858: inv_mwb_query_manager.add_where_clause('mil.status_id = :mil_status_id', 'ONHAND');
2859: inv_mwb_query_manager.add_where_clause('mln.status_id = :mln_status_id', 'ONHAND');
2860: inv_mwb_query_manager.add_where_clause('msn.status_id = :msn_status_id', 'ONHAND');
2861: */
2862: -- End Bug 6060233
2855:
2856: /*
2857: inv_mwb_query_manager.add_where_clause('msi.status_id = :msi_status_id', 'ONHAND');
2858: inv_mwb_query_manager.add_where_clause('mil.status_id = :mil_status_id', 'ONHAND');
2859: inv_mwb_query_manager.add_where_clause('mln.status_id = :mln_status_id', 'ONHAND');
2860: inv_mwb_query_manager.add_where_clause('msn.status_id = :msn_status_id', 'ONHAND');
2861: */
2862: -- End Bug 6060233
2863:
2856: /*
2857: inv_mwb_query_manager.add_where_clause('msi.status_id = :msi_status_id', 'ONHAND');
2858: inv_mwb_query_manager.add_where_clause('mil.status_id = :mil_status_id', 'ONHAND');
2859: inv_mwb_query_manager.add_where_clause('mln.status_id = :mln_status_id', 'ONHAND');
2860: inv_mwb_query_manager.add_where_clause('msn.status_id = :msn_status_id', 'ONHAND');
2861: */
2862: -- End Bug 6060233
2863:
2864: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2860: inv_mwb_query_manager.add_where_clause('msn.status_id = :msn_status_id', 'ONHAND');
2861: */
2862: -- End Bug 6060233
2863:
2864: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2865: inv_mwb_query_manager.add_bind_variable('mil_status_id', inv_mwb_globals.g_status_id);
2866: inv_mwb_query_manager.add_bind_variable('mln_status_id', inv_mwb_globals.g_status_id);
2867: inv_mwb_query_manager.add_bind_variable('msn_status_id', inv_mwb_globals.g_status_id);
2868: END IF;
2861: */
2862: -- End Bug 6060233
2863:
2864: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2865: inv_mwb_query_manager.add_bind_variable('mil_status_id', inv_mwb_globals.g_status_id);
2866: inv_mwb_query_manager.add_bind_variable('mln_status_id', inv_mwb_globals.g_status_id);
2867: inv_mwb_query_manager.add_bind_variable('msn_status_id', inv_mwb_globals.g_status_id);
2868: END IF;
2869: else -- Onhand Material Status Support
2862: -- End Bug 6060233
2863:
2864: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2865: inv_mwb_query_manager.add_bind_variable('mil_status_id', inv_mwb_globals.g_status_id);
2866: inv_mwb_query_manager.add_bind_variable('mln_status_id', inv_mwb_globals.g_status_id);
2867: inv_mwb_query_manager.add_bind_variable('msn_status_id', inv_mwb_globals.g_status_id);
2868: END IF;
2869: else -- Onhand Material Status Support
2870: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2863:
2864: inv_mwb_query_manager.add_bind_variable('msi_status_id', inv_mwb_globals.g_status_id);
2865: inv_mwb_query_manager.add_bind_variable('mil_status_id', inv_mwb_globals.g_status_id);
2866: inv_mwb_query_manager.add_bind_variable('mln_status_id', inv_mwb_globals.g_status_id);
2867: inv_mwb_query_manager.add_bind_variable('msn_status_id', inv_mwb_globals.g_status_id);
2868: END IF;
2869: else -- Onhand Material Status Support
2870: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2871: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2866: inv_mwb_query_manager.add_bind_variable('mln_status_id', inv_mwb_globals.g_status_id);
2867: inv_mwb_query_manager.add_bind_variable('msn_status_id', inv_mwb_globals.g_status_id);
2868: END IF;
2869: else -- Onhand Material Status Support
2870: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2871: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2872: inv_mwb_query_manager.add_where_clause('msn.status_id = :msn_status_id','ONHAND');
2873: inv_mwb_query_manager.add_bind_variable('msn_status_id', inv_mwb_globals.g_status_id);
2874: END IF;
2868: END IF;
2869: else -- Onhand Material Status Support
2870: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2871: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2872: inv_mwb_query_manager.add_where_clause('msn.status_id = :msn_status_id','ONHAND');
2873: inv_mwb_query_manager.add_bind_variable('msn_status_id', inv_mwb_globals.g_status_id);
2874: END IF;
2875: end if;
2876: END IF;
2869: else -- Onhand Material Status Support
2870: inv_mwb_query_manager.add_where_clause('NVL (msn.organization_type, 2) = 2', 'ONHAND');
2871: IF inv_mwb_globals.g_view_by <> 'STATUS' THEN
2872: inv_mwb_query_manager.add_where_clause('msn.status_id = :msn_status_id','ONHAND');
2873: inv_mwb_query_manager.add_bind_variable('msn_status_id', inv_mwb_globals.g_status_id);
2874: END IF;
2875: end if;
2876: END IF;
2877:
2952: */
2953:
2954: IF inv_mwb_globals.g_lpn_from_id IS NOT NULL
2955: OR inv_mwb_globals.g_lpn_to_id IS NOT NULL THEN
2956: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ','ONHAND');
2957: add_where_clause('msn.lpn_id = wlpn.lpn_id', 'ONHAND');
2958: add_where_clause('wlpn.lpn_context IN (1,9,11)', 'ONHAND');
2959: END IF;
2960:
3205: END CASE;
3206: END IF;
3207: END IF;
3208: inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Adding tables to the query : ' || p_mat_loc);
3209: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ' , p_mat_loc);
3210: inv_mwb_query_manager.add_from_clause(' wms_lpn_contents wlc ', p_mat_loc);
3211: add_where_clause(' wlc.parent_lpn_id = wlpn.lpn_id ', p_mat_loc);
3212:
3213: IF inv_mwb_globals.g_serial_from IS NOT NULL
3206: END IF;
3207: END IF;
3208: inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Adding tables to the query : ' || p_mat_loc);
3209: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ' , p_mat_loc);
3210: inv_mwb_query_manager.add_from_clause(' wms_lpn_contents wlc ', p_mat_loc);
3211: add_where_clause(' wlc.parent_lpn_id = wlpn.lpn_id ', p_mat_loc);
3212:
3213: IF inv_mwb_globals.g_serial_from IS NOT NULL
3214: OR inv_mwb_globals.g_serial_to IS NOT NULL
3213: IF inv_mwb_globals.g_serial_from IS NOT NULL
3214: OR inv_mwb_globals.g_serial_to IS NOT NULL
3215: OR inv_mwb_globals.g_tree_serial_number IS NOT NULL
3216: OR l_serial_control IN (2, 5) THEN
3217: inv_mwb_query_manager.add_from_clause(' mtl_serial_numbers msn ',p_mat_loc);
3218: add_where_clause('msn.lpn_id = wlpn.lpn_id', p_mat_loc);
3219: add_where_clause('msn.current_organization_id = wlc.organization_id', p_mat_loc);
3220: add_where_clause('msn.inventory_item_id = wlc.inventory_item_id', p_mat_loc);
3221:
3242: END IF;
3243:
3244: IF inv_mwb_globals.g_project_id IS NOT NULL
3245: OR inv_mwb_globals.g_task_id IS NOT NULL THEN
3246: inv_mwb_query_manager.add_from_clause(' mtl_item_locations mil ',p_mat_loc);
3247: add_where_clause('msn.lpn_id = wlpn.lpn_id', p_mat_loc);
3248: add_where_clause('mil.organization_id = wlc.organization_id', p_mat_loc);
3249: add_where_clause('wlpn.locator = mil.inventory_location_id', p_mat_loc);
3250: END IF;
3371:
3372: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
3373: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
3374: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
3375: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', p_mat_loc);
3376: add_where_clause('wlc.lot_number = mln3.lot_number',p_mat_loc);
3377: add_where_clause('wlc.inventory_item_id = mln3.inventory_item_id', p_mat_loc); -- Bug 9927569
3378: add_where_clause('wlc.organization_id = mln3.organization_id', p_mat_loc); -- Bug 9927569
3379:
3464: IF p_flag = 'TREE_LPN' THEN
3465: null;
3466: ELSIF p_flag = 'RECEIVING' THEN
3467: inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Addding table');
3468: inv_mwb_query_manager.add_from_clause(' rcv_supply rs ','RECEIVING');
3469: IF (inv_mwb_globals.g_lot_from IS NOT NULL
3470: OR inv_mwb_globals.g_lot_to IS NOT NULL)
3471: OR l_lot_control = 2 THEN
3472: inv_mwb_query_manager.add_from_clause('rcv_lots_supply rls','RECEIVING');
3468: inv_mwb_query_manager.add_from_clause(' rcv_supply rs ','RECEIVING');
3469: IF (inv_mwb_globals.g_lot_from IS NOT NULL
3470: OR inv_mwb_globals.g_lot_to IS NOT NULL)
3471: OR l_lot_control = 2 THEN
3472: inv_mwb_query_manager.add_from_clause('rcv_lots_supply rls','RECEIVING');
3473: add_where_clause('rls.shipment_line_id(+) = rs.shipment_line_id', 'RECEIVING');
3474:
3475: -- 9720688 start
3476: -- Following condition is added to avoid considering same
3485: inv_mwb_globals.g_lot_to IS NOT NULL AND
3486: inv_mwb_globals.g_lot_from = inv_mwb_globals.g_lot_to) THEN
3487: -- Bug 9865190 Start
3488: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3489: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3490: 'SUM(rls.primary_quantity)';
3491: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3492: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3493: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3487: -- Bug 9865190 Start
3488: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3489: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3490: 'SUM(rls.primary_quantity)';
3491: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3492: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3493: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3494: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3495: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3488: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3489: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3490: 'SUM(rls.primary_quantity)';
3491: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3492: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3493: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3494: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3495: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3496: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
3490: 'SUM(rls.primary_quantity)';
3491: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3492: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3493: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3494: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3495: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3496: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
3497: 'SUM(rls.secondary_quantity)'; -- 8687440
3498: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
3492: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3493: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3494: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3495: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3496: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
3497: 'SUM(rls.secondary_quantity)'; -- 8687440
3498: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
3499: 'SUM(DECODE (rs.lpn_id, null, rls.secondary_quantity, 0))'; -- 8687440
3500: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
3494: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3495: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3496: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
3497: 'SUM(rls.secondary_quantity)'; -- 8687440
3498: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
3499: 'SUM(DECODE (rs.lpn_id, null, rls.secondary_quantity, 0))'; -- 8687440
3500: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
3501: 'SUM(DECODE (rs.lpn_id, null, 0, rls.secondary_quantity))'; -- 8687440
3502: END IF;
3496: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
3497: 'SUM(rls.secondary_quantity)'; -- 8687440
3498: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
3499: 'SUM(DECODE (rs.lpn_id, null, rls.secondary_quantity, 0))'; -- 8687440
3500: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
3501: 'SUM(DECODE (rs.lpn_id, null, 0, rls.secondary_quantity))'; -- 8687440
3502: END IF;
3503: -- Bug 9865190 End
3504: add_where_clause('rls.lot_num = :rcv_lot_from', 'RECEIVING');
3508: IF (inv_mwb_globals.g_lot_from IS NOT NULL AND
3509: inv_mwb_globals.g_lot_from <> NVL(inv_mwb_globals.g_lot_to, -1) ) THEN
3510: -- Bug 9865190 Start
3511: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3512: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3513: 'SUM(rls.primary_quantity)';
3514: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3515: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3516: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3510: -- Bug 9865190 Start
3511: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3512: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3513: 'SUM(rls.primary_quantity)';
3514: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3515: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3516: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3517: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3518: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3511: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3512: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3513: 'SUM(rls.primary_quantity)';
3514: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3515: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3516: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3517: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3518: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3519: END IF;
3513: 'SUM(rls.primary_quantity)';
3514: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3515: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3516: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3517: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3518: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3519: END IF;
3520: -- Bug 9865190 End
3521: add_where_clause('rls.lot_num >= :rcv_lot_from', 'RECEIVING');
3525: IF (inv_mwb_globals.g_lot_to IS NOT NULL AND
3526: inv_mwb_globals.g_lot_to <> NVL(inv_mwb_globals.g_lot_from, -1) ) THEN
3527: -- Bug 9865190 Start
3528: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3529: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3530: 'SUM(rls.primary_quantity)';
3531: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3532: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3533: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3527: -- Bug 9865190 Start
3528: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3529: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3530: 'SUM(rls.primary_quantity)';
3531: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3532: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3533: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3534: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3535: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3528: IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
3529: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3530: 'SUM(rls.primary_quantity)';
3531: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3532: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3533: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3534: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3535: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3536: END IF;
3530: 'SUM(rls.primary_quantity)';
3531: inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
3532: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3533: 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
3534: inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3535: 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
3536: END IF;
3537: -- Bug 9865190 End
3538: add_where_clause('rls.lot_num <= :rcv_lot_to', 'RECEIVING');
3543:
3544: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
3545: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
3546: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
3547: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', 'RECEIVING');
3548: inv_mwb_query_manager.add_from_clause('rcv_lots_supply rls1','RECEIVING');
3549: add_where_clause('rls1.shipment_line_id(+) = rs.shipment_line_id', 'RECEIVING');
3550: add_where_clause('mln3.lot_number = rls1.lot_num', 'RECEIVING');
3551: add_where_clause('rs.item_id = mln3.inventory_item_id', 'RECEIVING'); -- Bug 9927569
3544: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
3545: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
3546: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
3547: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', 'RECEIVING');
3548: inv_mwb_query_manager.add_from_clause('rcv_lots_supply rls1','RECEIVING');
3549: add_where_clause('rls1.shipment_line_id(+) = rs.shipment_line_id', 'RECEIVING');
3550: add_where_clause('mln3.lot_number = rls1.lot_num', 'RECEIVING');
3551: add_where_clause('rs.item_id = mln3.inventory_item_id', 'RECEIVING'); -- Bug 9927569
3552: add_where_clause('rs.to_organization_id = mln3.organization_id', 'RECEIVING'); -- Bug 9927569
3610:
3611: --Bug 14548684 add view mtl_system_items_vl to support multi-language
3612: IF inv_mwb_globals.g_inventory_item_id IS NULL
3613: AND inv_mwb_globals.g_item_description IS NOT NULL THEN
3614: inv_mwb_query_manager.add_from_clause('mtl_system_items_b msib','RECEIVING');
3615: inv_mwb_query_manager.add_from_clause('mtl_system_items_vl msiv', 'RECEIVING');
3616: add_where_clause('msib.inventory_item_id = rs.item_id', 'RECEIVING');
3617: add_where_clause('msib.organization_id = rs.to_organization_id', 'RECEIVING');
3618: add_where_clause('msib.inventory_item_id = msiv.inventory_item_id', 'RECEIVING');
3611: --Bug 14548684 add view mtl_system_items_vl to support multi-language
3612: IF inv_mwb_globals.g_inventory_item_id IS NULL
3613: AND inv_mwb_globals.g_item_description IS NOT NULL THEN
3614: inv_mwb_query_manager.add_from_clause('mtl_system_items_b msib','RECEIVING');
3615: inv_mwb_query_manager.add_from_clause('mtl_system_items_vl msiv', 'RECEIVING');
3616: add_where_clause('msib.inventory_item_id = rs.item_id', 'RECEIVING');
3617: add_where_clause('msib.organization_id = rs.to_organization_id', 'RECEIVING');
3618: add_where_clause('msib.inventory_item_id = msiv.inventory_item_id', 'RECEIVING');
3619: add_where_clause('msib.organization_id = msiv.organization_id', 'RECEIVING');
3640: -- ER(9158529)
3641:
3642: IF inv_mwb_globals.g_lpn_from_id IS NOT NULL
3643: OR inv_mwb_globals.g_lpn_to_id IS NOT NULL THEN
3644: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ','RECEIVING');
3645: add_where_clause('rs.lpn_id = wlpn.lpn_id', 'RECEIVING');
3646: END IF;
3647:
3648: IF (inv_mwb_globals.g_lpn_from_id IS NOT NULL AND
3665: END IF;
3666:
3667: ELSIF p_flag = 'MSN_RECEIVING' THEN
3668:
3669: inv_mwb_query_manager.add_from_clause(' mtl_serial_numbers msn ','RECEIVING');
3670: add_where_clause('msn.current_status = 7', 'RECEIVING');
3671:
3672: /* Bug 8225619. Adding ELSE CONDITION code such that right side grid
3673: retrieves the organizations which are relevant to particular
3706:
3707: --Bug 14548684 add view mtl_system_items_vl to support multi-language
3708: IF inv_mwb_globals.g_inventory_item_id IS NULL
3709: AND inv_mwb_globals.g_item_description IS NOT NULL THEN
3710: inv_mwb_query_manager.add_from_clause('mtl_system_items_kfv msik','RECEIVING');
3711: inv_mwb_query_manager.add_from_clause('mtl_system_items_vl msiv', 'RECEIVING');
3712: add_where_clause('msik.inventory_item_id = msn.inventory_item_id', 'RECEIVING');
3713: add_where_clause('msik.organization_id = msn.current_organization_id', 'RECEIVING');
3714: add_where_clause('msik.inventory_item_id = msiv.inventory_item_id', 'RECEIVING');
3707: --Bug 14548684 add view mtl_system_items_vl to support multi-language
3708: IF inv_mwb_globals.g_inventory_item_id IS NULL
3709: AND inv_mwb_globals.g_item_description IS NOT NULL THEN
3710: inv_mwb_query_manager.add_from_clause('mtl_system_items_kfv msik','RECEIVING');
3711: inv_mwb_query_manager.add_from_clause('mtl_system_items_vl msiv', 'RECEIVING');
3712: add_where_clause('msik.inventory_item_id = msn.inventory_item_id', 'RECEIVING');
3713: add_where_clause('msik.organization_id = msn.current_organization_id', 'RECEIVING');
3714: add_where_clause('msik.inventory_item_id = msiv.inventory_item_id', 'RECEIVING');
3715: add_where_clause('msik.organization_id = msiv.organization_id', 'RECEIVING');
3736: -- ER(9158529)
3737:
3738: IF inv_mwb_globals.g_lpn_from_id IS NOT NULL
3739: OR inv_mwb_globals.g_lpn_to_id IS NOT NULL THEN
3740: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ','RECEIVING');
3741: add_where_clause('msn.lpn_id = wlpn.lpn_id', 'RECEIVING');
3742: END IF;
3743:
3744: IF (inv_mwb_globals.g_lpn_from_id IS NOT NULL AND
3800:
3801: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
3802: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
3803: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
3804: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', 'RECEIVING');
3805: add_where_clause('msn.lot_number =mln3.lot_number', 'RECEIVING');
3806: add_where_clause('msn.inventory_item_id = mln3.inventory_item_id', 'RECEIVING'); -- Bug 9927569
3807: add_where_clause('msn.current_organization_id = mln3.organization_id', 'RECEIVING'); -- Bug 9927569
3808:
3828: /* End of Bug 8396954 */
3829:
3830: ELSIF p_flag = 'MSN' THEN
3831:
3832: inv_mwb_query_manager.add_from_clause(' rcv_supply rs ','RECEIVING');
3833: inv_mwb_query_manager.add_from_clause('rcv_serials_supply rss','RECEIVING');
3834: add_where_clause('rs.shipment_line_id = rss.shipment_line_id (+) ', 'RECEIVING');
3835:
3836: /* Bug 8225619. Adding ELSE CONDITION code such that right side grid
3829:
3830: ELSIF p_flag = 'MSN' THEN
3831:
3832: inv_mwb_query_manager.add_from_clause(' rcv_supply rs ','RECEIVING');
3833: inv_mwb_query_manager.add_from_clause('rcv_serials_supply rss','RECEIVING');
3834: add_where_clause('rs.shipment_line_id = rss.shipment_line_id (+) ', 'RECEIVING');
3835:
3836: /* Bug 8225619. Adding ELSE CONDITION code such that right side grid
3837: retrieves the organizations which are relevant to particular
3869: END IF;
3870:
3871: IF inv_mwb_globals.g_inventory_item_id IS NULL
3872: AND inv_mwb_globals.g_item_description IS NOT NULL THEN
3873: inv_mwb_query_manager.add_from_clause('mtl_system_items_b msib','RECEIVING');
3874: add_where_clause('msib.inventory_item_id = rs.item_id', 'RECEIVING');
3875: add_where_clause('msib.organization_id = rs.to_organization_id', 'RECEIVING');
3876: add_where_clause('upper(msib.description) LIKE upper(:rcv_item_description)', 'RECEIVING');
3877: add_bind_variable('rcv_item_description', inv_mwb_globals.g_item_description);
3896: -- ER(9158529)
3897:
3898: IF inv_mwb_globals.g_lpn_from_id IS NOT NULL
3899: OR inv_mwb_globals.g_lpn_to_id IS NOT NULL THEN
3900: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ','RECEIVING');
3901: add_where_clause('rs.lpn_id = wlpn.lpn_id', 'RECEIVING');
3902: END IF;
3903:
3904: IF (inv_mwb_globals.g_lpn_from_id IS NOT NULL AND
3960:
3961: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
3962: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
3963: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
3964: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', 'RECEIVING');
3965: add_where_clause('mln3.lot_number = rss.lot_num', 'RECEIVING');
3966: add_where_clause('rs.item_id = mln3.inventory_item_id', 'RECEIVING'); -- Bug 9927569
3967: add_where_clause('rs.to_organization_id = mln3.organization_id', 'RECEIVING'); -- Bug 9927569
3968:
4013: AND organization_id = inv_mwb_globals.g_tree_organization_id;
4014:
4015: END IF;
4016:
4017: inv_mwb_query_manager.add_from_clause(' mtl_supply ms ','INBOUND');
4018: inv_mwb_query_manager.add_where_clause('ms.supply_type_code <> ''RECEIVING''','INBOUND');
4019: inv_mwb_query_manager.add_where_clause('ms.destination_type_code = ''INVENTORY''','INBOUND');
4020:
4021:
4014:
4015: END IF;
4016:
4017: inv_mwb_query_manager.add_from_clause(' mtl_supply ms ','INBOUND');
4018: inv_mwb_query_manager.add_where_clause('ms.supply_type_code <> ''RECEIVING''','INBOUND');
4019: inv_mwb_query_manager.add_where_clause('ms.destination_type_code = ''INVENTORY''','INBOUND');
4020:
4021:
4022: --15991963
4015: END IF;
4016:
4017: inv_mwb_query_manager.add_from_clause(' mtl_supply ms ','INBOUND');
4018: inv_mwb_query_manager.add_where_clause('ms.supply_type_code <> ''RECEIVING''','INBOUND');
4019: inv_mwb_query_manager.add_where_clause('ms.destination_type_code = ''INVENTORY''','INBOUND');
4020:
4021:
4022: --15991963
4023: IF inv_mwb_globals.g_include_po_without_asn = 0 THEN
4020:
4021:
4022: --15991963
4023: IF inv_mwb_globals.g_include_po_without_asn = 0 THEN
4024: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code = ''SHIPMENT''','INBOUND');
4025: END IF;
4026:
4027:
4028:
4032: OR inv_mwb_globals.g_vendor_item IS NOT NULL THEN
4033:
4034: inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Supplier tab');
4035:
4036: inv_mwb_query_manager.add_from_clause('po_headers_trx_v pha','INBOUND'); -- CLM project, bug 9403291
4037: inv_mwb_query_manager.add_where_clause(' pha.po_header_id = ms.po_header_id ','INBOUND');
4038: inv_mwb_query_manager.add_where_clause('pha.authorization_status = ''APPROVED''', 'INBOUND');
4039:
4040: /* IF inv_mwb_globals.g_include_po_without_asn = 1 THEN
4033:
4034: inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Supplier tab');
4035:
4036: inv_mwb_query_manager.add_from_clause('po_headers_trx_v pha','INBOUND'); -- CLM project, bug 9403291
4037: inv_mwb_query_manager.add_where_clause(' pha.po_header_id = ms.po_header_id ','INBOUND');
4038: inv_mwb_query_manager.add_where_clause('pha.authorization_status = ''APPROVED''', 'INBOUND');
4039:
4040: /* IF inv_mwb_globals.g_include_po_without_asn = 1 THEN
4041: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''PO'',''SHIPMENT'')','INBOUND');
4034: inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Supplier tab');
4035:
4036: inv_mwb_query_manager.add_from_clause('po_headers_trx_v pha','INBOUND'); -- CLM project, bug 9403291
4037: inv_mwb_query_manager.add_where_clause(' pha.po_header_id = ms.po_header_id ','INBOUND');
4038: inv_mwb_query_manager.add_where_clause('pha.authorization_status = ''APPROVED''', 'INBOUND');
4039:
4040: /* IF inv_mwb_globals.g_include_po_without_asn = 1 THEN
4041: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''PO'',''SHIPMENT'')','INBOUND');
4042: ELSIF inv_mwb_globals.g_include_po_without_asn = 0 THEN
4037: inv_mwb_query_manager.add_where_clause(' pha.po_header_id = ms.po_header_id ','INBOUND');
4038: inv_mwb_query_manager.add_where_clause('pha.authorization_status = ''APPROVED''', 'INBOUND');
4039:
4040: /* IF inv_mwb_globals.g_include_po_without_asn = 1 THEN
4041: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''PO'',''SHIPMENT'')','INBOUND');
4042: ELSIF inv_mwb_globals.g_include_po_without_asn = 0 THEN
4043: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code = ''SHIPMENT''','INBOUND');
4044: -- inv_mwb_query_manager.add_where_clause(' ms.shipment_header_id IS NULL','INBOUND');
4045: END IF;
4039:
4040: /* IF inv_mwb_globals.g_include_po_without_asn = 1 THEN
4041: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''PO'',''SHIPMENT'')','INBOUND');
4042: ELSIF inv_mwb_globals.g_include_po_without_asn = 0 THEN
4043: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code = ''SHIPMENT''','INBOUND');
4044: -- inv_mwb_query_manager.add_where_clause(' ms.shipment_header_id IS NULL','INBOUND');
4045: END IF;
4046: */
4047:
4040: /* IF inv_mwb_globals.g_include_po_without_asn = 1 THEN
4041: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''PO'',''SHIPMENT'')','INBOUND');
4042: ELSIF inv_mwb_globals.g_include_po_without_asn = 0 THEN
4043: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code = ''SHIPMENT''','INBOUND');
4044: -- inv_mwb_query_manager.add_where_clause(' ms.shipment_header_id IS NULL','INBOUND');
4045: END IF;
4046: */
4047:
4048: IF inv_mwb_globals.g_vendor_id IS NOT NULL THEN
4054: END IF;
4055: END IF;
4056:
4057: IF inv_mwb_globals.g_vendor_item IS NOT NULL THEN
4058: inv_mwb_query_manager.add_from_clause('po_lines_trx_v pla ','INBOUND'); -- CLM project, bug 9403291
4059: inv_mwb_query_manager.add_where_clause('ms.po_line_id = pla.po_line_id', 'INBOUND');
4060: inv_mwb_query_manager.add_where_clause('pla.vendor_product_num = :inb_vendor_item', 'INBOUND');
4061: add_bind_variable('inb_vendor_item', inv_mwb_globals.g_vendor_item);
4062: END IF;
4055: END IF;
4056:
4057: IF inv_mwb_globals.g_vendor_item IS NOT NULL THEN
4058: inv_mwb_query_manager.add_from_clause('po_lines_trx_v pla ','INBOUND'); -- CLM project, bug 9403291
4059: inv_mwb_query_manager.add_where_clause('ms.po_line_id = pla.po_line_id', 'INBOUND');
4060: inv_mwb_query_manager.add_where_clause('pla.vendor_product_num = :inb_vendor_item', 'INBOUND');
4061: add_bind_variable('inb_vendor_item', inv_mwb_globals.g_vendor_item);
4062: END IF;
4063:
4056:
4057: IF inv_mwb_globals.g_vendor_item IS NOT NULL THEN
4058: inv_mwb_query_manager.add_from_clause('po_lines_trx_v pla ','INBOUND'); -- CLM project, bug 9403291
4059: inv_mwb_query_manager.add_where_clause('ms.po_line_id = pla.po_line_id', 'INBOUND');
4060: inv_mwb_query_manager.add_where_clause('pla.vendor_product_num = :inb_vendor_item', 'INBOUND');
4061: add_bind_variable('inb_vendor_item', inv_mwb_globals.g_vendor_item);
4062: END IF;
4063:
4064: IF inv_mwb_globals.g_po_header_id IS NOT NULL THEN
4061: add_bind_variable('inb_vendor_item', inv_mwb_globals.g_vendor_item);
4062: END IF;
4063:
4064: IF inv_mwb_globals.g_po_header_id IS NOT NULL THEN
4065: inv_mwb_query_manager.add_where_clause(' ms.po_header_id = :inb_po_header_id ','INBOUND');
4066: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code = ''PO'' ','INBOUND');
4067: add_bind_variable('inb_po_header_id', inv_mwb_globals.g_po_header_id);
4068: END IF;
4069:
4062: END IF;
4063:
4064: IF inv_mwb_globals.g_po_header_id IS NOT NULL THEN
4065: inv_mwb_query_manager.add_where_clause(' ms.po_header_id = :inb_po_header_id ','INBOUND');
4066: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code = ''PO'' ','INBOUND');
4067: add_bind_variable('inb_po_header_id', inv_mwb_globals.g_po_header_id);
4068: END IF;
4069:
4070: IF inv_mwb_globals.g_po_release_id IS NOT NULL THEN
4073: END IF;
4074:
4075: IF inv_mwb_globals.g_shipment_header_id_asn IS NOT NULL
4076: OR inv_mwb_globals.g_tree_doc_type_id IN (3,4) THEN
4077: inv_mwb_query_manager.add_from_clause(' rcv_shipment_headers rsh ','INBOUND');
4078: add_where_clause('rsh.shipment_header_id(+) = ms.shipment_header_id', 'INBOUND');
4079: END IF;
4080:
4081: IF inv_mwb_globals.g_shipment_header_id_asn IS NOT NULL THEN
4078: add_where_clause('rsh.shipment_header_id(+) = ms.shipment_header_id', 'INBOUND');
4079: END IF;
4080:
4081: IF inv_mwb_globals.g_shipment_header_id_asn IS NOT NULL THEN
4082: inv_mwb_query_manager.add_where_clause(' rsh.shipment_header_id = :inb_shipment_header_id_asn ','INBOUND');
4083: add_bind_variable('inb_shipment_header_id_asn', inv_mwb_globals.g_shipment_header_id_asn);
4084: END IF;
4085:
4086: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL
4084: END IF;
4085:
4086: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL
4087: OR inv_mwb_globals.g_tree_doc_type_id IN (3,4) THEN
4088: inv_mwb_query_manager.add_from_clause(' rcv_shipment_lines rsl ','INBOUND');
4089: add_where_clause('rsl.shipment_line_id = ms.shipment_line_id', 'INBOUND');
4090: END IF;
4091:
4092: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
4089: add_where_clause('rsl.shipment_line_id = ms.shipment_line_id', 'INBOUND');
4090: END IF;
4091:
4092: IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
4093: inv_mwb_query_manager.add_where_clause(' rsl.asn_lpn_id = :inb_asn_lpn_id ','INBOUND');
4094: add_bind_variable('inb_asn_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
4095: END IF;
4096:
4097: IF inv_mwb_globals.g_organization_id IS NOT NULL THEN
4163: IF (inv_mwb_globals.g_lot_from IS NOT NULL
4164: OR inv_mwb_globals.g_lot_to IS NOT NULL)
4165: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4166: AND l_lot_control = 2) THEN
4167: inv_mwb_query_manager.add_from_clause(' rcv_lots_supply rls ','INBOUND');
4168: add_where_clause('rls.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4169: END IF;
4170:
4171: IF (inv_mwb_globals.g_lot_from IS NOT NULL AND
4189:
4190: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
4191: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
4192: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
4193: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', 'INBOUND');
4194: IF NOT ((inv_mwb_globals.g_lot_from IS NOT NULL
4195: OR inv_mwb_globals.g_lot_to IS NOT NULL)
4196: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4197: AND l_lot_control = 2)) THEN
4194: IF NOT ((inv_mwb_globals.g_lot_from IS NOT NULL
4195: OR inv_mwb_globals.g_lot_to IS NOT NULL)
4196: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4197: AND l_lot_control = 2)) THEN
4198: inv_mwb_query_manager.add_from_clause(' rcv_lots_supply rls ','INBOUND');
4199: add_where_clause('rls.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4200: END IF;
4201: add_where_clause('mln3.lot_number = rls.lot_num', 'INBOUND');
4202: add_where_clause('ms.item_id = mln3.inventory_item_id', 'INBOUND'); -- Bug 9927569
4226: IF (inv_mwb_globals.g_serial_from IS NOT NULL
4227: OR inv_mwb_globals.g_serial_to IS NOT NULL)
4228: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4229: AND l_serial_control = 2) THEN
4230: inv_mwb_query_manager.add_from_clause(' rcv_serials_supply rss ','INBOUND');
4231: add_where_clause('rss.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4232: END IF;
4233:
4234: IF (inv_mwb_globals.g_serial_from IS NOT NULL AND
4256: OR inv_mwb_globals.g_shipment_header_id_interorg IS NOT NULL THEN
4257:
4258: inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Interorg tab');
4259:
4260: inv_mwb_query_manager.add_from_clause(' rcv_shipment_headers rsh ','INBOUND');
4261: inv_mwb_query_manager.add_from_clause(' rcv_shipment_lines rsl ','INBOUND');
4262:
4263: inv_mwb_query_manager.add_where_clause(' rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4264: inv_mwb_query_manager.add_where_clause(' rsl.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4257:
4258: inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Interorg tab');
4259:
4260: inv_mwb_query_manager.add_from_clause(' rcv_shipment_headers rsh ','INBOUND');
4261: inv_mwb_query_manager.add_from_clause(' rcv_shipment_lines rsl ','INBOUND');
4262:
4263: inv_mwb_query_manager.add_where_clause(' rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4264: inv_mwb_query_manager.add_where_clause(' rsl.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4265: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''REQ'',''SHIPMENT'') ', 'INBOUND');
4259:
4260: inv_mwb_query_manager.add_from_clause(' rcv_shipment_headers rsh ','INBOUND');
4261: inv_mwb_query_manager.add_from_clause(' rcv_shipment_lines rsl ','INBOUND');
4262:
4263: inv_mwb_query_manager.add_where_clause(' rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4264: inv_mwb_query_manager.add_where_clause(' rsl.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4265: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''REQ'',''SHIPMENT'') ', 'INBOUND');
4266: inv_mwb_query_manager.add_where_clause(' rsh.asn_type IS NULL ', 'INBOUND');
4267:
4260: inv_mwb_query_manager.add_from_clause(' rcv_shipment_headers rsh ','INBOUND');
4261: inv_mwb_query_manager.add_from_clause(' rcv_shipment_lines rsl ','INBOUND');
4262:
4263: inv_mwb_query_manager.add_where_clause(' rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4264: inv_mwb_query_manager.add_where_clause(' rsl.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4265: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''REQ'',''SHIPMENT'') ', 'INBOUND');
4266: inv_mwb_query_manager.add_where_clause(' rsh.asn_type IS NULL ', 'INBOUND');
4267:
4268:
4261: inv_mwb_query_manager.add_from_clause(' rcv_shipment_lines rsl ','INBOUND');
4262:
4263: inv_mwb_query_manager.add_where_clause(' rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4264: inv_mwb_query_manager.add_where_clause(' rsl.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4265: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''REQ'',''SHIPMENT'') ', 'INBOUND');
4266: inv_mwb_query_manager.add_where_clause(' rsh.asn_type IS NULL ', 'INBOUND');
4267:
4268:
4269: IF inv_mwb_globals.g_shipment_header_id_interorg IS NOT NULL THEN
4262:
4263: inv_mwb_query_manager.add_where_clause(' rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4264: inv_mwb_query_manager.add_where_clause(' rsl.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4265: inv_mwb_query_manager.add_where_clause(' ms.supply_type_code IN (''REQ'',''SHIPMENT'') ', 'INBOUND');
4266: inv_mwb_query_manager.add_where_clause(' rsh.asn_type IS NULL ', 'INBOUND');
4267:
4268:
4269: IF inv_mwb_globals.g_shipment_header_id_interorg IS NOT NULL THEN
4270: add_where_clause(' rsh.shipment_header_id = :inb_shipment_header_id ', 'INBOUND');
4270: add_where_clause(' rsh.shipment_header_id = :inb_shipment_header_id ', 'INBOUND');
4271: add_where_clause(' ms.supply_type_code = ''SHIPMENT'' ', 'INBOUND');
4272: add_bind_variable('inb_shipment_header_id', inv_mwb_globals.g_shipment_header_id_interorg);
4273: ELSIF inv_mwb_globals.g_req_header_id IS NOT NULL THEN
4274: inv_mwb_query_manager.add_where_clause(' ms.req_header_id = :inb_req_header_id ', 'INBOUND');
4275: add_bind_variable('inb_req_header_id', inv_mwb_globals.g_req_header_id);
4276: END IF;
4277:
4278: IF inv_mwb_globals.g_source_org_id IS NOT NULL THEN
4275: add_bind_variable('inb_req_header_id', inv_mwb_globals.g_req_header_id);
4276: END IF;
4277:
4278: IF inv_mwb_globals.g_source_org_id IS NOT NULL THEN
4279: inv_mwb_query_manager.add_where_clause(' ms.from_organization_id = :inb_from_organization_id ', 'INBOUND');
4280: add_bind_variable('inb_from_organization_id', inv_mwb_globals.g_source_org_id);
4281: END IF;
4282:
4283: IF inv_mwb_globals.g_organization_id IS NOT NULL THEN
4338: END IF;
4339:
4340: IF inv_mwb_globals.g_lpn_from_id IS NOT NULL
4341: OR inv_mwb_globals.g_lpn_to_id IS NOT NULL THEN
4342: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ','INBOUND');
4343: add_where_clause('rsl.asn_lpn_id = wlpn.lpn_id', 'INBOUND');
4344: END IF;
4345:
4346: IF (inv_mwb_globals.g_lpn_from_id IS NOT NULL AND
4367: IF (inv_mwb_globals.g_lot_from IS NOT NULL
4368: OR inv_mwb_globals.g_lot_to IS NOT NULL)
4369: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4370: AND l_lot_control = 2) THEN
4371: inv_mwb_query_manager.add_from_clause(' rcv_lots_supply rls ','INBOUND');
4372: add_where_clause('rls.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4373: END IF;
4374:
4375: IF (inv_mwb_globals.g_lot_from IS NOT NULL AND
4393:
4394: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
4395: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
4396: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
4397: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', 'INBOUND');
4398: IF NOT ((inv_mwb_globals.g_lot_from IS NOT NULL
4399: OR inv_mwb_globals.g_lot_to IS NOT NULL)
4400: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4401: AND l_lot_control = 2)) THEN
4398: IF NOT ((inv_mwb_globals.g_lot_from IS NOT NULL
4399: OR inv_mwb_globals.g_lot_to IS NOT NULL)
4400: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4401: AND l_lot_control = 2)) THEN
4402: inv_mwb_query_manager.add_from_clause(' rcv_lots_supply rls ','INBOUND');
4403: add_where_clause('rls.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4404: END IF;
4405: add_where_clause('mln3.lot_number = rls.lot_num', 'INBOUND');
4406: add_where_clause('ms.item_id = mln3.inventory_item_id', 'INBOUND'); -- Bug 9927569
4430: IF (inv_mwb_globals.g_serial_from IS NOT NULL
4431: OR inv_mwb_globals.g_serial_to IS NOT NULL)
4432: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4433: AND l_serial_control =2) THEN
4434: inv_mwb_query_manager.add_from_clause(' rcv_serials_supply rss ','INBOUND');
4435: add_where_clause('rss.shipment_line_id = ms.shipment_line_id ', 'INBOUND');
4436: END IF;
4437:
4438: IF (inv_mwb_globals.g_serial_from IS NOT NULL AND
4468: END IF;
4469:
4470: ELSE -- If interorg and supplier tab null
4471:
4472: inv_mwb_query_manager.add_from_clause('rcv_shipment_headers rsh ','INBOUND');
4473: inv_mwb_query_manager.add_from_clause('rcv_shipment_lines rsl ','INBOUND');
4474: inv_mwb_query_manager.add_where_clause('rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4475: inv_mwb_query_manager.add_where_clause('rsl.shipment_line_id(+) = ms.shipment_line_id', 'INBOUND');
4476:
4469:
4470: ELSE -- If interorg and supplier tab null
4471:
4472: inv_mwb_query_manager.add_from_clause('rcv_shipment_headers rsh ','INBOUND');
4473: inv_mwb_query_manager.add_from_clause('rcv_shipment_lines rsl ','INBOUND');
4474: inv_mwb_query_manager.add_where_clause('rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4475: inv_mwb_query_manager.add_where_clause('rsl.shipment_line_id(+) = ms.shipment_line_id', 'INBOUND');
4476:
4477: IF inv_mwb_globals.g_organization_id IS NOT NULL THEN
4470: ELSE -- If interorg and supplier tab null
4471:
4472: inv_mwb_query_manager.add_from_clause('rcv_shipment_headers rsh ','INBOUND');
4473: inv_mwb_query_manager.add_from_clause('rcv_shipment_lines rsl ','INBOUND');
4474: inv_mwb_query_manager.add_where_clause('rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4475: inv_mwb_query_manager.add_where_clause('rsl.shipment_line_id(+) = ms.shipment_line_id', 'INBOUND');
4476:
4477: IF inv_mwb_globals.g_organization_id IS NOT NULL THEN
4478: add_where_clause('ms.to_organization_id = :inb_to_organization_id', 'INBOUND');
4471:
4472: inv_mwb_query_manager.add_from_clause('rcv_shipment_headers rsh ','INBOUND');
4473: inv_mwb_query_manager.add_from_clause('rcv_shipment_lines rsl ','INBOUND');
4474: inv_mwb_query_manager.add_where_clause('rsh.shipment_header_id(+) = ms.shipment_header_id ', 'INBOUND');
4475: inv_mwb_query_manager.add_where_clause('rsl.shipment_line_id(+) = ms.shipment_line_id', 'INBOUND');
4476:
4477: IF inv_mwb_globals.g_organization_id IS NOT NULL THEN
4478: add_where_clause('ms.to_organization_id = :inb_to_organization_id', 'INBOUND');
4479: add_bind_variable('inb_to_organization_id', inv_mwb_globals.g_organization_id);
4515: -- ER(9158529)
4516:
4517: IF inv_mwb_globals.g_lpn_from_id IS NOT NULL
4518: OR inv_mwb_globals.g_lpn_to_id IS NOT NULL THEN
4519: inv_mwb_query_manager.add_from_clause(' wms_license_plate_numbers wlpn ','INBOUND');
4520: add_where_clause('rsl.asn_lpn_id = wlpn.lpn_id', 'INBOUND');
4521: END IF;
4522:
4523: IF (inv_mwb_globals.g_lpn_from_id IS NOT NULL AND
4569:
4570: /* Bug 8396954, Adding below if condition for checking supplier_lot_number condition */
4571: IF (inv_mwb_globals.g_supplier_lot_from IS NOT NULL OR
4572: inv_mwb_globals.g_supplier_lot_to IS NOT NULL ) THEN
4573: inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln3', 'INBOUND');
4574: IF NOT (inv_mwb_globals.g_lot_from IS NOT NULL
4575: OR inv_mwb_globals.g_lot_to IS NOT NULL
4576: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4577: AND l_lot_control = 2)
4575: OR inv_mwb_globals.g_lot_to IS NOT NULL
4576: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4577: AND l_lot_control = 2)
4578: OR inv_mwb_globals.g_tree_lot_number IS NOT NULL) THEN
4579: inv_mwb_query_manager.add_from_clause(' rcv_lots_supply rls ','INBOUND');
4580: add_where_clause('rls.shipment_line_id(+) = ms.shipment_line_id ', 'INBOUND');
4581: END IF;
4582: add_where_clause('mln3.lot_number = rls.lot_num', 'INBOUND');
4583: add_where_clause('ms.item_id = mln3.inventory_item_id', 'INBOUND'); -- Bug 9927569
4608: OR inv_mwb_globals.g_serial_to IS NOT NULL
4609: OR (inv_mwb_globals.g_tree_item_id IS NOT NULL
4610: AND l_serial_control = 2)
4611: OR inv_mwb_globals.g_tree_serial_number IS NOT NULL THEN
4612: inv_mwb_query_manager.add_from_clause('rcv_serials_supply rss','INBOUND');
4613: add_where_clause('rss.shipment_line_id(+) = ms.shipment_line_id', 'INBOUND');
4614: END IF;
4615:
4616: -- for bug 8420783
7108: RETURN;
7109: END IF;
7110:
7111: add_from_clause('wms_license_plate_numbers wlpn', 'ONHAND_1');
7112: g_onhand_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7113: 'wlpn.subinventory_code';
7114: g_onhand_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7115: 'wlpn.locator_id';
7116: g_onhand_1_select(inv_mwb_query_manager.LPN).column_value :=
7110:
7111: add_from_clause('wms_license_plate_numbers wlpn', 'ONHAND_1');
7112: g_onhand_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7113: 'wlpn.subinventory_code';
7114: g_onhand_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7115: 'wlpn.locator_id';
7116: g_onhand_1_select(inv_mwb_query_manager.LPN).column_value :=
7117: 'wlpn.license_plate_number';
7118: g_onhand_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7112: g_onhand_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7113: 'wlpn.subinventory_code';
7114: g_onhand_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7115: 'wlpn.locator_id';
7116: g_onhand_1_select(inv_mwb_query_manager.LPN).column_value :=
7117: 'wlpn.license_plate_number';
7118: g_onhand_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7119: 'wlpn.organization_id';
7120: g_onhand_1_select(inv_mwb_query_manager.LPN_ID).column_value :=
7114: g_onhand_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7115: 'wlpn.locator_id';
7116: g_onhand_1_select(inv_mwb_query_manager.LPN).column_value :=
7117: 'wlpn.license_plate_number';
7118: g_onhand_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7119: 'wlpn.organization_id';
7120: g_onhand_1_select(inv_mwb_query_manager.LPN_ID).column_value :=
7121: 'wlpn.lpn_id'; -- 12984304
7122:
7116: g_onhand_1_select(inv_mwb_query_manager.LPN).column_value :=
7117: 'wlpn.license_plate_number';
7118: g_onhand_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7119: 'wlpn.organization_id';
7120: g_onhand_1_select(inv_mwb_query_manager.LPN_ID).column_value :=
7121: 'wlpn.lpn_id'; -- 12984304
7122:
7123: add_group_clause('wlpn.organization_id', 'ONHAND_1');
7124: add_group_clause('wlpn.subinventory_code', 'ONHAND_1');
7149: inv_mwb_globals.g_is_nested_lpn := 'NO';
7150: RETURN;
7151: END IF;
7152:
7153: inv_mwb_query_manager.add_from_clause('wms_license_plate_numbers wlpn', 'RECEIVING_1');
7154: g_receiving_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7155: 'wlpn.subinventory_code';
7156: g_receiving_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7157: 'wlpn.locator_id';
7150: RETURN;
7151: END IF;
7152:
7153: inv_mwb_query_manager.add_from_clause('wms_license_plate_numbers wlpn', 'RECEIVING_1');
7154: g_receiving_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7155: 'wlpn.subinventory_code';
7156: g_receiving_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7157: 'wlpn.locator_id';
7158: g_receiving_1_select(inv_mwb_query_manager.LPN).column_value :=
7152:
7153: inv_mwb_query_manager.add_from_clause('wms_license_plate_numbers wlpn', 'RECEIVING_1');
7154: g_receiving_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7155: 'wlpn.subinventory_code';
7156: g_receiving_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7157: 'wlpn.locator_id';
7158: g_receiving_1_select(inv_mwb_query_manager.LPN).column_value :=
7159: 'wlpn.license_plate_number';
7160: g_receiving_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7154: g_receiving_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7155: 'wlpn.subinventory_code';
7156: g_receiving_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7157: 'wlpn.locator_id';
7158: g_receiving_1_select(inv_mwb_query_manager.LPN).column_value :=
7159: 'wlpn.license_plate_number';
7160: g_receiving_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7161: 'wlpn.organization_id';
7162:
7156: g_receiving_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7157: 'wlpn.locator_id';
7158: g_receiving_1_select(inv_mwb_query_manager.LPN).column_value :=
7159: 'wlpn.license_plate_number';
7160: g_receiving_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7161: 'wlpn.organization_id';
7162:
7163: add_group_clause('wlpn.organization_id', 'RECEIVING_1');
7164: add_group_clause('wlpn.subinventory_code', 'RECEIVING_1');
7190: END IF;
7191:
7192: add_from_clause('wms_license_plate_numbers wlpn', 'INBOUND_1');
7193:
7194: g_inbound_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7195: 'wlpn.subinventory_code';
7196: g_inbound_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7197: 'wlpn.locator_id';
7198: g_inbound_1_select(inv_mwb_query_manager.LPN).column_value :=
7192: add_from_clause('wms_license_plate_numbers wlpn', 'INBOUND_1');
7193:
7194: g_inbound_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7195: 'wlpn.subinventory_code';
7196: g_inbound_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7197: 'wlpn.locator_id';
7198: g_inbound_1_select(inv_mwb_query_manager.LPN).column_value :=
7199: 'wlpn.license_plate_number';
7200: g_inbound_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7194: g_inbound_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
7195: 'wlpn.subinventory_code';
7196: g_inbound_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7197: 'wlpn.locator_id';
7198: g_inbound_1_select(inv_mwb_query_manager.LPN).column_value :=
7199: 'wlpn.license_plate_number';
7200: g_inbound_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7201: 'wlpn.organization_id';
7202:
7196: g_inbound_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
7197: 'wlpn.locator_id';
7198: g_inbound_1_select(inv_mwb_query_manager.LPN).column_value :=
7199: 'wlpn.license_plate_number';
7200: g_inbound_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
7201: 'wlpn.organization_id';
7202:
7203: add_group_clause('wlpn.organization_id', 'INBOUND_1');
7204: add_group_clause('wlpn.subinventory_code', 'INBOUND_1');
7209: add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
7210:
7211: END make_nested_lpn_inbound_query;
7212:
7213: END INV_MWB_QUERY_MANAGER;