Query For Vendor Payment Forecast

Query For Vendor Payment Forecast, AskHareesh.blogspot.com

Query For Vendor Payment Forecast

SELECT   *
FROM     (SELECT pov.vendor_type_lookup_code vendor_type_code
                ,vtype.meaning vendor_type
                ,pov.vendor_name vendor_name
                ,msi.segment1 item_number
                ,poh.segment1 po_number
                , poh.segment1
                  || DECODE (por.release_num
                            ,NULL, NULL
                            , '-' || por.release_num) || ',' || pol.line_num
                  || DECODE (pll.shipment_num
                            ,NULL, NULL
                            , '-' || pll.shipment_num) number_release_line
                ,pll.quantity line_qty
                ,NVL (pll.price_override, pol.unit_price) unit_price
                , pll.quantity * NVL (pll.price_override, pol.unit_price)
                                                                       line_amt
              --  ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YY')
               ----                                                             eta
                                            ,DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YY')) eta
                ,TO_CHAR (DECODE (pov.vendor_type_lookup_code
                                 ,'DOMESTIC VENDORS', TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY')- 7
                                 ,'INVENTORY', TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 21
                                 ,TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 7)
                         ,'DD-MON-YY') po_ship_date
                ,DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') in_transit
                ,NVL (poh.fob_lookup_code, povs.fob_lookup_code) fob_code
                ,terms.NAME payment_terms
                ,DECODE
                    (terms.term_id
                    ,NULL, TO_DATE(TO_CHAR (DECODE (pov.vendor_type_lookup_code
                                 ,'DOMESTIC VENDORS', TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY')- 7
                                 ,'INVENTORY', TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 21
                                 ,TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 7)
                         ,'DD-MON-YYYY') ,'DD-MON-YYYY')
                    ,ap_create_pay_scheds_pkg.calc_due_date
                                          (TO_DATE(TO_CHAR (DECODE (pov.vendor_type_lookup_code
                                 ,'DOMESTIC VENDORS', TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY')- 7
                                 ,'INVENTORY', TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 21
                                 ,TO_DATE(DECODE(DECODE
                    ((SELECT 1
                      FROM   rcv_shipment_lines rsl
                            ,rcv_shipment_headers rsh
                      WHERE  1 = 1
                      AND    rsl.po_header_id = poh.po_header_id
                      AND    rsl.po_line_id = pol.po_line_id
                      AND    rsl.source_document_code = 'PO'
                      AND    rsl.po_line_location_id = pll.line_location_id
                      AND    rsh.asn_type IN ('ASN', 'ASBN')
                      AND    rsl.shipment_line_status_code IN
                                ('EXPECTED'
                                ,'PARTIALLY RECEIVED'
                                ,'FULLY RECEIVED')
                      AND    rsh.shipment_header_id = rsl.shipment_header_id
                      AND    (NOT EXISTS (
                                 SELECT ' '
                                 FROM   rcv_transactions_interface rti
                                 WHERE  rti.shipment_header_id =
                                                          rsl.shipment_header_id
                                 AND    rti.shipment_line_id =
                                                            rsl.shipment_line_id))
                      AND    NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
                      AND    ROWNUM <= 1)
                    ,NULL, 'N'
                    ,'Y') ,'Y',
                   (select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
              rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
                   ) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 7)
                         ,'DD-MON-YYYY') ,'DD-MON-YYYY')
                                          ,terms.term_id
                                          ,NULL
                                          ,1
                                          ,NULL)) due_date
                ,pll.ship_to_location_id location_id
                ,pol.unit_meas_lookup_code unit
                ,poh.po_header_id head_id
                ,pll.line_location_id loc_id
                ,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 charge_account
          FROM   po_vendors pov
                ,po_vendor_sites_all povs
                ,po_releases_all por
                ,po_line_types plt
                ,mtl_categories mca
                ,po_line_locations_all pll
                ,po_lines_all pol
                ,po_headers_all poh
                ,mtl_system_items_kfv msi
                ,po_distributions_all pod
                ,fnd_lookup_values_vl vtype
                ,ap_terms terms
                ,hr_operating_units hou
                ,gl_code_combinations gcc
          WHERE  poh.po_header_id = pol.po_header_id
          AND    poh.terms_id = terms.term_id(+)
          AND    povs.vendor_id = poh.vendor_id
          AND    povs.vendor_site_id = poh.vendor_site_id
          AND    vtype.lookup_type = 'VENDOR TYPE'
          AND    vtype.lookup_code = pov.vendor_type_lookup_code
          AND    ((:P_CLOSED_CODE = 'OPEN' AND DECODE (poh.type_lookup_code
                        ,'STANDARD', NVL (poh.closed_code, 'OPEN')
                        ,'PLANNED', NVL (poh.closed_code, 'OPEN')
                        ,'BLANKET', NVL (por.closed_code, 'OPEN')) = 'OPEN'
                        )
                 OR
                  /*(:P_CLOSED_CODE = 'CLOSED' AND DECODE (poh.type_lookup_code
                        ,'STANDARD', NVL (poh.closed_code, 'OPEN')
                        ,'PLANNED', NVL (poh.closed_code, 'OPEN')
                        ,'BLANKET', NVL (por.closed_code, 'OPEN')) IN ('CLOSED', 'FINALLY CLOSED')
                        ) */
                   (:P_CLOSED_CODE = 'CLOSED' AND 1=1
                        )
                 )
          AND    ((:P_CLOSED_CODE = 'OPEN' AND DECODE (poh.type_lookup_code
                        ,'STANDARD', NVL (pol.closed_code, 'OPEN')
                        ,'PLANNED', NVL (pol.closed_code, 'OPEN')
                        ,'BLANKET', 'OPEN') = 'OPEN'
                   )
                   OR (:P_CLOSED_CODE = 'CLOSED' AND DECODE (poh.type_lookup_code
                        ,'STANDARD', NVL (pol.closed_code, 'OPEN')
                        ,'PLANNED', NVL (pol.closed_code, 'OPEN')
                        ,'BLANKET', 'OPEN') IN ('CLOSED', 'FINALLY CLOSED')
                   )
                   )
          AND    NVL (poh.cancel_flag, 'N') = 'N'
          AND    NVL (pol.cancel_flag, 'N') = 'N'
          AND    NVL (por.cancel_flag, 'N') = 'N'
          AND    NVL (pll.cancel_flag, 'N') = 'N'
          AND    poh.vendor_id = pov.vendor_id
          AND    pol.po_line_id = pll.po_line_id
          AND    pol.line_type_id = plt.line_type_id
          AND    pol.item_id = msi.inventory_item_id(+)
          AND    pll.ship_to_organization_id = msi.organization_id
          AND    pol.category_id = mca.category_id
          AND    pll.po_release_id = por.po_release_id(+)
          AND    pll.shipment_type IN ('STANDARD', 'SCHEDULED', 'BLANKET')
          AND    NVL (pll.approved_flag, 'N') = 'Y'
          AND    NVL (pll.cancel_flag, 'N') = 'N'
          AND    poh.type_lookup_code IN ('BLANKET', 'STANDARD', 'PLANNED')
          AND    ((:P_CLOSED_CODE = 'OPEN' AND NVL (pll.closed_code, 'OPEN') IN ('OPEN','CLOSED FOR INVOICE')
          AND    NVL (pll.quantity, 0) - NVL (pll.quantity_cancelled, 0) >
                                                  NVL (pll.quantity_received, 0)
                  )
                  OR :P_CLOSED_CODE = 'CLOSED' AND 1=1 )
          AND    pll.line_location_id = pod.line_location_id(+)
          AND    hou.organization_id = poh.org_id
          AND  pod.code_combination_id = gcc.code_combination_id 
--          AND     poh.segment1='12345'         
         )
WHERE    1 = 1
AND      NVL (due_date, SYSDATE) BETWEEN NVL (TO_DATE (:p_date_from
                                                      ,'yyyy/mm/dd hh24:mi:ss')
                                             , NVL (due_date, SYSDATE) - 1)
                                     AND NVL (TO_DATE (:p_date_to
                                                      ,'yyyy/mm/dd hh24:mi:ss')
                                             , NVL (due_date, SYSDATE) + 1)
ORDER BY number_release_line;

*/