[Home] [Help]
700: -- fetches the UOM from the local attributes record before the
701: -- the global.
702:
703: /* Bug # 1671405. pchintal
704: Added the table PO_ASL_STATUS_RULES_V and corresponding where condition
705: to the below cursor so that it will not fetch those asl_id's whose
706: status has a control value of PREVENT and rule is SOURCING, so that
707: the ASL with status debarred will not be picked and sourcing will happen
708: properly.
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)
732: AND pasl.using_organization_id IN (-1, x_using_organization_id)
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)
771: AND pasl.using_organization_id IN (-1, x_using_organization_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
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
3807: AND pasl.using_organization_id in (-1, p_using_organization_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,
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
3846: WHERE pasl.item_id = p_item_id --
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
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
4180: AND pasl.using_organization_id in (-1, p_using_organization_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,
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
4219: WHERE pasl.category_id = p_category_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))
5488: AND pasl.asl_id = paa.asl_id