Adding a custom view to an extended query for a QRP form

[Janis Allard]

Hi,

I need to update a QRP form to include a quantity for each warehouse / location ID combination in our Part_Location table. In order to gather this data, a custom view must be added to the extended query (SQLQUERY) used to provide customized data for the QRP form.

We use Oracle Visual Manufacturing 6.5.4 and Gupta Report Builder 2005 version 4.0.0, and the form involved is VMTRVRP2.

Are there any VM / Gupta users out there who can tell me how / where my view should be created in order to be referenced in the extended query? Also, can I use an array to hold the view results?

Thanks,
Janis

[cachandl]

Hi Janice. I don't know if you're using Oracle but, if so, here's what you need to do: Create the view, create a public synonym on the view with the same name (this is because the view will be in the SYSADM schema and the user won't see it otherwise) then, grant select on the view to either public or the users that you want to have access to it. That should do it.

[Janis Allard]

Thanks! Yes, I am using Oracle. After posting my question, I created the view in SYSADM (without using a synonym), then created an extended query referencing the view. The QRP recognized the view, but only displayed data from one of the resulting rows that matched the part_id. I'm not sure what I need to do differently to get the QRP to display multiple rows from the view matching the part_id.

Here's my view script and extended query script:

Code: Select all

VIEW:
CREATE OR REPLACE VIEW SYSADM.L3_PART_LOCATION_QTY_V(
   PL_PART_ID,
   PL_WAREHOUSE_ID,
   PL_LOCATION_ID,
   PL_QTY,
   PL_WHSE_LOC_QTY
)
AS
   SELECT 
    DISTINCT
       PL.PART_ID, PL.WAREHOUSE_ID, PL.LOCATION_ID, PL.QTY,
       PL.PART_ID || '//' ||    --INCLUDE FOR TESTING ONLY 
       PL.WAREHOUSE_ID || '/' || PL.LOCATION_ID || '/' || PL.QTY || ' '
FROM PART_LOCATION PL
     INNER JOIN REQUIREMENT RQ
        ON RQ.PART_ID = PL.PART_ID
       AND ((RQ.WAREHOUSE_ID = PL.WAREHOUSE_ID) OR
            (PL.WAREHOUSE_ID ='COMMON' AND PL.LOCATION_ID = 'AUTOISSUE')
           )
WHERE   PL.QTY > 0
WITH READ ONLY;
GRANT SELECT ON SYSADM.L3_PART_LOCATION_QTY_V TO PUBLIC;
COMMIT;

EXTENDED QUERY:
'SELECT PLVW.PL_WHSE_LOC_QTY FROM L3_PART_LOCATION_QTY_V PLVW, REQUIREMENT RQ WHERE PLVW.PL_PART_ID = RQ.PART_ID AND PLVW.PL_PART_ID = :RQ_PART_ID AND PLVW.PL_QTY > 0 INTO :PLVW_WHSE_LOC_QTY'

QRP FORM:
I added a cell to the QRP form to display PLVW_WHSE_LOC_QTY.

[Jeff Luther]

Janis: FYI, What I just did is changed your msg. so the SQL code's formatting/indenting is preserved. When you simply copy/pasted this forum just left-justifies everything, so more difficult to read.

How did I do this?
** after pasting, while still in edit mode and you can see the indenting you want to preserve, select all that text

** now click the "Code" button just above this edit field. What that does is surround the select text with

Code: Select all

 and the forum engine now keeps indenting.

This is useful for any text you add here where you want to preserve indenting: TD source code, your SQL code, etc.

And if you just want something to...
[quote]stand out[/quote]
like I just did with "stand out", I selected that and clicked the "Quote" button above. It doesn't preserve indenting but it does put text right out there.