[Home] [Help]
142: AND Nvl(poll.cancel_flag,'N') = 'N'
143: AND receiving_routing_id = 3 --- Direct only supported by OPM
144: -- AND poll.closed_code = 'OPEN' -- Bug 2859355
145: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING')
146: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
147: AND poll.ship_to_organization_id = p_organization_id)
148: AND pl.line_num LIKE (p_po_line_num)
149: AND nvl(pl.item_id,-999) LIKE nvl(p_inventory_item_id,'%')
150: UNION ALL
171: AND Nvl(poll.cancel_flag,'N') = 'N'
172: AND receiving_routing_id = 3 --- Direct only supported by OPM
173: -- AND poll.closed_code = 'OPEN' --Bug 2859355
174: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED','CLOSED FOR RECEIVING')
175: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
176: AND poll.ship_to_organization_id = p_organization_id)
177: AND pl.line_num LIKE (p_po_line_num)
178: AND ( (mri.related_item_id = msi.inventory_item_id
179: and pl.item_id = mri.inventory_item_id
479: WHERE poll.po_header_id = po.po_header_id
480: AND Nvl(poll.approved_flag,'N') = 'Y'
481: AND Nvl(poll.cancel_flag,'N') = 'N'
482: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED','FINALLY CLOSED','CLOSED FOR RECEIVING')
483: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
484: AND poll.ship_to_organization_id = p_organization_id
485: AND poll.po_line_id = po.po_line_id
486: AND po.item_id = p_item_id
487: AND po.po_header_id = p_po_header_id);
506: WHERE poll.po_header_id = po.po_header_id
507: AND Nvl(poll.approved_flag,'N') = 'Y'
508: AND Nvl(poll.cancel_flag,'N') = 'N'
509: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING')
510: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
511: AND poll.ship_to_organization_id = p_organization_id
512: AND poll.po_line_id = po.po_line_id
513: AND po.item_id = p_item_id
514: AND po.po_header_id = p_po_header_id)
678: AND Nvl(poll.cancel_flag,'N') = 'N'
679: -- AND poll.closed_code = 'OPEN' -- Bug 2859335
680: AND receiving_routing_id = 3 --- Direct only supported by OPM
681: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
682: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
683: AND poll.ship_to_organization_id = p_organization_id)
684: -- Bug 2859355 Added the Extra conditions for poh.
685: AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
686: AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
681: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
682: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
683: AND poll.ship_to_organization_id = p_organization_id)
684: -- Bug 2859355 Added the Extra conditions for poh.
685: AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
686: AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
687: AND NVL(POH.CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3152693
688: AND poh.segment1 LIKE (p_doc_number)
689: AND exists ( select 'x'
728: AND Nvl(poll.cancel_flag,'N') = 'N'
729: -- AND poll.closed_code = 'OPEN' -- Bug 2859355
730: AND receiving_routing_id = 3 --- Direct only supported by OPM
731: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
732: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
733: AND poll.ship_to_organization_id = p_organization_id)
734: -- Bug 2859355 Added the Extra conditions for poh.
735: AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
736: AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
731: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
732: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
733: AND poll.ship_to_organization_id = p_organization_id)
734: -- Bug 2859355 Added the Extra conditions for poh.
735: AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
736: AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
737: AND NVL(POH.CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3152693
738: AND poh.segment1 LIKE (p_doc_number)
739: AND exists ( select 'x'
783: AND Nvl(poll.cancel_flag,'N') = 'N'
784: AND receiving_routing_id = 3 --- Direct only supported by OPM
785: -- AND poll.closed_code = 'OPEN' --Bug 2859355
786: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
787: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
788: AND poll.ship_to_organization_id = p_organization_id)
789: -- Bug 2859355 Added the Extra conditions for poh.
790: AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
791: AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
786: AND Nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3687249
787: AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
788: AND poll.ship_to_organization_id = p_organization_id)
789: -- Bug 2859355 Added the Extra conditions for poh.
790: AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT')
791: AND NVL(POH.CANCEL_FLAG, 'N') IN ('N', 'I')
792: AND NVL(POH.CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED','CLOSED FOR RECEIVING', 'CLOSED') -- 3152693
793: AND poh.segment1 LIKE (p_doc_number)
794: AND exists ( select 'x'