[Home] [Help]
723: paa.vmi_max_qty,
724: paa.enable_vmi_auto_replenish_flag,
725: paa.vmi_replenishment_approval
726: FROM po_approved_supplier_lis_val_v pasl,
727: po_asl_attributes paa,
728: po_asl_status_rules_v pasr
729: WHERE pasl.item_id = x_item_id --
730: AND pasl.vendor_id = x_vendor_id
731: AND nvl(pasl.vendor_site_id, -1) = nvl(x_vendor_site_id, -1)
735: AND pasr.allow_action_flag like 'Y'
736: AND pasr.status_id = pasl.asl_status_id
737: AND paa.using_organization_id =
738: (SELECT max(paa2.using_organization_id)
739: FROM po_asl_attributes paa2
740: WHERE paa2.asl_id = pasl.asl_id
741: AND paa2.using_organization_id IN (-1, x_using_organization_id))
742: ORDER BY pasl.using_organization_id DESC;
743:
762: paa.vmi_max_qty,
763: paa.enable_vmi_auto_replenish_flag,
764: paa.vmi_replenishment_approval
765: FROM po_approved_supplier_lis_val_v pasl,
766: po_asl_attributes paa,
767: po_asl_status_rules_v pasr
768: WHERE pasl.category_id = p_category_id --
769: AND pasl.vendor_id = x_vendor_id
770: AND nvl(pasl.vendor_site_id, -1) = nvl(x_vendor_site_id, -1)
774: AND pasr.allow_action_flag like 'Y'
775: AND pasr.status_id = pasl.asl_status_id
776: AND paa.using_organization_id =
777: (SELECT max(paa2.using_organization_id)
778: FROM po_asl_attributes paa2
779: WHERE paa2.asl_id = pasl.asl_id
780: AND paa2.using_organization_id IN (-1, x_using_organization_id))
781: ORDER BY pasl.using_organization_id DESC;
782:
918: enable_vmi_flag
919: INTO
920: l_enable_vmi_flag
921: FROM
922: po_asl_attributes asl
923: WHERE
924: asl.asl_id = l_asl_id
925: AND asl.using_organization_id = l_using_organization_id
926: ;
2892: --
2893: --Created dummy variables so that when we call the asl_sourcing procedure, we can ignore the
2894: --values returned by the procedure. We are interested only in asl_id and do not want the
2895: --source document related info.
2896: l_dummy_cons_from_sup_flag PO_ASL_ATTRIBUTES.consigned_from_supplier_flag%type := NULL;
2897: l_dummy_enable_vmi_flag PO_ASL_ATTRIBUTES.enable_vmi_flag%type := NULL;
2898: l_dummy_sequence_number PO_ASL_DOCUMENTS.sequence_num%type := NULL;
2899: l_dummy_vendor_product_num PO_REQUISITIONS_INTERFACE_ALL.suggested_vendor_item_num%type := x_vendor_product_num;
2900: l_dummy_asl_purchasing_uom PO_REQUISITIONS_INTERFACE_ALL.unit_of_measure%type := x_purchasing_uom;
2893: --Created dummy variables so that when we call the asl_sourcing procedure, we can ignore the
2894: --values returned by the procedure. We are interested only in asl_id and do not want the
2895: --source document related info.
2896: l_dummy_cons_from_sup_flag PO_ASL_ATTRIBUTES.consigned_from_supplier_flag%type := NULL;
2897: l_dummy_enable_vmi_flag PO_ASL_ATTRIBUTES.enable_vmi_flag%type := NULL;
2898: l_dummy_sequence_number PO_ASL_DOCUMENTS.sequence_num%type := NULL;
2899: l_dummy_vendor_product_num PO_REQUISITIONS_INTERFACE_ALL.suggested_vendor_item_num%type := x_vendor_product_num;
2900: l_dummy_asl_purchasing_uom PO_REQUISITIONS_INTERFACE_ALL.unit_of_measure%type := x_purchasing_uom;
2901: l_dummy_category_id PO_REQUISITIONS_INTERFACE_ALL.category_id%type := p_category_id;
3792: )
3793: IS
3794: --SQL WHAT: Get the matching asl_id if one exists in current OU
3795: --SQL WHY: This information will be used to identify the document in ASL.
3796: --SQL JOIN: po_asl_attributes using asl_id, po_asl_status_rules_v using status_id
3797: -- po_vendor_sites_all using vendor_site_id
3798: SELECT pasl.asl_id, paa.using_organization_id,
3799: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
3800: paa.consigned_from_supplier_flag, paa.enable_vmi_flag
3798: SELECT pasl.asl_id, paa.using_organization_id,
3799: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
3800: paa.consigned_from_supplier_flag, paa.enable_vmi_flag
3801: FROM po_approved_supplier_lis_val_v pasl,
3802: po_asl_attributes paa,
3803: po_asl_status_rules_v pasr,
3804: po_vendor_sites_all pvs
3805: WHERE pasl.item_id = p_item_id --
3806: AND pasl.vendor_id = p_vendor_id
3829: )
3830: IS
3831: --SQL WHAT: Get the matching asl_id, sequence_num of GA if one exists in other OU
3832: --SQL WHY: This information will be used to identify the document in ASL.
3833: --SQL JOIN: po_asl_attributes using asl_id, po_asl_status_rules_v using status_id
3834: -- po_asl_docuyment using asl_id, sequence_num, po_headers_all using
3835: -- po_header_id, po_lines_all using po_line_id
3836: SELECT pasl.asl_id, paa.using_organization_id,
3837: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
3837: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
3838: paa.consigned_from_supplier_flag, paa.enable_vmi_flag,
3839: pad.sequence_num
3840: FROM po_approved_supplier_lis_val_v pasl,
3841: po_asl_attributes paa,
3842: po_asl_status_rules_v pasr,
3843: po_asl_documents pad,
3844: po_headers_all poh,
3845: po_lines_all pol
4165: )
4166: IS
4167: --SQL WHAT: Get the matching asl_id if one exists in current OU
4168: --SQL WHY: This information will be used to identify the document in ASL.
4169: --SQL JOIN: po_asl_attributes using asl_id, po_asl_status_rules_v using status_id
4170: -- po_vendor_sites_all using vendor_site_id
4171: SELECT pasl.asl_id, paa.using_organization_id,
4172: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
4173: paa.consigned_from_supplier_flag, paa.enable_vmi_flag
4171: SELECT pasl.asl_id, paa.using_organization_id,
4172: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
4173: paa.consigned_from_supplier_flag, paa.enable_vmi_flag
4174: FROM po_approved_supplier_lis_val_v pasl,
4175: po_asl_attributes paa,
4176: po_asl_status_rules_v pasr,
4177: po_vendor_sites_all pvs
4178: WHERE pasl.category_id = p_category_id --
4179: AND pasl.vendor_id = p_vendor_id
4202: )
4203: IS
4204: --SQL WHAT: Get the matching asl_id, sequence_num of GA if one exists in other OU
4205: --SQL WHY: This information will be used to identify the document in ASL.
4206: --SQL JOIN: po_asl_attributes using asl_id, po_asl_status_rules_v using status_id
4207: -- po_asl_docuyment using asl_id, sequence_num, po_headers_all using
4208: -- po_header_id, po_lines_all using po_line_id
4209: SELECT pasl.asl_id, paa.using_organization_id,
4210: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
4210: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
4211: paa.consigned_from_supplier_flag, paa.enable_vmi_flag,
4212: pad.sequence_num
4213: FROM po_approved_supplier_lis_val_v pasl,
4214: po_asl_attributes paa,
4215: po_asl_status_rules_v pasr,
4216: po_asl_documents pad,
4217: po_headers_all poh,
4218: po_lines_all pol
4745: x_rate_date DATE := NULL;
4746: x_rate NUMBER := NULL;
4747: x_return_code BOOLEAN := NULL;
4748: x_commodity_id NUMBER := NULL;
4749: x_purchasing_uom po_asl_attributes.purchasing_unit_of_measure%type;
4750: x_uom_code po_requisitions_interface.uom_code%type;
4751: x_unit_of_measure po_requisitions_interface.unit_of_measure%type;
4752: x_autosource_flag po_requisitions_interface.autosource_flag%type;
4753: x_organization_id NUMBER := NULL;
4768: l_req_dist_sequence_id PO_REQUISITIONS_INTERFACE.req_dist_sequence_id%type;
4769: l_primary_uom MTL_SYSTEM_ITEMS.primary_unit_of_measure%type;
4770: l_unit_of_issue MTL_SYSTEM_ITEMS.unit_of_issue%type;
4771: l_rounding_factor MTL_SYSTEM_ITEMS.rounding_factor%type;
4772: l_min_ord_qty PO_ASL_ATTRIBUTES.min_order_qty%type;
4773: l_fixed_lot_multiple PO_ASL_ATTRIBUTES.fixed_lot_multiple%type;
4774: l_uom_conversion_rate NUMBER;
4775: l_enforce_full_lot_qty PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%type;
4776: l_interface_source_code PO_REQUISITIONS_INTERFACE.interface_source_code%type;
4769: l_primary_uom MTL_SYSTEM_ITEMS.primary_unit_of_measure%type;
4770: l_unit_of_issue MTL_SYSTEM_ITEMS.unit_of_issue%type;
4771: l_rounding_factor MTL_SYSTEM_ITEMS.rounding_factor%type;
4772: l_min_ord_qty PO_ASL_ATTRIBUTES.min_order_qty%type;
4773: l_fixed_lot_multiple PO_ASL_ATTRIBUTES.fixed_lot_multiple%type;
4774: l_uom_conversion_rate NUMBER;
4775: l_enforce_full_lot_qty PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%type;
4776: l_interface_source_code PO_REQUISITIONS_INTERFACE.interface_source_code%type;
4777: l_asl_purchasing_uom PO_ASL_ATTRIBUTES.purchasing_unit_of_measure%type; --
4773: l_fixed_lot_multiple PO_ASL_ATTRIBUTES.fixed_lot_multiple%type;
4774: l_uom_conversion_rate NUMBER;
4775: l_enforce_full_lot_qty PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%type;
4776: l_interface_source_code PO_REQUISITIONS_INTERFACE.interface_source_code%type;
4777: l_asl_purchasing_uom PO_ASL_ATTRIBUTES.purchasing_unit_of_measure%type; --
4779:
4780: --
4781: l_line_type_id PO_REQUISITION_LINES_ALL.line_type_id%TYPE;
5132: l_primary_uom := NULL;
5133: l_rounding_factor := NULL;
5134: END;
5135:
5136: --* Retrieving the min_order_qty, fixed_lot_multiple from PO_ASL_ATTRIBUTES table,
5137: -- only if primary_uom of the item is same as the UOM mentioned on the requisition.
5138:
5139: --* This if condition is required as Order Modifiers will only be applied in case
5140: -- the above condition is true.
5147: IF (l_asl_id IS NOT NULL AND l_interface_source_code = 'INV' ) THEN
5148: BEGIN
5149: SELECT min_order_qty, fixed_lot_multiple, purchasing_unit_of_measure
5150: INTO l_min_ord_qty, l_fixed_lot_multiple, l_asl_purchasing_uom
5151: FROM PO_ASL_ATTRIBUTES
5152: WHERE ASL_ID = l_asl_id;
5153: EXCEPTION
5154: WHEN OTHERS THEN
5155: l_min_ord_qty := NULL;
5479: pasl.asl_id,
5480: pasl.primary_vendor_item,
5481: paa.purchasing_unit_of_measure
5482: FROM po_approved_supplier_lis_val_v pasl,
5483: po_asl_attributes paa,
5484: po_asl_status_rules_v pasr
5485: WHERE pasl.item_id = x_item_id
5486: AND (pasl.using_organization_id IN
5487: (-1, x_using_organization_id))
5490: AND pasr.allow_action_flag like 'Y'
5491: AND pasr.status_id = pasl.asl_status_id
5492: AND paa.using_organization_id =
5493: (SELECT max(paa2.using_organization_id)
5494: FROM po_asl_attributes paa2
5495: WHERE paa2.asl_id = pasl.asl_id
5496: AND (pasl.using_organization_id IN
5497: (-1,x_using_organization_id)))
5498: ORDER BY pasl.using_organization_id DESC;