FORMS: QTY-ON_HAND & How to display items of non-base tables

From: Cecil D'Souza <cecil_at_total.net>
Date: 1997/10/12
Message-ID: <01bcd77d$87195740$1f9dcdcd_at_default>#1/1

Hi guys

I really need help to sort this out. I'd appreciate any help or ideas. The screen is shown below.
Invoice_hdr -BLOCK on Screen- SINGLE RECORD BLOCK

---

Invoice #     Customer#   Date       AmountPaid       Amount Due
12345         00001         sysdate                                  370.00

Invoice_Detail- ON SCREEN- MULTI RECORD BLOCK

---

PRDcode     PRDDESC     QTY-ORDERED   PRICE   $VALUE
001            sweat shirts             10                 10        100
002            sweat pants             10                 20        200
003            T shirts                     5                 10         50
001            sweat shirts               2                10         20

CONTROL BLOCK - QUERY, SAVE , CANCEL, EXIT (BUTTONS) I do have the relationship between the Invoice header and Invoice detail record created based on the
INVOICE_NUMBER column. That is fine.

What I am doing when executing the QUERY is this. 1) Display LOV of Invoice numbers with the Customer number

2) SELECT from LOV

3) The Invoice header record should display along with the customer name that is fetched from the customer master. ( This does not happen)

4) The detail records (products) should display with the product code and QTY's ordered.

What is actually happeneing.
1) The customer name does not display.
(CUST NAME is a display item in the INV_HDR BLOCK.)

2) An alert box appears asking if I want to save changes maybe to the invoice header (need to stop that) YES NO CANCEL

3) The product details like product desc and price do not display but the product code and qty ordered display (which are details of the invoice detail records)

What I also NEED to know is how
1) to display the product desc and price from the product master, do I have to create a POST_QUERY trigger at the INV_DETAIL Block?

2) I need to keep a track of the product QTY_ON_HAND after the product is ordered in case the same product is ordered again on the same invoice. Eg. prod code 001 is ordered. Ordered qty checked against QTY_ON_HAND from Product master. IF QOH is greater, then minused from QOH. Record(s) not COMmited by user till last. So if the same product is ordered again, how do I know the QOH of that product.?

3) Do you have a better method to accomplish what I am doing?

---

I have this piece of code in my POST_QUERY trigger at the INV-HDR BLOCK level.

 CURSOR cust_cur IS

          SELECT cust_name 
            FROM CUSTMAST
           WHERE cust_code = :inv_hdr.invoice_cust_code;

      CURSOR dtl_cur is 
             SELECT invoice_number, invoice_prod_code, invoice_qty 
             FROM   INVOICE_DETAIL d
             WHERE  d.invoice_number = :INV_HDR.invoice_number
              AND   d.invoice_prod_code = (
                    SELECT prd_desc, prd_sell_price
                      FROM PRODMAST p
                     WHERE d.invoice_prod_code = p.prd_code);
                     
     invdtl_rec dtl_cur%ROWTYPE;

BEGIN
     OPEN cust_cur;
     FETCH cust_cur INTO :inv_hdr.custname; 
     CLOSE cust_cur;
   
     OPEN dtl_cur;
     LOOP
        FETCH dtl_cur into invdtl_rec;
        EXIT when dtl_cur%NOTFOUND;


        :inv_dtl.invoice_prod_code := invdtl_rec.invoice_prod_code;

        :inv_dtl.prd_desc    := invdtl_rec.prd_desc;

        :inv_dtl.prd_price   := invdtl_rec.prd_sell_price;


        :inv_dtl.invoice_qty := invdtl_rec.invoice_qty;


     END LOOP;
     CLOSE dtl_cur;
    

Received on Sun Oct 12 1997 - 00:00:00 CEST