| Sub_code generation. [message #80679] |
Wed, 30 October 2002 23:54  |
K.N. Ramesh
Messages: 15
Registered: September 2002
|
Junior Member |
|
|
Dear Sirs,
I have been given the following tables:
MED_STOCK
Med_Cd Varchar2(6) Not Null,
Med_Sub_Cd Number(3) Not Null,-Internal numberng
Batch_No Varchar2(10) Not Null,-Given by Supplier
Unit_rate Number(8,2) Not Null,
Act_Unit_Price Number(8,2)- (Unit_Price + Tax - Discount) used for issue
Qty Number(5) - Quantity (Current stock = quantity - out_stock)
Expiry_Dt Date,
Updated_Dt Date,
MED_RECPT_MAS
Receipt_No Varchar2(8) Not Null,- Internal Generation
Receipt_Dt Date Not Null,- System Date
Supp_Cd Varchar2(5) Not Null
Bill_No Varchar2(8) Not Null
Bill_Dt Date,
PO_No Varchar2(8),
PO_Dt Date,
Discount_Per Number(4,2)
Discount Number(8,2)
Gross_Amt Number(10,2)
MED_RECPT_DETL
Receipt_No Varchar2(8) Not Null,-Internal Generation
Receipt_Dt Date Not Null,- System date
Med_Cd Varchar2(6) Not Null,
Med_Sub_Cd Number(3) Not Null - Generated when new stocks are
updated.
Purc_Qty Number(5), - Different from qty in Med_Stock.
Pur_val Number(9),
Tax_Per Number(4,2),
Tax Number(8,2)
My questions are very basic which may please be answered.
Q-1) When purchasing material, i need to capture med_cd, purchased quantity, unit rate, tax percent overall discount, batch number and expiry date.
The above details are split into three tables.
How should i do this. Should i create three blocks? or a procedure will do these jobs?
Q-2) How subcode is generated when new batch product is included.
I am not asked the entire system. But understand the process, i need to know above basic answers.
Anybody would help me!
Regards,
K.N.Ramesh
|
|
|
|
| Re: Sub_code generation. [message #80684 is a reply to message #80679] |
Thu, 31 October 2002 16:56  |
Julie
Messages: 98
Registered: February 2002
|
Member |
|
|
|
2 blocks. Receipt master and detail. Sub code is populated on the pre-insert trigger. On your detail block, create a lov to the stock table and populate unit rate and expiry date when the user chooses the med code and med sub code.
|
|
|
|
| Re: Sub_code generation. [message #80689 is a reply to message #80679] |
Fri, 01 November 2002 02:26 |
K.N. Ramesh
Messages: 15
Registered: September 2002
|
Junior Member |
|
|
Julie Esq,
Could you please explain detail block LOV. Should I include non-database item in the detail block showing unit rate and expiry_date. I need to insert batch_no also, if it does not exist in stock table.
Await to your answer.
K.N.Ramesh
|
|
|
|
| Re: Sub_code generation. [message #80691 is a reply to message #80689] |
Fri, 01 November 2002 07:25 |
Julie
Messages: 98
Registered: February 2002
|
Member |
|
|
Yes. Make the lov not validated, then in the key-commit trigger write code to see if it exists and if not add it.
Like select count(*) into cnt from table...
if cnt < 1 then
insert into table ...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
