[Home] [Help]
982: raise_application_error(-20001, 'Error getting the Schema : ' || sqlerrm);
983: END GET_SCHEMA_NAME;
984:
985:
986: -- This function returns the VMI_FLAG in MSC_ITEM_SUPPLIERS as follows:
987: -- 1. If for a (item,supplier) combination there exists both local and
988: -- global ASLs, then the local will take precedence.
989: -- 2. If the supplier_site is null in msc_supplies but not null in
990: -- msc_item_suppliers, then this record's vmi_flag will not be considered.
986: -- This function returns the VMI_FLAG in MSC_ITEM_SUPPLIERS as follows:
987: -- 1. If for a (item,supplier) combination there exists both local and
988: -- global ASLs, then the local will take precedence.
989: -- 2. If the supplier_site is null in msc_supplies but not null in
990: -- msc_item_suppliers, then this record's vmi_flag will not be considered.
991: -- 3. If the supplier and supplier_site are both null in msc_supplies and supplier_site is
992: -- null in msc_item_suppliers, then this record's vmi_flag will be considered
993: -- 4. If the supplier and supplier_site are not null, then supplier_site level
994: -- record's vmi_flag will be considered.
988: -- global ASLs, then the local will take precedence.
989: -- 2. If the supplier_site is null in msc_supplies but not null in
990: -- msc_item_suppliers, then this record's vmi_flag will not be considered.
991: -- 3. If the supplier and supplier_site are both null in msc_supplies and supplier_site is
992: -- null in msc_item_suppliers, then this record's vmi_flag will be considered
993: -- 4. If the supplier and supplier_site are not null, then supplier_site level
994: -- record's vmi_flag will be considered.
995:
996: FUNCTION get_vmi_flag(var_plan_id IN NUMBER,
1002:
1003: lv_vmi_flag NUMBER := 2;
1004:
1005: CURSOR GET_VMI_FLAG_C1 is
1006: select vmi_flag from msc_item_suppliers mis
1007: Where mis.supplier_id = var_supplier_id
1008: and nvl(mis.supplier_site_id,-1) = nvl(var_supplier_site_id,-1)
1009: and mis.using_organization_id = var_org_id
1010: --AND mis.plan_id = var_plan_id
1016: AND ROWNUM = 1;
1017:
1018: CURSOR GET_VMI_FLAG_C2 is
1019: select vmi_flag
1020: from msc_item_suppliers mis
1021: Where mis.supplier_id = var_supplier_id
1022: and mis.supplier_site_id is null
1023: and var_supplier_site_id is not null
1024: and mis.using_organization_id = var_org_id
1031: AND ROWNUM = 1;
1032:
1033: CURSOR GET_VMI_FLAG_C3 is
1034: select vmi_flag
1035: from msc_item_suppliers mis
1036: Where mis.supplier_id = var_supplier_id
1037: and nvl(mis.supplier_site_id,-1) = nvl(var_supplier_site_id,-1)
1038: and mis.using_organization_id = -1
1039: --AND mis.plan_id = var_plan_id
1045: AND ROWNUM = 1;
1046:
1047: CURSOR GET_VMI_FLAG_C4 is
1048: select vmi_flag
1049: from msc_item_suppliers mis
1050: Where mis.supplier_id = var_supplier_id
1051: and mis.supplier_site_id is null
1052: and var_supplier_site_id is not null
1053: and mis.using_organization_id = -1
1061:
1062: BEGIN
1063:
1064: /*
1065: We need to query from msc_item_suppliers based
1066: on it's unique index in 4 ways depending on the supplier_site_id
1067: is null/not null and org_id is -1 or not -1.
1068: Instead of having 4 unions, here we have declared 4 cursors
1069: with org_id as a parameter.