Shulou(Shulou.com)06/01 Report--

Sometimes you need to display the summary number of a field, and when you add, delete, or modify records, the value of the summary item should be changed accordingly. If you directly use the SUM attribute function in Form, you have to deal with complex operations such as clearing. Oracle provides APP_CALCULATE.RUNNING_TOTAL to implement this requirement, which is encapsulated in APPCORE.PLL.

For Example:

The ITEM to be summarized is: LINES.QUANTITY, and the BLOCK to display the summary result is HEADERS

Create a summary result ITEM to be displayed in 1.HEADERS, such as QTY_SUM

Create two non-database items in 2.HEADERS, named QTY_SUM_RTOT_OLD and QTY_SUM_RTOT_DB (Numbertype)

Create two non-database items in 3.LINES, named QUANTITY_RTOT_OLD and QUANTITY_RTOT_DB (Numbertype)

4. Set up stored procedures to wrap APP_CALCULATE.RUNNING_TOTAL

Example:

PROCEDURE RUNNING_TOTAL_QUANTITY (EVENT VARCHAR2) IS

BEGIN

APP_CALCULATE.RUNNING_TOTAL (EVENT, 'LINES.QUANTITY',' HEADERS.QTY_SUM')

END

-- call RUNNING_TOTAL_QUANTITY in TRIGGER for calculation processing

Write RUNNING_TOTAL_QUANTITY ('WHEN-VALIDATE-ITEM') in the WHEN-VALIDATE-ITEM Trigger of 5.:LINES.QUANTITY

If the field you want to summarize comes from the operation of multiple fields, you need to write the WHEN-VALIDATE-ITEM of each field to recalculate the value assigned to the field to be summarized.

Grab the initial summary value from the database in the POST-QUERY Trigger of 6.:HEADERS, for example:

Declare

V_SUM NUMBER

BEGIN

SELECT NVL (SUM (QUANTITY), 0)

INTO V_SUM

FROM LINES

WHERE HEADER_ID =: HEADERS.HEADER_ID

: HEADERS.QTY_SUM: = V_SUM

: HEADERS.QTY_SUM_RTOT_DB: = Vendor SUMbomachi-Note ①

END

Write the corresponding code in the following TRIGGER of 7.:LINES:

-- KEY-DELREC

RUNNING_TOTAL_QUANTITY ('KEY-DELREC')

DELETE_RECORD

RUNNING_TOTAL_QUANTITY ('UNDELETE');-- Note ③

-- KEY-DUPREC

RUNNING_TOTAL_QUANTITY ('KEY-DUPREC')

DUPLICATE_RECORD

-- KEY-CLRREC

RUNNING_TOTAL_QUANTITY ('KEY-CLRREC')

CLEAR_RECORD;-- (online example: APP_FOLDER.EVENT ('KEY-CLRREC');)

-- POST-QUERY

RUNNING_TOTAL_QUANTITY ('POST-QUERY')

-- WHEN-CLEAR-BLOCK

RUNNING_TOTAL_QUANTITY ('WHEN-CLEAR-BLOCK');-- Note ②

Note: online examples are also written in post_insert,post_update,pre_record, I have not written, test OK, do not know what loopholes there will be, not tested for the time being.

In addition, in the link above, there will be a problem with the author's example. As the comments on the link article said, the author needs to add the ① code, that is, the WHEN-CLEAR-BLOCK code needs to exist at the same time as the note ① (the query finds that the PO screen in the EBS also does the same).

At first, I didn't write the code for ② and ①, but after testing, I found that the lack of these two parts would lead to problems, so let's give an example:

Thank you very much.

For example, there is only one record for the Line entry, with a value of 100

1) after normal query, the summary bar shows 100

2) change the record value to 99 and the summary column to 99

3) press F11, you will be prompted "whether to save the record" and select "No"

-- the summary column value is 99 (normally the summary bar displays 100).

4) execute Ctrl+F11

-- at this point, the summary column value is still 99 (normally, the summary bar displays 100, (and the big problem is that the line value is 100, the summary is 99).

Thank you very much.

So: note ② + Note ① must be written.

Note ③: the Delete Allowed of Block is set to No, and the Delete button is lit for various reasons. In this case, you will be prompted when you click the Delete button

FRM-41049:You cannot delete this record. But the total remittance will be reduced, it will be counted all the time, it will be reduced all the time.. Up to negative NNNN...

So add ③ to avoid the mistake of sinking the total amount in this case.

As for whether and how to write in the other Trigger involved in APP_CALCULATE.RUNNING_TOTAL, it has not been tested, and so far, the application is normal.

(there is a problem, that is, "add a record, save, and then click the clear-record button", the number will be reduced, not solved, the rest of the Trigger should also need to be added, but there do not seem to be a few Trigger under the Block, but there are other global variables generated by Trigger calls that have not been tested too much for the time being. Busy . . )

In addition, if you want to compare the summary results with other values, such as raise error if you exceed a certain value, pay attention to the order of running_total and comparison size.

If you compare in when-validate-item, then running_total first and then compare in this trigger, otherwise.

- -

The original APP_CALCULATE.RUNNING_TOTAL code is attached:

PACKAGE BODY app_calculate IS

PROCEDURE running_total (event VARCHAR2

Source_field VARCHAR2

Total_field VARCHAR2) IS

Last_Val_Field VARCHAR2 (61): = Source_Field | |'_ RTOT_OLD'

DB_Source_Field VARCHAR2 (61): = Source_Field | |'_ RTOT_DB'

DB_Total_Field VARCHAR2 (61): = Total_Field | |'_ RTOT_DB'

Last_Value NUMBER

Source_Value NUMBER

DB_Source_Value NUMBER

Total_Value NUMBER: = NVL (name_in (Total_Field), 0)

DB_Total_Value NUMBER: = NVL (name_in (DB_Total_Field), 0)

New_Total NUMBER

Form_Id FORMMODULE

BEGIN

COPY ('Entering app_calculate.running_total. Event is' | | event |'.'

'global.frd_debug')

IF (event 'WHEN-CLEAR-BLOCK') THEN

Last_Value: = NVL (name_in (Last_Val_Field), 0)

Source_Value: = NVL (name_in (Source_Field), 0)

DB_Source_Value: = NVL (name_in (DB_Source_Field), 0)

END IF

IF (event = 'POST-QUERY') THEN

Copy (to_char (Source_Value), DB_Source_Field)

Copy (to_char (Source_Value), Last_Val_Field)

ELSIF (event = 'WHEN-CLEAR-BLOCK') THEN

IF (Total_Value DB_Total_Value) THEN

Copy (to_char (DB_Total_Value), Total_Field)

END IF

ELSIF (event = 'WHEN-VALIDATE-ITEM') THEN

IF (Source_Value Last_Value) THEN

New_Total: = Total_Value-Last_Value + Source_Value

Copy (to_char (New_Total), Total_Field)

Copy (to_char (Source_Value), Last_Val_Field)

END IF

ELSIF (event = 'KEY-DELREC') THEN

IF (Last_Value 0) THEN

New_Total: = Total_Value-Last_Value

Copy (to_char (New_Total), Total_Field)

END IF

Copy (to_char (Source_Value), Last_Val_Field)

ELSIF (event IN ('POST-INSERT',' POST-UPDATE')) THEN

IF (Source_Value DB_Source_Value) THEN

Copy (to_char (DB_Source_Value), Last_Val_Field)

Copy (to_char (Source_Value), DB_Source_Field)

END IF

ELSIF (event = 'PRE-RECORD') THEN

IF (Source_Value Last_Value) THEN

Form_Id: = Find_Form (Get_Application_Property (Current_Form_Name))

IF (Name_In ('GLOBAL.RTOT_' | |

Substr (source_field

one,

Least (instr (source_field,'.')-1,15) | |

To_char (Form_id.id)) ='N') THEN

Copy (to_char (Last_Value), DB_Source_Field)

END IF

Copy (to_char (Source_Value), Last_Val_Field)

END IF

ELSIF (event = 'PRE-COMMIT') THEN

Form_Id: = Find_Form (Get_Application_Property (Current_Form_Name))

Copy ('N')

'GLOBAL.RTOT_' | |

Substr (source_field, 1, least (instr (source_field,'.')-1,15)) | |

To_char (Form_id.id))

ELSIF (event = 'POST-FORMS-COMMIT') THEN

Form_Id: = Find_Form (Get_Application_Property (Current_Form_Name))

Copy ('Y')

'GLOBAL.RTOT_' | |

Substr (source_field, 1, least (instr (source_field,'.')-1,15)) | |

To_char (Form_id.id))

IF (Total_Value DB_Total_Value) THEN

Copy (to_char (Total_Value), DB_Total_Field)

END IF

ELSIF (event = 'UNDELETE') THEN

New_Total: = Total_Value + Source_Value

Copy (to_char (New_Total), Total_Field)

Copy (to_char (Source_Value), Last_Val_Field)

ELSIF (event = 'KEY-DUPREC') THEN

New_Total: = Total_Value + Source_Value

Copy (to_char (New_Total), Total_Field)

Copy (to_char (Source_Value), Last_Val_Field)

Copy (NULL, DB_Source_Field)

ELSIF (event = 'KEY-CLRREC') THEN

IF (Last_Value DB_Source_Value) THEN

New_Total: = Total_Value-Last_Value + DB_Source_Value

Copy (to_char (New_Total), Total_Field)

END IF

ELSE

Message ('Invalid event' | | event | |

'in app_calulate.running_total')

END IF

COPY ('Completed app_calculate.running_total. Event is' | | event |'.'

'global.frd_debug')

END running_total

END app_calculate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.