Hi,
I'm using Apex 20.0
I am developing a purchase module where i have a purchase order and Goods Receipt note.(Master Detail Pages)
The process that is followed is, first i raise a purchase order against a vendor / supplier. After that when goods are received i select the supplier and purchase orders raised against that supplier is displayed (only pending purchase orders where order quantity has not been fulfilled through supplies)
I can have multiple supplies against one purchase order for a single item.
For example: I have placed an order for Item A for 10 nos. I receive 5 nos on day-1 and then receive 5 nos on day-2.
Once i receive the first supply (entered in the goods receipt note) on day-1, I receive 5 nos (enter it using the Goods Receipt note) against the order of 10 nos. After saving the transaction the status of the line item in the order still remains pending.
On Day-2 when i receive the balance 5 nos I enter using the Goods receipts note and save it. The status of the line item in the purchase order changes to closed from pending.
The issue i have is when i open the first or second saved transaction in view or edit mode after the entire supply is done the Display Value changes to Return Value in the Goods receipt note. As long as the status of the line item is pending the Display Value is shown in the Goods Receipt Note,
the moment the status of the line item changes to Closed in the purchase order it changes to the Return value from the Display Value.
I have detailed out the code on what i have done. What am I doing wrong here? Any help would be highly appreciated.
Here is the code for Product Name which is of type POPUP LOV(Products which are pending against the Purchase Order Number)
SELECT PM.PRODUCT_NAME, PM.ID FROM PH_PRODUCT_MASTER PM, PH_PURCHASE_ORDER_DETAILS POD, PH_PURCHASE_ORDER PO WHERE PO.PO_NO= POD.PO_NO AND PM.ID=POD.PRODUCT_NAME
AND PO.APPROVAL_STATUS='Approved' AND POD.PO_STATUS='Pending' AND PO.PO_NO=:P314_PO_NO
Please help to resolve this...
Vinish Kapoor
The problem is in the condition POD.PO_STATUS = 'Pending'. Because when it is not pending, then that item will not be in the LOV.
Or I think you should include both the status in your condition. For example:
POD.PO_STATUS in ('Pending', 'Closed')
VARSHA
Yes, u are right
But i can't include both status because there it should show only pending Products
Vinish Kapoor
Ok, then select the LOV source as Function Returning SQL query. Using this, you would be able to return different queries according to the status. Below is an example:
VARSHA
Any suggestion please...
VARSHA
Thanks for the reply Vinish
But here i'm selecting "Order Number" in the master form not the STATUS, based on "Order Number" Pending Products should show in Interactive Grid column
Farooq Ahmad
Hi See You Have PO_detail Table and GRN_DETAIL
now you will have PO_NO in GRN_Detail so use not in query in where clause and get by pl sql curser
VARSHA
Thanks for the reply
Can you please show it by example