Folder Function and Create a prompt block with the items similar to the datablock in Oracle Forms

Folder Function in Oracle Forms

Folder function in forms

1. Copy TEMPLATE.fmb and rename it.
2. Open the APPSTAND.fmb and copy the STANDARD_FOLDER object group to the custom form

3. Attach library APPFLDR.pll

4. Create data block and subclass the items as text items

5. Create a prompt block with the items similar to the datablock.

6. Subclass the items of the prompt as FOLDER_PROMPT_MULTIROW

7. Create a content Canvas XX_FOLDER and a Stacked canvas XX_FOLDER_STA.

8. Arrange the items and the corresponding prompt items in the in the stacked and content canvas

9. Create a window and associate the window and its primary canvas(Content)

10. Add the following objects in the XXPO_PWB_LINES_PROMPT Prompt block and assign it to the respective Canvas

Item Name	Item Type	Subclass	Canvas
FOLDER_OPEN	Push Button	Dynamic Title	XX_FOLDER
FOLDER_TITLE	Display Item	Dynamic Title	XX_FOLDER
FOLDER_DUMMY	Text Item	Folder Dummy	Toolbar
ORDER_BY1	Push Button		XX_FOLDER
ORDER_BY2	Push Button		XX_FOLDER
ORDER_BY3	Push Button		XX_FOLDER
Folder_Switcher	Text Item	Switcher	XX_FOLDER

11. Add the below code in FOLDER_ACTION trigger

app_folder.event(:global.folder_action);

12. WHEN-NEW_BLOCK-INSTANCE

app_folder.event('WHEN-NEW-BLOCK-INSTANCE');

13. Create a parameter XX_FLDR_COUNT of type char

KEY-PREV-ITEM

IF (:parameter.xx_fldr_count = 1)

THEN

PREVIOUS_ITEM;

ELSE

app_folder.event ('KEY-PREV-ITEM');

END IF;

14. KEY-NEXT-ITEM

IF (:parameter.xx_fldr_count = 1)

THEN

NEXT_ITEM;

ELSE

app_folder.event ('KEY-NEXT-ITEM');

END IF;

15. PRE-BLOCK

app_folder.event('PRE-BLOCK');

16. POST-BLOCK

app_folder.event('POST-BLOCK');

17. PRE-QUERY

app_folder.event('PRE-QUERY');

18. KEY-EXEQRY

app_folder.event('KEY-EXEQRY');

19. POST-QUERY

app_folder.event('POST-QUERY');

20. Compile the form

Set up of Revaluation accounts

SELECT
    GL.NAME "Ledger Name",
    GL.DESCRIPTION "Ledger Description",
    GLR.NAME "Revaluation Name",
    GLR.DESCRIPTION "Revaluation Description",
    (CASE GLR.AUTOMATIC_POST_FLAG
     WHEN 'Y' THEN 'Enabled'
     WHEN 'N' THEN 'Not Enabled' END) "Auto Post Revaluation Flag",
    (CASE GLR.SECURITY_FLAG
     WHEN 'Y' THEN 'Enabled'
     WHEN 'N' THEN 'Not Enabled' END) "Enable Security Flag",
    (CASE GLR.FROM_CURRENCY_OPTION_CODE
     WHEN 'A' THEN 'All Currencies'
     WHEN 'S' THEN 'Single Currency' END) "Currency Options",
    GLR.FROM_CURRENCY_CODE "From Currency Code",
    (CASE GLR.CONVERSION_RATE_OPTION_CODE
     WHEN 'D' THEN 'Daily Rates'
     WHEN 'U' THEN 'One-Time' END) "Rate Options",
    (CASE GLR.CONVERSION_RATE_TYPE
     WHEN '1000' THEN 'Month-end'
     ELSE GLR.CONVERSION_RATE_TYPE END) "Conversion Rate Type",
    GLR.REVALUATION_RATE "Revaluation Rate",
    (GLR.GAIN_SEGMENT1||'.'||GLR.GAIN_SEGMENT2||'.'||GLR.GAIN_SEGMENT3||'.'||GLR.GAIN_SEGMENT4||'.'||
     GLR.GAIN_SEGMENT5||'.'||GLR.GAIN_SEGMENT6||'.'||GLR.GAIN_SEGMENT7||'.'||GLR.GAIN_SEGMENT8) "Unrealised Gain Account",
    (GLR.LOSS_SEGMENT1||'.'||GLR.LOSS_SEGMENT2||'.'||GLR.LOSS_SEGMENT3||'.'||GLR.LOSS_SEGMENT4||'.'||
     GLR.LOSS_SEGMENT5||'.'||GLR.LOSS_SEGMENT6||'.'||GLR.LOSS_SEGMENT7||'.'||GLR.LOSS_SEGMENT8) "Unrealised Loss Account",
    greatest(gl.last_update_date, glr.last_update_date) "Last Update Date",
    (select name from v$database) "Database Name",
    sysdate "Script Run Date"
FROM
    APPS.GL_REVALUATIONS GLR,
    APPS.GL_LEDGERS GL
WHERE gl.chart_of_accounts_id = glr.chart_of_accounts_id
and  gl.name in('XXX Primary Ledger')ORDER BY GL.NAME

Reconciled and UnReconciled breakdown of check payments and bank statements

select h.name, c.check_id, c.check_number, c.check_date, c.currency_code, c.amount "Payment Amount", c.status_lookup_code "Check Status"
, decode(c.payment_type_flag, 'A', 'Auto', 'M', 'Manual', 'Q', 'Quick', 'R', 'Refund', c.payment_type_flag) "Payment Type"
, c.payment_method_code, c.vendor_name, c.remit_to_supplier_name,  c.bank_account_name
, h.statement_header_id, h.statement_number, h.statement_date, h.bank_account_id, h.creation_date, h.auto_loaded_flag, h.statement_complete_flag
, l.statement_line_id, l.line_number, l.trx_date, l.trx_type, l.amount "Statement Amount", l.status, l.BANK_TRX_NUMBER
, l.trx_text, l.customer_text, l.invoice_text, l.bank_account_text
from ap_checks_all c, ce_statement_reconcils_all cre
, ce_statement_lines l, ce_statement_headers h
, hr_operating_units h
where 1=1
and cre.statement_line_id = l.statement_line_id
and l.statement_header_id = h.statement_header_id
and c.org_id = h.organization_id
and c.check_id = cre.reference_id
and c.check_date between '01-JAN-2017' and '31-MAR-2017'
and c.status_lookup_code <> 'VOIDED'
and cre.current_record_flag = 'Y'
and cre.reference_type = 'PAYMENT'
and cre.status_flag = 'M'
union
select h.name, c.check_id, c.check_number, c.check_date, c.currency_code, c.amount "Payment Amount", c.status_lookup_code "Check Status"
, decode(c.payment_type_flag, 'A', 'Auto', 'M', 'Manual', 'Q', 'Quick', 'R', 'Refund', c.payment_type_flag) "Payment Type"
, c.payment_method_code, c.vendor_name, c.remit_to_supplier_name,  c.bank_account_name
, h.statement_header_id, h.statement_number, h.statement_date, h.bank_account_id, h.creation_date, h.auto_loaded_flag, h.statement_complete_flag
, l.statement_line_id, l.line_number, l.trx_date, l.trx_type, l.amount "Statement Amount", l.status, l.BANK_TRX_NUMBER
, l.trx_text, l.customer_text, l.invoice_text, l.bank_account_text
from ce_statement_lines l, ce_statement_headers h, ap_checks_all c, ce_bank_accounts ba, hr_operating_units h
where l.statement_header_id = h.statement_header_id
and l.BANK_TRX_NUMBER = to_char(c.check_number)
and ba.bank_account_id = h.bank_account_id
and ba.bank_account_name = c.bank_account_name
and c.org_id = h.organization_id
and c.check_date between '01-JAN-2017' and '31-MAR-2017'
and c.status_lookup_code <> 'VOIDED'
and not exists (select 1 from ce_statement_reconcils_all cre
where cre.current_record_flag = 'Y'
and cre.reference_type = 'PAYMENT'
and cre.status_flag in ('M')
and c.check_id = cre.reference_id)
union
select h.name, c.check_id, c.check_number, c.check_date, c.currency_code, c.amount "Payment Amount", c.status_lookup_code "Check Status" --36,273
, decode(c.payment_type_flag, 'A', 'Auto', 'M', 'Manual', 'Q', 'Quick', 'R', 'Refund', c.payment_type_flag) "Payment Type"
, c.payment_method_code, c.vendor_name, c.remit_to_supplier_name,  c.bank_account_name
, null, null, null,null,null,null,null
, null, null, null, null, null,null,null
, null, null, null, null
from ap_checks_all c, hr_operating_units h
where 1=1
and c.check_date between '01-JAN-2017' and '31-MAR-2017'
and c.status_lookup_code not in ('VOIDED', 'RECONCILED')
and h.organization_id = c.org_id
and not exists (select 1 from ce_statement_lines l, ce_statement_headers h, ce_bank_accounts ba
where l.statement_header_id = h.statement_header_id
and ba.bank_account_id = h.bank_account_id
and ba.bank_account_name = c.bank_account_name
and l.BANK_TRX_NUMBER = to_char(c.check_number))
union
select null, null, null, null, null, null, null, null, null, null, null, ba.bank_account_name
, h.statement_header_id, h.statement_number, h.statement_date, h.bank_account_id, h.creation_date, h.auto_loaded_flag, h.statement_complete_flag
, l.statement_line_id, l.line_number, l.trx_date, l.trx_type, l.amount "Statement Amount", l.status, l.BANK_TRX_NUMBER
, l.trx_text, l.customer_text, l.invoice_text, l.bank_account_text
from ce_statement_lines l, ce_statement_headers h, ce_bank_accounts ba
where l.statement_header_id = h.statement_header_id
and ba.bank_account_id = h.bank_account_id
and h.creation_date between '01-JAN-2017' and '31-MAR-2017'
and trx_type = 'DEBIT'
and l.status = 'UNRECONCILED'
and ba.account_owner_org_id in (23281,23301,23302,23303,23304,23305)
and not exists (select 1 from ap_checks_all c
where c.status_lookup_code <> 'VOIDED'
and l.BANK_TRX_NUMBER = to_char(c.check_number)
and ba.bank_account_name = c.bank_account_name)
and not exists (select 1 from CE_STATEMENT_RECONCILS_ALL  csr
    where csr.statement_line_id = l.statement_line_id
    and REFERENCE_TYPE = 'JE_LINE')