Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 8 April 2018

Query Cheque Stock Register Details

0 comments


SELECT   TO_CHAR (aca.creation_date, 'DD-MON-YYYY') CREATION_DATE,
           INT_BANK_NAME,
           INT_BANK_ACCOUNT_NUMBER,
           aca.check_number,
           cpd.payment_document_name,
           cpd.first_available_document_num Rec_bal_from,
           cpd.last_available_document_number Rec_bal_to,
           (SELECT   MIN (check_number)
              FROM   ap_checks_All aca1
             WHERE   aca1.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
                     AND TRUNC (aca1.creation_date) = TRUNC (aca.creation_date))
              Open_bal_from,
           cpd.last_available_document_number Open_bal_to,
           (SELECT   MIN (check_number)
              FROM   ap_checks_All aca1
             WHERE   aca1.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
                     AND TRUNC (aca1.creation_date) = TRUNC (aca.creation_date))
              issue_bal_from,
           (SELECT   MAX (check_number)
              FROM   ap_checks_All aca1
             WHERE   aca1.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
                     AND TRUNC (aca1.creation_date) = TRUNC (aca.creation_date))
              ISSUE_BAL_TO,
           (SELECT   MAX (check_number) + 1
              FROM   ap_checks_All aca1
             WHERE   aca1.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
                     AND TRUNC (aca1.creation_date) = TRUNC (aca.creation_date))
              CLOSE_BAL_FROM,
           cpd.last_available_document_number clsoe_bal_to,
           DECODE (aca.STATUS_LOOKUP_CODE, 'VOIDED', 'Yes', 'No') VOIDED,
           ipa.payment_date,
           PAYEE_SUPPLIER_NUMBER,
           (SELECT   vendor_name
              FROM   ap_suppliers
             WHERE   vendor_id = ipa.INV_PAYEE_SUPPLIER_ID)
              vendor_name,
           (SELECT   vendor_site_code
              FROM   ap_supplier_sites_all
             WHERE   vendor_id = ipa.INV_PAYEE_SUPPLIER_ID
                     AND vendor_site_id = ipa.SUPPLIER_SITE_ID)
              site_code,
           ipa.PAYMENT_METHOD_CODE,
           aca.void_date gl_date
    FROM   ap_checks_All aca, iby_payments_all ipa, ce_payment_documents cpd
   WHERE       ipa.paper_document_number = aca.check_number
           AND ipa.PAYMENT_ID = aca.PAYMENT_ID
           AND aca.PAYMENT_DOCUMENT_ID = cpd.PAYMENT_DOCUMENT_ID
ORDER BY   aca.CREATION_DATE,
           INT_BANK_NAME,
           INT_BANK_ACCOUNT_NAME,
           CHECK_NUMBER

Continue reading >>

Monday, 25 December 2017

Enabling Query Behavior In Oracle Forms In Oracle Apps

0 comments



Implementing Row–LOV
Implementing Find Windows

Enabling Query Behavior



Steps To Implementing Row–LOV


  • Create a Parameter for Your Primary Key
  • Create an LOV and attach the return value as your parameter
  • Create a block–level PRE–QUERY trigger (Execution Hierarchy: Before)
              IF :parameter.G_query_find = ’TRUE’ THEN
<Primary Key> := :parameter.<Your parameter>;
:parameter.G_query_find := ’FALSE’;
              END IF;

  • Create a block–level user–named trigger QUERY_FIND on the results block (Execution Hierarchy: Override) that contains:
APP_FIND.QUERY_FIND(’<Your LOV Name>’);

Implementing Find Windows


  • Copy the QUERY_FIND Object Group from APPSTAND
  • After copying it, delete the object group.
  • Rename the Block, Canvas and Window
  • Edit the NEW Button’s Trigger with :
app_find.new(’<Your results blockname here>’);

  • Edit the FIND Button’s Trigger with :
app_find.find(’<Your results blockname here>’);


  • Set the Previous Navigation Data Block property of the Find block to be the results block.
  • Edit the KEY–NXTBLK Trigger same as FIND button’s trigger so that if user presses Go -> Next Block the behavior should mimic the FIND button
  • Change the Find Window Title
  • Create Necessary Items
Set the Required property to No
Set the default value to NULL
Attach the LOV and canvas
Set the query length to 100 and change the data type accordingly


  • Create a block–level Pre–Query trigger in the Results block (Execution Hierarchy: Before) that copies query criteria from the Find window block to the Results block (where the query actually occurs).
IF :parameter.G_query_find = ’TRUE’ THEN
COPY (<find Window field>,’<results field>’);
:parameter.G_query_find := ’FALSE’;
END IF;

  • Create a block–level user–named trigger ”QUERY_FIND” (Execution Hierarchy: Override) on the Results block that contains:
APP_FIND.QUERY_FIND(’<results block window>’, ’<Find window>’, ’<Find window block>’);

Raising Query Find on Form Start Up

At the end of your WHEN–NEW–FORM–INSTANCE trigger, call: EXECUTE_TRIGGER(’QUERY_FIND’);


Continue reading >>