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

Pages

Sunday, 1 December 2019

Types Of Triggers In Oracle Reports

0 comments
What types of triggers are there in oracle report ?



      There are eight Triggers available, among them five triggers consider as global Oracle Report triggers and these trigger are used initializing parameter values, Validate Parameter values and Dynamic query execution


Oracle Reporting Triggers



Following Order of Oracle Triggers Firing sequence



1.Before Parameter Form
2.After Parameter Form
3.Before Report
4.Between Pages
5.After Report



1Before Parameter Form: Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.

2. After Parameter Form: Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

3Before Report: Fires before the report is executed but after queries are parsed 


4Between Pages: Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Previewer, this trigger only fires the first time that you go to page. If you subsequently return to the page, the trigger does not fire again.

5. After Report: Fires after you exit the Previewed, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle Office userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.






Following three general triggers not consider as main triggers:

1.Validation Triggers
2.Format Triggers
3.Action Triggers

1. Validation Triggers: Validation trigger are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each validation trigger may fire twice when you execute the report) Validation trigger are also used to validate the Initial Value property of the parameter. The function must return a boolean value.

2. Format Triggers: This trigger are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object. The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.

Definition Level: layout object
On Failure:  Excludes the current instance of the object from the output.
Format trigger example (highlighting a value)
/* suppose that you are building a banking report and would like it to indicate if a customer is overdrawn. To do so, you give the repeating frame around the customer information a format trigger that causes it to have a border only if a customer's account balance is less than 0 (or the required minimum balance). */
function my_formtrig return BOOLEAN is begin
  if: bal < 0 then
    srw.attr.mask := SRW.BORDERWIDTH_ATTR;
    srw.attr.borderwidth := 1;
    srw.set_attr (0, srw.attr);
  end if;
    return (true);
end;

3. Action Triggers: Action triggers are PL/SQL procedures executed when a button is selected in the Runtime Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL. You can access action triggers from the Object Navigator, the Property Palette (PL/SQL Trigger property), or the PL/SQL Editor.


Definition Level: button

Continue reading >>

Thursday, 21 November 2019

Resolved: ORA-00913 Too Many Values

0 comments

ORA-00913 Too Many Values:

     This error will occur many cases here shown some causes ,occurs and solutions

Cause: 

     The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set.

Case 1:

     This error will occurs when create Subquery as shown below and it will return More than one value and it will through the error like ORA-00913 Too Many Values


SELECT * FROM EMP WHERE Deptno in (SELECT deptno,dname FROM Dept)


In this case subquery return more than one value (deptno,dname) but subquery need to return only one value(deptno) as shown below statement


Solution:

SELECT * FROM EMP WHERE Deptno in (SELECT deptno FROM Dept)



Case 2:

     This error will occurs when inserting data into table with multiple values as shown below and it will return More than one value and it will through the error like ORA-00913 Too Many Values


Insert into dept (deptno,dname) values (10,'Accounting','US')

In this case inserting data into table reference columns two (deptno,dname) but inserting values are three(10,'Accouing','US') return more than one value but values should be two (10,'Accouing') as shown below statement


Solution:


Insert into dept (deptno,dname) values (10,'Accounting')


Case 3:

     This error will occurs when scaler query as shown below and it will return More than one value and it will through the error like ORA-00913 Too Many Values


SELECT deptno,  dname, (SELECT ename  FROM emp WHERE deptno = d.deptno) ename FROM dept d


In this case scaler query return more than one value, One department having multiple employees you can show all employees by placing comma separated values as shown below by using list tag function


Solution:


SELECT deptno,
       dname,
       (SELECT listagg (ename, ',') WITHIN GROUP (ORDER BY enameename 
          FROM emp
         WHERE deptno = d.deptno)
  FROM dept d



Continue reading >>

Monday, 11 November 2019

Display Running Total in RTF xml Reports

0 comments


Running Total means previous value need to add to current values as shown below.

Running Total Sample xml rtf report




Display your Running total by following two steps.


Let us take example from salary column from EMP table as shown below for running total in rtf xml report




Step1:  Define Variable with initialize it to 0 before group start as shown below 

            <?xdoxslt:set_variable($_XDOCTX, 'empsal', 0)?>

Running Total Variable Define xml reports

Step2: Refer variable in running total column as shown below.


  <?xdoxslt:set_variable($_XDOCTX, 'empsal', xdoxslt:get_variable($_XDOCTX, 'empsal') + SAL)?> <?xdoxslt:get_variable($_XDOCTX, 'empsal')?>


Runing Total Output:




Continue reading >>

Sunday, 10 November 2019

Query to Get WIP Discrete Job Details in Oracle Apps R12

0 comments

SELECT wip.wip_entity_name WO_NUMBER,
  msi.description,
  wip.start_quantity qty,
  msi.segment1 part_number,
  (SELECT OPERATION_DESCRIPTION
  FROM
    (SELECT BOS.OPERATION_SEQ_NUM,
      BOS.OPERATION_DESCRIPTION
    FROM APPS.BOM_OPERATIONAL_ROUTINGS BOR,
      APPS.BOM_OPERATION_SEQUENCES BOS
    WHERE BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
    AND BOR.ORGANIZATION_ID       = WIP.ORGANIZATION_ID
    AND BOR.ASSEMBLY_ITEM_ID      = WIP.PRIMARY_ITEM_ID
    AND UPPER(BOS.OPERATION_DESCRIPTION) LIKE 'S/U%PRESS%'
    ORDER BY BOS.OPERATION_SEQ_NUM
    )
  WHERE ROWNUM = 1
  ) SETUP_OP_DESC,
  NVL(wip.date_released,wip.creation_date) released_creation_date,
  wip.job_type_meaning wo_type,
  wip.status_type_disp,
  TO_CHAR(wip.scheduled_start_date,'DD-MON-YYYY') start_date,
  TO_CHAR(wip.scheduled_completion_date,'DD-MON-YYYY') comp_date,
  TO_CHAR(wip.date_released,'DD-MON-YYYY HH24:MI:SS') date_released,
  TO_CHAR(wip.creation_date,'DD-MON-YYYY HH24:MI:SS') creation_date
FROM apps.wip_discrete_jobs_v wip,
  apps.mtl_parameters mp,
  apps.mtl_system_items_b msi
WHERE mp.organization_id  = msi.organization_id
AND mp.organization_id    = wip.organization_id
AND msi.inventory_item_id = wip.primary_item_id

Continue reading >>

Thursday, 29 August 2019

Query to Get Assets Financial Information in Oracle Apps R12

0 comments

SELECT fab.asset_number asset,
       fat.description asset_name,
       fab.asset_type,
       fai.invoice_number,
       fai.po_number,
       (SELECT pla.line_num
        FROM   po_lines_all pla,
               po_distributions_all pda,
               ap_invoice_distributions_all aida
        WHERE  pda.po_line_id = pla.po_line_id
        AND    aida.po_distribution_id = pda.po_distribution_id
        AND    aida.invoice_distribution_id = fai.invoice_distribution_id)
                                                                    po_line_num,
       (SELECT replace(pla.item_description, chr(10) , ' ')
        FROM   po_lines_all pla,
               po_distributions_all pda,
               ap_invoice_distributions_all aida
        WHERE  pda.po_line_id = pla.po_line_id
        AND    aida.po_distribution_id = pda.po_distribution_id
        AND    aida.invoice_distribution_id = fai.invoice_distribution_id)
                                                            po_line_description,
       aps.vendor_name,
       aps.segment1 supplier_number,
       replace(fai.description, chr(10) , ' ')description,
       fai.fixed_assets_cost line_amount,
       fai.invoice_line_number invoice_line,
       fai.ap_distribution_line_number fa_dist_line,
       fai.deleted_flag active,
       fai.payables_batch_name source_batch,
       fai.project_id project_number,
       fai.task_id task_number
FROM   fa_additions_b fab,
       fa_additions_tl fat,
       fa_asset_invoices fai,
       ap_suppliers aps,
       fa_books fb
WHERE  fab.asset_id = fat.asset_id
AND    fai.asset_id = fat.asset_id
AND    aps.vendor_id(+) = fai.po_vendor_id
AND    fb.asset_id = fai.asset_id
AND    fai.date_ineffective IS NULL
AND    fb.date_ineffective IS NULL

Continue reading >>

Query to get Corporate Card Inter-Company Accounting Details in Oracle apps R12

0 comments

SELECT DISTINCT aeh.invoice_num exp_rept_num
              , aeh.report_header_id
              , ael.report_line_id
              , ael.merchant_name
              , ( SELECT segment1
                 FROM   po_vendors pv
                      , ap_invoices_all aia
                 WHERE  pv.vendor_id = aia.vendor_id
                 AND    aia.invoice_id = aila.invoice_id ) vendor_num
              ,
                glcc.segment1 company_code
              , glcc.segment2 dept_code
              , paf.full_name employee_name
              , xah.accounting_date
              , ael.creation_date expense_date
              , CASE
                   WHEN NVL( UPPER( :p_expense_type ), 'A' ) IN
                                                             ( 'Y', 'N', 'A' )
                      THEN CASE
                             WHEN ael.credit_card_trx_id IS NOT NULL
                                THEN 'Credit Card'
                             WHEN ael.credit_card_trx_id IS NULL
                                THEN 'Out Of Pocket and Per-diem'
                          END
                END expense_type
              , aerp.prompt expense_item
              , xdl.unrounded_entered_dr debit
              , xdl.unrounded_entered_cr credit
              , (   NVL( xdl.unrounded_entered_dr, 0 )
                  - NVL( xdl.unrounded_entered_cr, 0 )
                ) dr_cr
              , glcc1.segment6 accounting_company_code
              , xal.code_combination_id
              , xal.ae_line_num
              ,    glcc1.segment1
                || '.'
                || glcc1.segment2
                || '.'
                || glcc1.segment3
                || '.'
                || glcc1.segment4
                || '.'
                || glcc1.segment5
                || '.'
                || glcc1.segment6
                || '.'
                || glcc1.segment7 charge_account
              -- Added by GSINGH FOR Version 1.5
,               aeh.week_end_date
              , ap_web_policy_utils.get_lookup_meaning
                                        ( 'EXPENSE REPORT STATUS'
                                        , DECODE
                                               ( ( SELECT ai.cancelled_date
                                                  FROM   ap_invoices_all ai
                                                  WHERE  ai.invoice_id =
                                                                   aeh.vouchno )
                                               , NULL, aeh.expense_status_code
                                               , 'CANCELLED'
                                               )
                                        ) report_status
              , ap_web_policy_utils.get_lookup_meaning( 'OIE_AUDIT_TYPES'
                                                      , NVL( aeh.audit_code
                                                           , 'AUDIT'
                                                           )
                                                      ) audit_type
              , ap_web_policy_utils.get_lookup_meaning( 'RECEIPT_STATUS'
                                                      , aeh.receipts_status
                                                      )
                                                       receipt_package_status
              , ( SELECT DISTINCT LISTAGG(ppa.segment1,'~')  WITHIN GROUP (ORDER BY  ppa.segment1) a
                 FROM   pa_projects_all ppa
                      , ap_exp_report_dists_all aerd
                 WHERE  ppa.project_id = aerd.project_id
                 AND    aeh.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id = ael.report_line_id )
                                                               project_number
        ,(SELECT DISTINCT LISTAGG(pt.task_number,'~')  WITHIN GROUP (ORDER BY  task_number) a FROM  pa_tasks pt
                      , ap_exp_report_dists_all aerd
                 WHERE  pt.task_id = aerd.task_id
                   AND    aerd.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id =ael.report_line_id ) task_number  
              , ( SELECT LISTAGG(ho.name,'~')  WITHIN GROUP (ORDER BY  ho.name) a
                 FROM   hr_all_organization_units ho
                      , ap_exp_report_dists_all aerd
                 WHERE  ho.organization_id = aerd.expenditure_organization_id
                 AND    aeh.report_header_id = aerd.report_header_id
                 AND    aerd.report_line_id = ael.report_line_id ) exp_org
              , ael.justification
-- END
FROM            ap_expense_report_headers_all aeh
              , ap_expense_report_lines_all ael
              , ap_expense_report_params_all aerp
              , ap_invoice_distributions_all aid
              , xla_ae_headers xah
              , xla_ae_lines xal
              , xla_distribution_links xdl
              , ap_invoice_lines_all aila
              , per_all_people_f paf
              , per_all_assignments_f pasf
              , gl_code_combinations glcc
              , gl_code_combinations glcc1
WHERE           aeh.report_header_id = ael.report_header_id
AND             aeh.vouchno != 0
AND             EXISTS(
                   SELECT 'X'
                   FROM   ap_expense_report_lines_all
                   WHERE  report_line_id = ael.report_line_id
                   AND    UPPER( :p_expense_type ) = 'Y'
                   AND    credit_card_trx_id IS NOT NULL )
AND             xal.code_combination_id = glcc1.code_combination_id
AND             aeh.employee_id = paf.person_id
AND             TRUNC( SYSDATE ) BETWEEN TRUNC( paf.effective_start_date )
                                     AND TRUNC( paf.effective_end_date )
AND             paf.person_id = pasf.person_id
AND             TRUNC( ael.creation_date )
                   BETWEEN TRUNC( pasf.effective_start_date )
                       AND TRUNC( pasf.effective_end_date )
AND             pasf.default_code_comb_id = glcc.code_combination_id
AND             ael.web_parameter_id = aerp.parameter_id(+)
AND             aeh.vouchno = aid.invoice_id
AND             aid.line_type_lookup_code = 'ITEM'
AND             aid.accounting_event_id = xah.event_id
AND             aid.invoice_distribution_id = xdl.source_distribution_id_num_1
AND             xah.ae_header_id = xal.ae_header_id
AND             xal.ae_line_num = xdl.ae_line_num
AND             xal.ae_header_id = xdl.ae_header_id
AND             xah.ae_header_id = xdl.ae_header_id
AND             xah.event_id = xdl.event_id
AND             xdl.rounding_class_code = 'ITEM EXPENSE'
AND             aeh.vouchno = aila.invoice_id
AND             ael.report_line_id = aila.reference_key2
AND             aila.line_type_lookup_code = 'ITEM'
AND             aila.line_number = aid.invoice_line_number                                    

Continue reading >>