[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)
736: AND pasr.allow_action_flag like 'Y'
737: AND pasr.status_id = pasl.asl_status_id
738: AND paa.using_organization_id =
739: (SELECT max(paa2.using_organization_id)
740: FROM po_asl_attributes paa2
741: WHERE paa2.asl_id = pasl.asl_id
742: AND paa2.using_organization_id IN (-1, x_using_organization_id))
743: ORDER BY pasl.using_organization_id DESC,
744: NVL(pasl.vendor_site_id,-1) DESC; --Bug #13743965
764: paa.vmi_max_qty,
765: paa.enable_vmi_auto_replenish_flag,
766: paa.vmi_replenishment_approval
767: FROM po_approved_supplier_lis_val_v pasl,
768: po_asl_attributes paa,
769: po_asl_status_rules_v pasr
770: WHERE pasl.category_id = p_category_id --
771: AND pasl.vendor_id = x_vendor_id
772: AND (nvl(pasl.vendor_site_id, -1) = nvl(x_vendor_site_id, -1)
777: AND pasr.allow_action_flag like 'Y'
778: AND pasr.status_id = pasl.asl_status_id
779: AND paa.using_organization_id =
780: (SELECT max(paa2.using_organization_id)
781: FROM po_asl_attributes paa2
782: WHERE paa2.asl_id = pasl.asl_id
783: AND paa2.using_organization_id IN (-1, x_using_organization_id))
784: ORDER BY pasl.using_organization_id DESC,
785: NVL(pasl.vendor_site_id,-1) DESC; --Bug #13743965
922: enable_vmi_flag
923: INTO
924: l_enable_vmi_flag
925: FROM
926: po_asl_attributes asl
927: WHERE
928: asl.asl_id = l_asl_id
929: AND asl.using_organization_id = l_using_organization_id
930: ;
2915: --
2916: --Created dummy variables so that when we call the asl_sourcing procedure, we can ignore the
2917: --values returned by the procedure. We are interested only in asl_id and do not want the
2918: --source document related info.
2919: l_dummy_cons_from_sup_flag PO_ASL_ATTRIBUTES.consigned_from_supplier_flag%type := NULL;
2920: l_dummy_enable_vmi_flag PO_ASL_ATTRIBUTES.enable_vmi_flag%type := NULL;
2921: l_dummy_sequence_number PO_ASL_DOCUMENTS.sequence_num%type := NULL;
2922: l_dummy_vendor_product_num PO_REQUISITIONS_INTERFACE_ALL.suggested_vendor_item_num%type := x_vendor_product_num;
2923: l_dummy_asl_purchasing_uom PO_REQUISITIONS_INTERFACE_ALL.unit_of_measure%type := x_purchasing_uom;
2916: --Created dummy variables so that when we call the asl_sourcing procedure, we can ignore the
2917: --values returned by the procedure. We are interested only in asl_id and do not want the
2918: --source document related info.
2919: l_dummy_cons_from_sup_flag PO_ASL_ATTRIBUTES.consigned_from_supplier_flag%type := NULL;
2920: l_dummy_enable_vmi_flag PO_ASL_ATTRIBUTES.enable_vmi_flag%type := NULL;
2921: l_dummy_sequence_number PO_ASL_DOCUMENTS.sequence_num%type := NULL;
2922: l_dummy_vendor_product_num PO_REQUISITIONS_INTERFACE_ALL.suggested_vendor_item_num%type := x_vendor_product_num;
2923: l_dummy_asl_purchasing_uom PO_REQUISITIONS_INTERFACE_ALL.unit_of_measure%type := x_purchasing_uom;
2924: l_dummy_category_id PO_REQUISITIONS_INTERFACE_ALL.category_id%type := p_category_id;
3818: )
3819: IS
3820: --SQL WHAT: Get the matching asl_id if one exists in current OU
3821: --SQL WHY: This information will be used to identify the document in ASL.
3822: --SQL JOIN: po_asl_attributes using asl_id, po_asl_status_rules_v using status_id
3823: -- po_vendor_sites_all using vendor_site_id
3824: SELECT pasl.asl_id, paa.using_organization_id,
3825: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
3826: paa.consigned_from_supplier_flag, paa.enable_vmi_flag
3824: SELECT pasl.asl_id, paa.using_organization_id,
3825: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
3826: paa.consigned_from_supplier_flag, paa.enable_vmi_flag
3827: FROM po_approved_supplier_lis_val_v pasl,
3828: po_asl_attributes paa,
3829: po_asl_status_rules_v pasr,
3830: po_vendor_sites_all pvs
3831: WHERE pasl.item_id = p_item_id --
3832: AND pasl.vendor_id = p_vendor_id
3857: )
3858: IS
3859: --SQL WHAT: Get the matching asl_id, sequence_num of GA if one exists in other OU
3860: --SQL WHY: This information will be used to identify the document in ASL.
3861: --SQL JOIN: po_asl_attributes using asl_id, po_asl_status_rules_v using status_id
3862: -- po_asl_docuyment using asl_id, sequence_num, po_headers_all using
3863: -- po_header_id, po_lines_all using po_line_id
3864: SELECT pasl.asl_id, paa.using_organization_id,
3865: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
3865: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
3866: paa.consigned_from_supplier_flag, paa.enable_vmi_flag,
3867: pad.sequence_num
3868: FROM po_approved_supplier_lis_val_v pasl,
3869: po_asl_attributes paa,
3870: po_asl_status_rules_v pasr,
3871: po_asl_documents pad,
3872: po_headers_all poh,
3873: po_lines_all pol
4193: )
4194: IS
4195: --SQL WHAT: Get the matching asl_id if one exists in current OU
4196: --SQL WHY: This information will be used to identify the document in ASL.
4197: --SQL JOIN: po_asl_attributes using asl_id, po_asl_status_rules_v using status_id
4198: -- po_vendor_sites_all using vendor_site_id
4199: SELECT pasl.asl_id, paa.using_organization_id,
4200: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
4201: paa.consigned_from_supplier_flag, paa.enable_vmi_flag
4199: SELECT pasl.asl_id, paa.using_organization_id,
4200: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
4201: paa.consigned_from_supplier_flag, paa.enable_vmi_flag
4202: FROM po_approved_supplier_lis_val_v pasl,
4203: po_asl_attributes paa,
4204: po_asl_status_rules_v pasr,
4205: po_vendor_sites_all pvs
4206: WHERE pasl.category_id = p_category_id --
4207: AND pasl.vendor_id = p_vendor_id
4230: )
4231: IS
4232: --SQL WHAT: Get the matching asl_id, sequence_num of GA if one exists in other OU
4233: --SQL WHY: This information will be used to identify the document in ASL.
4234: --SQL JOIN: po_asl_attributes using asl_id, po_asl_status_rules_v using status_id
4235: -- po_asl_docuyment using asl_id, sequence_num, po_headers_all using
4236: -- po_header_id, po_lines_all using po_line_id
4237: SELECT pasl.asl_id, paa.using_organization_id,
4238: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
4238: pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
4239: paa.consigned_from_supplier_flag, paa.enable_vmi_flag,
4240: pad.sequence_num
4241: FROM po_approved_supplier_lis_val_v pasl,
4242: po_asl_attributes paa,
4243: po_asl_status_rules_v pasr,
4244: po_asl_documents pad,
4245: po_headers_all poh,
4246: po_lines_all pol
4783: x_rate_date DATE := NULL;
4784: x_rate NUMBER := NULL;
4785: x_return_code BOOLEAN := NULL;
4786: x_commodity_id NUMBER := NULL;
4787: x_purchasing_uom po_asl_attributes.purchasing_unit_of_measure%type;
4788: x_uom_code po_requisitions_interface.uom_code%type;
4789: x_unit_of_measure po_requisitions_interface.unit_of_measure%type;
4790: x_autosource_flag po_requisitions_interface.autosource_flag%type;
4791: x_organization_id NUMBER := NULL;
4806: l_req_dist_sequence_id PO_REQUISITIONS_INTERFACE.req_dist_sequence_id%type;
4807: l_primary_uom MTL_SYSTEM_ITEMS.primary_unit_of_measure%type;
4808: l_unit_of_issue MTL_SYSTEM_ITEMS.unit_of_issue%type;
4809: l_rounding_factor MTL_SYSTEM_ITEMS.rounding_factor%type;
4810: l_min_ord_qty PO_ASL_ATTRIBUTES.min_order_qty%type;
4811: l_fixed_lot_multiple PO_ASL_ATTRIBUTES.fixed_lot_multiple%type;
4812: l_uom_conversion_rate NUMBER;
4813: l_enforce_full_lot_qty PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%type;
4814: l_interface_source_code PO_REQUISITIONS_INTERFACE.interface_source_code%type;
4807: l_primary_uom MTL_SYSTEM_ITEMS.primary_unit_of_measure%type;
4808: l_unit_of_issue MTL_SYSTEM_ITEMS.unit_of_issue%type;
4809: l_rounding_factor MTL_SYSTEM_ITEMS.rounding_factor%type;
4810: l_min_ord_qty PO_ASL_ATTRIBUTES.min_order_qty%type;
4811: l_fixed_lot_multiple PO_ASL_ATTRIBUTES.fixed_lot_multiple%type;
4812: l_uom_conversion_rate NUMBER;
4813: l_enforce_full_lot_qty PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%type;
4814: l_interface_source_code PO_REQUISITIONS_INTERFACE.interface_source_code%type;
4815: l_asl_purchasing_uom PO_ASL_ATTRIBUTES.purchasing_unit_of_measure%type; --
4811: l_fixed_lot_multiple PO_ASL_ATTRIBUTES.fixed_lot_multiple%type;
4812: l_uom_conversion_rate NUMBER;
4813: l_enforce_full_lot_qty PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%type;
4814: l_interface_source_code PO_REQUISITIONS_INTERFACE.interface_source_code%type;
4815: l_asl_purchasing_uom PO_ASL_ATTRIBUTES.purchasing_unit_of_measure%type; --
4817:
4818: --
4819: l_line_type_id PO_REQUISITION_LINES_ALL.line_type_id%TYPE;
5174: l_primary_uom := NULL;
5175: l_rounding_factor := NULL;
5176: END;
5177:
5178: --* Retrieving the min_order_qty, fixed_lot_multiple from PO_ASL_ATTRIBUTES table,
5179: -- only if primary_uom of the item is same as the UOM mentioned on the requisition.
5180:
5181: --* This if condition is required as Order Modifiers will only be applied in case
5182: -- the above condition is true.
5189: IF (l_asl_id IS NOT NULL AND l_interface_source_code in ('INV','MRP') ) THEN
5190: BEGIN
5191: SELECT min_order_qty, fixed_lot_multiple, purchasing_unit_of_measure
5192: INTO l_min_ord_qty, l_fixed_lot_multiple, l_asl_purchasing_uom
5193: FROM PO_ASL_ATTRIBUTES
5194: WHERE ASL_ID = l_asl_id;
5195: EXCEPTION
5196: WHEN OTHERS THEN
5197: l_min_ord_qty := NULL;
5556: pasl.asl_id,
5557: pasl.primary_vendor_item,
5558: paa.purchasing_unit_of_measure
5559: FROM po_approved_supplier_lis_val_v pasl,
5560: po_asl_attributes paa,
5561: po_asl_status_rules_v pasr
5562: WHERE pasl.item_id = x_item_id
5563: AND (pasl.using_organization_id IN
5564: (-1, x_using_organization_id))
5567: AND pasr.allow_action_flag like 'Y'
5568: AND pasr.status_id = pasl.asl_status_id
5569: AND paa.using_organization_id =
5570: (SELECT max(paa2.using_organization_id)
5571: FROM po_asl_attributes paa2
5572: WHERE paa2.asl_id = pasl.asl_id
5573: AND (pasl.using_organization_id IN
5574: (-1,x_using_organization_id)))
5575: ORDER BY pasl.using_organization_id DESC;
7046: INTO
7047: x_consignEnabled_flag,
7048: x_VmiEnabled_flag
7049: FROM po_approved_supplier_list pasl,
7050: po_asl_attributes paa,
7051: po_asl_status_rules pasr,
7052: po_vendor_sites_all pvsl,
7053: org_organization_definitions currentorg_ou
7054: WHERE pasl.item_id = p_item_id
7059: AND pasr.status_id = pasl.asl_status_id
7060: AND ( disable_flag IS NULL
7061: OR disable_flag = 'N' )
7062: AND paa.using_organization_id = (SELECT MAX(paa2.using_organization_id)
7063: FROM po_asl_attributes paa2
7064: WHERE paa2.asl_id = pasl.asl_id
7065: AND paa2.using_organization_id IN( -1,p_organization_id ))
7066: AND pvsl.vendor_id = pasl.vendor_id
7067: AND currentorg_ou.organization_id = p_organization_id