5 Replies Latest reply on May 12, 2008 8:54 AM by 620970

    Need SQL query for PO that do NOT have 3-way match.

    620970
      The question is -- have any PO"s been generated with other then 3-way match selected - which means they overrode the set-ups in purchasing , to see if there are an PO that do NOT have 3-way match.

      Thanks
      Mohan.
        • 1. Re: Need SQL query for PO that do NOT have 3-way match.
          620970
          Can any body give me the SQL qurey to list the PO's have been generated with other then 3-way match selected .
          • 2. Re: Need SQL query for PO that do NOT have 3-way match.
            456359
            select * from po_headers_all ph
            where not exists ( select 1 from po_line_locations_all pll
            where ph.po_header_id = pll.po_header_id
            and receipt_required_flag = 'Y'
            and inspection_required_flag = 'N');

            Thanks
            • 3. Re: Need SQL query for PO that do NOT have 3-way match.
              620970
              1)Generally when ever we will do Supplier setup we will select in the Receiving tab Match approval level we will select either 3-way ,2-way,4-way or we will keep that as blank.
              for that we do not have any direct column in the back end ,so we will use the following conditions.

              '2WAY' inspection_required_flag = 'N'
              receipt_required_flag = 'N'

              '3WAY' inspection_required_flag = 'N'
              receipt_required_flag = 'Y'

              '4WAY' inspection_required_flag = 'Y'
              receipt_required_flag = 'Y'

              2)When ever we will create a PO in the shipments level what ever the Match approval level,
              we defined in the supplier will automatically reflects in the shipments-More tab.

              3)Here i wants to know all the PO's whihc are having Match approval level differences between supplier definiton and PO's match approval level.

              (i.e If i will select a supplier for a PO , automatically we will get one Match approvallevel from supplier setup .if i will change that match approval level manually in the PO shipments -More tab i wants to know all that PO by using a SQL query.

              4)But for some PO's i am getting receipt_required_flag and inspection_required_flag as null.But in the Supplier setup it may be one of the Matching approval.

              Thanks
              Mohan.
              • 4. Re: Need SQL query for PO that do NOT have 3-way match.
                620970
                HI Saurabh,
                I used the below query , but for some PO's
                eiher of the coulmn is showing as blank (ex: receipt_required_flag is 'Y' and
                inspection_required_flag is 'N')
                do you have any idea why for some PO's either of the column is blank.

                select * from po_headers_all ph
                where not exists ( select 1 from po_line_locations_all pll
                where ph.po_header_id = pll.po_header_id
                and receipt_required_flag = 'Y'
                and inspection_required_flag = 'N');


                Thanks
                Mohan.
                • 5. Re: Need SQL query for PO that do NOT have 3-way match.
                  620970
                  This is the query i wrote , could please check and let me know if this correct.

                  Thanks
                  Mohan



                  Inspection required flag Receipt required flag

                  N N 2-way
                  NULL NULL 2-way
                  NULL Y 3-way
                  N               Y 3-way
                  Y Y 4-way

                  select ph.segment1"PO NUMBER",
                            pl.line_num,
                            pll.shipment_num,
                  ph.creation_date,
                            ph.org_id,
                  decode(pll.INSPECTION_REQUIRED_FLAG,'N',decode(pll.RECEIPT_REQUIRED_FLAG,'N','2 WAY','Y','3 WAY',NULL),
                  'Y',decode(pll.RECEIPT_REQUIRED_FLAG,'Y','4 WAY',NULL),
                                 NULL,decode(pll.RECEIPT_REQUIRED_FLAG,NULL,'2 WAY','Y','3 WAY',NULL),               
                                 NULL) MATCHING_PO
                  from po_headers_all ph,
                            po_lines_all pl,
                  po_line_locations_all pll          
                  where ph.po_header_id=pll.po_header_id
                  and      ph.org_id=2
                  and ph.po_header_id = pl.po_header_id
                  and      pl.po_line_id = pll.po_line_id
                  and ((pll.inspection_required_flag!='N' or pll.inspection_required_flag is not null) and pll.receipt_required_flag!='Y')
                  and (pll.po_release_id is null)
                  order by creation_date desc;