Search This Blog

Monday, December 13, 2010

Matrix Report in XML Publisher

This report is number of JVs created per month per source wise.

select je_source,to_char(date_created,'Mon-YY') Source_Month,count(je_header_id) je_count from gl_je_headerswhere to_char(date_created,'Q') = :p_quarter and to_char(date_created,'YYYY') = :p_year group
 by je_source,to_char(date_created,'Mon-YY')
RDF report:

Total Number of JVs Quarterly

Source Name
G If exists: JE_COUNT end; E
G If exists: 999 end; E

G Close to JE_SOURCE - <?for-each-   group@section:G_SERVICE_NAME;./JE_SOURCE?><?sort:JE_SOURCE;'ascending';data-type='text'?>

 JE_SOURCE - <?JE_SOURCE?><?variable@incontext:JS;JE_SOURCE?>
 G close to Source_Month :
Source_Month - <?SOURCE_MONTH?>

E  - <?end for-each-group?>
G Near If Exists -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH?
IF Exists –

JE_COUNT - <?current-group()[JE_SOURCE=$JS]/JE_COUNT?> 
End; - <?end if?>

E  - <?end for-each-group?>
G in Total -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH?

IF Exists in Total –
999 in Total
End if in Total:
<?end if?>
E in Total –
<?end for-each-group?>

999 in Total Column - <?sum(//G_SERVICE_NAME[JE_SOURCE=$JS]/JE_COUNT)?>
E in Total Column:
<?end for-each-group?>



Sunday, December 12, 2010

How to Use FND_STANDARD_DATE value set

Let’s take example of report – AP Invoices Paid in Time
Here GL_Date in Date parameter.
SELECT api.invoice_id, api.invoice_num, invoice_date, api.invoice_amount,
       api.vendor_name, api.vendor_number, apc.check_number, aps.due_date,
  FROM ap_invoices_v api,
       ap_payment_schedules_all aps,
       ap_invoice_payments_all aip,
       ap_checks_all apc
 WHERE api.invoice_id = aps.invoice_id
   AND aip.invoice_id = api.invoice_id
   AND aip.check_id = apc.check_id
   AND apc.check_date <= aps.due_date
   --and api.gl_date >= nvl(:p_gl_date,api.gl_date)
   AND api.gl_date >=
          NVL (TO_DATE (SUBSTR (:p_gl_date, 1, 10), 'YYYY/MM/DD'),

That's it. Test the report through SRS window.

Stock Market in India Development of Stock Market in India