[Home] [Help]
724: raise_application_error(-20001, 'Error getting the Schema : ' || sqlerrm);
725: END GET_SCHEMA_NAME;
726:
727:
728: -- This function returns the VMI_FLAG in MSC_ITEM_SUPPLIERS as follows:
729: -- 1. If for a (item,supplier) combination there exists both local and
730: -- global ASLs, then the local will take precedence.
731: -- 2. If the supplier_site is null in msc_supplies but not null in
732: -- msc_item_suppliers, then this record's vmi_flag will not be considered.
728: -- This function returns the VMI_FLAG in MSC_ITEM_SUPPLIERS as follows:
729: -- 1. If for a (item,supplier) combination there exists both local and
730: -- global ASLs, then the local will take precedence.
731: -- 2. If the supplier_site is null in msc_supplies but not null in
732: -- msc_item_suppliers, then this record's vmi_flag will not be considered.
733: -- 3. If the supplier and supplier_site are both null in msc_supplies and supplier_site is
734: -- null in msc_item_suppliers, then this record's vmi_flag will be considered
735: -- 4. If the supplier and supplier_site are not null, then supplier_site level
736: -- record's vmi_flag will be considered.
730: -- global ASLs, then the local will take precedence.
731: -- 2. If the supplier_site is null in msc_supplies but not null in
732: -- msc_item_suppliers, then this record's vmi_flag will not be considered.
733: -- 3. If the supplier and supplier_site are both null in msc_supplies and supplier_site is
734: -- null in msc_item_suppliers, then this record's vmi_flag will be considered
735: -- 4. If the supplier and supplier_site are not null, then supplier_site level
736: -- record's vmi_flag will be considered.
737:
738: FUNCTION get_vmi_flag(var_plan_id IN NUMBER,
744:
745: lv_vmi_flag NUMBER := 2;
746:
747: CURSOR GET_VMI_FLAG_C1 is
748: select vmi_flag from msc_item_suppliers mis
749: Where mis.supplier_id = var_supplier_id
750: and nvl(mis.supplier_site_id,-1) = nvl(var_supplier_site_id,-1)
751: and mis.using_organization_id = var_org_id
752: --AND mis.plan_id = var_plan_id
758: AND ROWNUM = 1;
759:
760: CURSOR GET_VMI_FLAG_C2 is
761: select vmi_flag
762: from msc_item_suppliers mis
763: Where mis.supplier_id = var_supplier_id
764: and mis.supplier_site_id is null
765: and var_supplier_site_id is not null
766: and mis.using_organization_id = var_org_id
773: AND ROWNUM = 1;
774:
775: CURSOR GET_VMI_FLAG_C3 is
776: select vmi_flag
777: from msc_item_suppliers mis
778: Where mis.supplier_id = var_supplier_id
779: and nvl(mis.supplier_site_id,-1) = nvl(var_supplier_site_id,-1)
780: and mis.using_organization_id = -1
781: --AND mis.plan_id = var_plan_id
787: AND ROWNUM = 1;
788:
789: CURSOR GET_VMI_FLAG_C4 is
790: select vmi_flag
791: from msc_item_suppliers mis
792: Where mis.supplier_id = var_supplier_id
793: and mis.supplier_site_id is null
794: and var_supplier_site_id is not null
795: and mis.using_organization_id = -1
803:
804: BEGIN
805:
806: /*
807: We need to query from msc_item_suppliers based
808: on it's unique index in 4 ways depending on the supplier_site_id
809: is null/not null and org_id is -1 or not -1.
810: Instead of having 4 unions, here we have declared 4 cursors
811: with org_id as a parameter.