Reset Page Numbers in XML Publisher (XMLP)

@section:row_AMS_SOA_PROG_T?> That's it.

Database creation date time

select created from v$database;

Hiding Elements - Printable Version of a Page

Rather than create a printable version of a page that is a copy of a given page sans buttons, headers and other elements that don't look good on a hard-copy, you can add a simple button and then define which fields you don't want to be printable. Much easier.

1. Add a button or link to your page.
2. Under the Push Button/Hyperlink Page Properties setup as follows:
   Destination = 'External Link'
   External Link = 'Dynamic'
3. Add some PeopleCode:
   DERIVED_REC.JS_PRINT.Value = "javascript:window.print()";
   
   Now you have a basic link on your page that when clicked will print it from the browser.
4. Add a HTML Area to the page with the following value:
   
   <style type="text/css">
@media print {
#PAGEBAR, .PSHEADERTOOLBARFRAME, #PRINT_BTN,
#INFO,
#DERIVED_REC_JS_PRINT { display: none }
}
</style>
   
   Note that #DERIVED_REC_JS_PRINT is the link you made in step 1.
   #Info in this example is a chunk of HTML text I wrapped in <span id="Info">
   You can add any field, usually in the format RECORD_NAME_FIELD_NAME, check your HTML source to be sure.

PDF Stamper (iText)

Download the JAR file from here:
http://itextpdf.com/download.php

Place it on the App Server, where all the other JAR files are (somewhere)

Note you'll need Acrobat Pro to add fields to the PDF and give them names.

Example PeopleCode. Will Instantiate the Java object, open the Template file (source) and the target file (...target) and then map data to the defined fields on your template.

Will also create an attachment and then view ti - pop up the PDF output.

Local JavaObject &Outputstream, &PDFWriter, &PDFReader, &PDFStamper, &TemplateFields;

/* File name */
Evaluate &recAMS_CAF_DAT_VW.AMS_CAF_TYPE.Value
When = "C"
   /* Pivot date for old/new form is 22/12/2010 */
   If &recAMS_CAF_DAT_VW.EFFDT.Value >= Date(20101222) Then
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1290A_NEW_TEMPLATE.pdf";
   Else
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1290A_OLD_TEMPLATE.pdf";
   End-If;
When = "F"
   /* There is a really old form version, pre 2009. */
   If &recAMS_CAF_DAT_VW.EFFDT.Value >= Date(20090101) Then
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1292A_OLDOLD_TEMPLATE.pdf";
   Else
      /* Pivot date for old/new form is 22/12/2010 */
      If &recAMS_CAF_DAT_VW.EFFDT.Value >= Date(20101222) Then
         &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1292A_NEW_TEMPLATE.pdf";
      Else
         &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1292A_OLD_TEMPLATE.pdf";
      End-If;
   End-If;
When = "V"
   /* Pivot date for old/new form is 22/12/2010 */
   If &recAMS_CAF_DAT_VW.EFFDT.Value >= Date(20101222) Then
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1296A_NEW_TEMPLATE.pdf";
   Else
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1296A_OLD_TEMPLATE.pdf";
   End-If;
End-Evaluate;

&FILE = %OperatorId | "_" | %Datetime | "_eCAF_PDF_STAMPER_OUTPUT.pdf";

/* Output File path */
/* ps_home = /software/peoplesoft/product/pscs/pscsdv4 */
&InOutputPdfPathname = GetEnv("PS_HOME") | "/rmit/tmp/" | &FILE;


/* Create the "input" Template PDF */
&PDFReader = CreateJavaObject("com.lowagie.text.pdf.PdfReader", &TemplatePathname);
&PDFReader.consolidateNamedDestinations();
&PDFReader.removeUnusedObjects();

/* Create the "Output" Template PDF */
&Outputstream = CreateJavaObject("java.io.FileOutputStream", &InOutputPdfPathname);
&PDFStamper = CreateJavaObject("com.lowagie.text.pdf.PdfStamper", &PDFReader, &Outputstream);

/* Flatten the form */
&PDFStamper.setFormFlattening( True);

/* Get the fields to stamp */
&TemplateFields = &PDFStamper.getAcroFields();

/* Stamp fields */
&TemplateFields.setField("FAMILY_NAME", "Test family name");
rem &TemplateFields.setField("name", "John Student");
rem &TemplateFields.setField("address", "Baeyensstraat 121, Sint-Amandsberg");
rem &TemplateFields.setField("postal_code", "BE-9040");
rem &TemplateFields.setField("email", "s1234567@somewhere.edu.au");

/* Close the stamper */
&PDFStamper.close();

/* Needed if in think-time */
CommitWork();


/* Put PDF file temporarily into database, so we can send to users browser using the ViewAttachment function */
&Ret = PutAttachment("record://AMS_ECAF_ATTACH", &FILE, &InOutputPdfPathname);
If &Ret = %Attachment_Success Then;
   
   /* Send Output file to the browser */
   &Ret = ViewAttachment("record://AMS_ECAF_ATTACH", &FILE, &FILE, True, True);
   
   /* Remove temporary PDF file from database */
   &Ret = DeleteAttachment("record://AMS_ECAF_ATTACH", &FILE, True);
   
End-If;

Sequential Numbers - SQL (2)

MERGE INTO ps_sf_acctg_ln
USING (SELECT ROW_NUMBER() OVER(PARTITION BY run_dt,seqnum ORDER BY run_dt,seqnum, sf_line_nbr) sf_line_nbr, ROWID rid FROM ps_sf_acctg_ln) SOURCE
ON (ps_sf_acctg_ln.ROWID = SOURCE.rid)
WHEN MATCHED THEN UPDATE SET ps_SF_ACCTG_LN.SF_LINE_NBR = SOURCE.sf_line_nbr;
commit;

Sequential Numbers - SQL

This will creating a running sequence number for each unique combination of RUN_DT and SEQNUM; NEW_SF_LINE_NBR

SELECT
run_dt,
seqnum,
ROW_NUMBER() OVER(PARTITION BY run_dt,seqnum ORDER BY run_dt, seqnum) new_sf_line_nbr
FROM ps_sf_acctg_ln

Using DateTime Fields in SQR - Gotcha #343

This will return unexpected results:

AND A.CLASS_PRICE_DTTM <= $RC_FromDate

This will return expected results:

AND A.CLASS_PRICE_DTTM <= to_date($RC_FromDate, 'DD-MON-YYYY')

Where $RC_FromDate is a variable populated from a Date field on your Run Control page.