Oracle Change/Modify PO Print Logo XSL-FO

SOURCE
Changing the Logo on PO Print is a little tricky. I say tricky purely because even though the PO Print program is a XML Publisher based report the layout part of the report is XSL-FO type. So all the details are in XML.

--------------------------------------------------------
Note: For doing this we need to have XML Publisher template builder installed. Pls follow the below instructions

Download the patch from Metalink. Latest version as of today is Patch 12395372: UPDATE FOR BI PUBLISHER DESKTOP 10.1.3.2.1 (5.6.3) size = 95.9 MB Product=BI Publisher (formerly XML Publisher)

Unzip this patch and then run BIPublisherDesktop.exe to install in a directory
--------------------------------------------------------
So lets get to the task.

Step 1: Create an RTF Document and embed the image into the document. I create a MSWord document and 'Save As' RTF document. And in Word (Menu)Insert -> Picture -> From File -> Select the image.

Step 2: Update the size of image to approx the size it appears in the document.

Step 3: Once you have template builder installed, the plug-in adds new menu options in Word. Tools -> Export -> XSL-FO Style Sheet. Screenshot attached

Step 4: An XML file is generated, Open the file and search for tag 'fo:instream-foreign-object' tag which is of our interest.

Step 5: Copy the tag contents from the beginning to the end of the tag 'fo:instream-foreign-object'. So this is our image xdofo:uid 

Step 6: Open XML Publisher, Search for Template 'Standard Purchase Order Stylesheet', Duplicate the template entering required details. Download PO_STANDARD_XSLFO.xsl and rename to XXC_PO_STANDARD_XSLFO.xsl

Step 7: Open XXC_PO_STANDARD_XSLFO.xsl and search for 'fo:inline' which contains the Logo info. Uncomment the inline tags and replace the 'fo:external-graphic content' with the tags contents we copied in step 5.

The file should look like this.

Step 8: Save XXC_PO_STANDARD_XSLFO.xsl and attach the file to the template we created in Step 6. Also update the xsl:style sheet definition(at the beginning of the document) to refer to xdo namespace as follows.

'xsl:stylesheet version="1.0" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"' 

Step 9: Attach the new template to PO Document Types. (Document Type Layout), so the new template will be picked for the PO document creation

Step 10: For this article purpose lets simply the process and see if we can get the PO Document with the new Logo, by using the preview button in XML Publisher(for the new template we created in Step 6).Here we have it.

R12.2 Links

Oracle E-Business Suite Release 12.2 Information Center - Documentation

Doc ID 1583110.1

http://docs.oracle.com/cd/V39571_01/current/html/docset.html

Label Printing WMS

Note: 242789.1 - Oracle Warehouse Management Label Customization 
Note: 298250.1 - Troubleshooting WMS / MSCA Label Printing
Note: 875122.1 - Certified Integration Partners With Oracle WMS/MSCA For Label Printing

Label Printing Strategies for Oracle Warehouse Management System (WMS) (Doc ID 414368.1)

11i - Inventory Label Printing Documentation (Doc ID 277357.1)

MMT Columns

For Sales Order Pick

TRX_SOURCE_LINE_ID : Sales order line id

SOURCE_LINE_ID : Move order line id

PO receipt

TRX_SOURCE_LINE_ID : NULL

SOURCE_LINE_ID :  rcv transaction id

Sales order imp columns

------------------------------------------------------------

OE ORDER HEADERS ALL 

------------------------------------------------------------

order_category_code  

Identify if it's a return

ORDER
RETURN


order_source_id

Identify if its a Normal Sales order or Internal

SELECT *
FROM   OE_ORDER_SOURCES
WHERE  order_source_id

SELECT org_id, order_source_id
FROM   po_system_parameters_all

sales_channel_code

lookup_type = 'SALES_CHANNEL' 

freight_terms_code

lookup_type = 'FREIGHT_TERMS' 

fob_point_code

lookup_type = 'FOB'

payment_term_id

ra_terms

shipping_method_code

wsh_carrier_services

price_list_id

qp_list_headers

------------------------------------------------------------

OE ORDER LINES ALL 

------------------------------------------------------------

item_type_code

lookup_type = 'ITEM_TYPE'

source_type_code 

INTERNAL   : non-dropship orders  
EXTERNAL  : Dropship

---------------------------------------------------------------------

PROGRAMS

---------------------------------------------------------------------

DEMAND INTERFACE PROGRAMS - 

Creates Demand in mtl_demand table.

After Order is booked

SCHEDULE ORDER PROGRAM - 

Creates soft reservation for the sales order

SELECT ooh.order_number
              ,ool.line_id
              ,ool.ordered_quantity
              ,ool.shipped_quantity
              ,ool.invoiced_quantity
              ,wdd.delivery_detail_id
              ,wnd.delivery_id
              ,rctl.interface_line_attribute1
              ,rctl.interface_line_attribute3
              ,rctl.interface_line_attribute6
              ,rct.org_id
              ,rct.creation_date
              ,trx_number
              ,rctl.quantity_ordered
              ,rct.interface_header_context
  FROM oe_order_headers_all ooh
             ,oe_order_lines_all ool
             ,wsh_delivery_details wdd
             ,wsh_new_deliveries wnd
             ,wsh_delivery_assignments wda
             ,ra_customer_trx_all rct
             ,ra_customer_trx_lines_all rctl
 WHERE ooh.header_Id=ool.header_id
      AND wdd.source_header_id=ooh.header_id
      AND wdd.delivery_detail_Id=wda.delivery_detail_id
      AND wda.delivery_id=wnd.delivery_id
      AND rctl.interface_line_attribute1=to_char(ooh.order_number)
      AND rctl.interface_line_attribute6=to_char(ool.line_id)
      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)
      AND rctl.customer_trx_id=rct.customer_trx_id
      AND rct.interface_header_context='ORDER ENTRY'

SQLERRM : ORA-06508 PL/SQL: Could Not Find Program Unit Being Called

SOURCE

When you search this error on the net, you will find out these :

Cause:	An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.
Action:	Check that all referenced programs, including their package bodies, exist and are compatible.

Yes, that’s true. But if you have lots of objects depends to a lot of object  You will need to more information to fix this error. If you face up with this error, you probably use SQLCODE and SQLERRM in your exception block of your PL/SQL code. Something  like this:

BEGIN
 NULL;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 INSERT_LOG(SQLCODE, SQLERRM);
END;

I do not advise to handle errors in this way. Because this error description does not give you enough information abour your error as ORA-06508 error. You don’t really know what triggered this exception. Operation of this code on a production database is also not easy. At this point, Oracle’s DBMS_UTILITY package will be included in out lives. 

If you write something like this:

BEGIN
 NULL;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 INSERT_LOG(SQLCODE,
 DBMS_UTILITY.FORMAT_ERROR_STACK || '@' ||
 DBMS_UTILITY.FORMAT_CALL_STACK);
END;

You will not get ORA-06508 error for the same error. You will get something like this explanation:

ORA-04045: errors during recompilation/revalidation of SIM.PKXXX
ORA-04052: error occurred when looking up remote object TCLCM.PRC_XX@ERP_APPS.WORLD
@----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
3dc3a9010      1565  package body SIM.PK_XXX
3d9571de8         3  anonymous block

FULL OUTER JOIN : Analysis

create table wwt_test_config ( org_id number, item_id number, quantity number);
create table wwt_test_lpn ( org_id number, item_id number, quantity number);

Case 1

insert into wwt_test_config VALUES ( 2434, 1234, 1);
insert into wwt_test_config VALUES ( 2434, 5678, 2);
insert into wwt_test_config VALUES ( 2434, 8761, 6);
insert into wwt_test_config VALUES ( 2434, 4321, 7);



insert into wwt_test_lpn VALUES ( 2434, 1234, 1);
insert into wwt_test_lpn VALUES ( 2434, 5678, 2);
insert into wwt_test_lpn VALUES ( 2434, 8761, 6);
insert into wwt_test_lpn VALUES ( 2434, 4321, 7);
insert into wwt_test_lpn VALUES ( 2434, 9999, 17);


ORG_ID ITEM_ID QUANTITY ORG_ID_1 ITEM_ID_1 QUANTITY_1
2434 1234 1                 2434 1234 1
2434 5678 2                 2434 5678 2
2434 8761 6                 2434 8761 6
2434 4321 7                 2434 4321 7
                                2434 9999 17



Case 2

insert into wwt_test_config VALUES ( 2434, 1234, 1);
insert into wwt_test_config VALUES ( 2434, 5678, 2);
insert into wwt_test_config VALUES ( 2434, 8761, 6);
insert into wwt_test_config VALUES ( 2434, 4321, 7);
insert into wwt_test_config VALUES ( 2434, 9999, 17);


insert into wwt_test_lpn VALUES ( 2434, 1234, 1);
insert into wwt_test_lpn VALUES ( 2434, 5678, 2);
insert into wwt_test_lpn VALUES ( 2434, 8761, 6);
insert into wwt_test_lpn VALUES ( 2434, 4321, 7);


ORG_ID ITEM_ID QUANTITY ORG_ID_1 ITEM_ID_1 QUANTITY_1
2434 1234 1                  2434 1234 1
2434 5678 2                  2434 5678 2
2434 8761 6                  2434 8761 6
2434 4321 7                  2434 4321 7
2434 9999 17



Case 3

insert into wwt_test_config VALUES ( 2434, 1234, 1);
insert into wwt_test_config VALUES ( 2434, 5678, 2);
insert into wwt_test_config VALUES ( 2434, 8761, 6);
insert into wwt_test_config VALUES ( 2434, 4321, 7);
insert into wwt_test_config VALUES ( 2434, 9999, 17);


insert into wwt_test_lpn VALUES ( 2434, 1234, 1);
insert into wwt_test_lpn VALUES ( 2434, 5678, 2);
insert into wwt_test_lpn VALUES ( 2434, 8761, 6);
insert into wwt_test_lpn VALUES ( 2434, 4321, 7);
insert into wwt_test_lpn VALUES ( 2434, 1111, 17);

ORG_ID ITEM_ID QUANTITY ORG_ID_1 ITEM_ID_1 QUANTITY_1
2434 1234 1                 2434 1234 1
2434 5678 2                 2434 5678 2
2434 8761 6                 2434 8761 6
2434 4321 7                 2434 4321 7
                                2434 1111 17
2434 9999 17