Check Print for multiple invoices

Generally in one check print one invoice information is very easy. But problem arises when a supplier pays multiple invoices in one check. 20-30 invoice still can be managed. But if it is more than 40 invoices then in a single check it can not be managed. Either it has o be restricted or Dummy check has to be printed in new page. Restriction also possible from payment profile or it has to be controlled in the rtf.

Assuming maximum 40 lines will be printed. If 3 invoices paid in one check that means 37 dummy lines has to be printed. So that gap can be maintained.

Solution 1:

<?for-each:OutboundPayment?>

--Set a variable with 0

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

       <?for-each:DocumentPayable?>

        ---print the Invoice, payment information 

        -- before the end of group increment with one

       <?xdoxslt:set_variable($_XDOCTX, 'y', xdoxslt:get_variable($_XDOCTX, 'y')+1)?> 

        <?End for-each?> 

        ---print 37 dummy lines

        <?for-each:xdoxslt:foreach_number($_XDOCTX,1,40-xdoxslt:get_variable($_XDOCTX, 'y'),1)?> 

         ----Dummy lines

        <?End for-each?>

<?End for-each?>

Solution 2: ---If more than 40 lines and dummy check has to be printed in new page

<?for-each:OutboundPayment?>

--Set a variable with 0

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

       <?for-each:DocumentPayable?>

       <?if:xdoxslt:get_variable($_XDOCTX, 'x') < 41?>

        ---print the Invoice, payment information 

       

       <?end if?>

         -- before the end of group increment with one

        <?xdoxslt:set_variable($_XDOCTX, 'y', xdoxslt:get_variable($_XDOCTX, 'y')+1)?> 

        <?End for-each?> 

        ---print 37 dummy lines

        <?for-each:xdoxslt:foreach_number($_XDOCTX,1,40-xdoxslt:get_variable($_XDOCTX, 'y'),1)?> 

         ----Dummy lines

        <?End for-each?>

<?End for-each?>

***In the new page****

<?for-each:DocumentPayable?>

<?if:xdoxslt:get_variable($_XDOCTX, 'y') > 40?>

  ---print the Invoice, payment information 

?end if?>

 -- before the end of group increment with one

 <?xdoxslt:set_variable($_XDOCTX, 'y', xdoxslt:get_variable($_XDOCTX, 'y')+1)?> 

  <?End for-each?> 

Multiple level of approval using AME

Multiple level of approval using AME

• Define attributes as per the requirement 
• Define conditions as per the requirement 
• Define approver group
  Give Name and Description
  Select Order Number-1
  Select Voting method-Serial
  Select Usage Type-Dynamic

Write Query

SELECT * FROM (SELECT xx_approval_pkg.approval_insert('REQAPPRV',10,:transactionId) aa FROM DUAL
union all
SELECT xx_approval_pkg.approval_insert('REQAPPRV',20,:transactionId) aa FROM DUAL
union all
SELECT xx_approval_pkg.approval_insert('REQAPPRV',30,:transactionId) aa FROM DUAL
union all
SELECT xx_approval_pkg.approval_insert('REQAPPRV',40,:transactionId) aa FROM DUAL) x WHERE aa <> 'PERSON_ID:0'

• Define Action Type
• Define Rule.

Logic behind my program,
Query will call xx_approval_pkg.approval_insert package.function
here, REQAPPRV --means Requisition approval--Can pass any value(EXPENSE,INVOICE,REQAPPRV)
Sequence -10,20,30,40 ---Can send any number of sequences(I tested with upto 100)
:transactionId is the requisition header id for Requosition.

If sequence is 10, program will create hierarchy information and will store in a tem table xxvir.xxvirtu_doc_supervisor and return personid for sequence 10 only. next sequence onwards only return personid (no hierachy creation). 
If for any sequence value does not retrun any value that means it will retrun personid=0.

xxvir.xxvirtu_doc_supervisor table values
seq --- person_id---itemtype---itemkey
10----1001-----REQAPPRV---XXX
20---1002---REQAPPRV---XXX
30---1003---REQAPPRV---XXX.


FUNCTION approval_insert (
      p_itemtype      VARCHAR2,
      p_itemkey       VARCHAR2,
      p_sequence      NUMBER,
      p_document_id   NUMBER
   )
      RETURN VARCHAR2
   AS
      v_person_id   NUMBER;
   --PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      IF p_sequence = 10
      THEN
         IF p_itemtype = 'EXPENSE'
         THEN
            exp_approval_list_insert (p_itemtype         => 'EXPENSE',
                                      p_itemkey          => NULL,
                                      p_document_id      => p_document_id
                                     );
         ELSIF p_itemtype = 'INVOICE'
         THEN
            inv_approval_list_insert (p_itemtype         => 'INVOICE',
                                      p_itemkey          => NULL,
                                      p_document_id      => p_document_id
                                     );
         ELSIF p_itemtype = 'REQAPPRV'
         THEN
            req_approval_list_insert (p_itemtype         => 'REQAPPRV',
                                      p_itemkey          => NULL,
                                      p_document_id      => p_document_id
                                     );
         END IF;
      --RETURN 0;
      END IF;

      --ELSE
      BEGIN
         SELECT person_id
           INTO v_person_id
           FROM xxvir.xxvirtu_doc_supervisor
          WHERE itemtype = p_itemtype
            AND document_id = p_document_id
            AND sequence_number = p_sequence;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_person_id := 0;
            RETURN ('PERSON_ID:' || v_person_id);
      END;

      --RETURN v_person_id;
      RETURN ('PERSON_ID:' || v_person_id);
   -- END IF;
   END;

Based on the document type and document id program will generate the hierarchy and will store the person id value in  xxvir.xxvirtu_doc_supervisor table.

AME query will return like 1001,1002,1003 (person ids) and then approval message will go to first 1001, once it approves the approval message will go to 1002 and then to1003. Finally if 1003 approves then the requisition will be approved.

PO Output for Communication

The easiest way is to create a custom RTF, register it in XML publisher and attach it to the concurrent PO OUTPUT FOR COMMUNICATION.

• In Purchasing > Setup > Organizations > Purchasing Options> tab Control > set "PO Output Format" = PDF
• In Purchasing > Setup > Purchasing > Document Types > find Document Type "Purchase Order - Standard".Then find the Document Type Layout 'Standard Purchase Order Stylesheet' which is actually Template Defination. 
• Go to XML publisher and find the data defination 'Standard Purchase Order Data Source'(PO_STANDARD_PO). 
• Define your own template definition using the data definition and attach the custom template file.
• Change the new template name in the document type.

To generate the XMl Tag , do the following

Choose the `PO Output for Communication' as report name. 

In the parameter section please enter the following parameters and submit the request. 

Print Selection = All 

Buyer name, Employee Number: Choose the buyer name (who created the PO) 

Purchase Order Numbers From = PO Num 

Purchase Order Numbers To = PO Num 

Test = Debug (Running with Test flag as 'Debug' will only produce XML output if 

the PDF had not been generated successfully previously.) 

Leave all other parameters blank. 

Note: The document should be in status Incomplete otherwise it won't generate the readable XML.

The report will be completed with error status, but log file of the request 

will show the XML generated by this program run.

Authentication failed error in the notification attachment

Authentication failed error

Report output send as an attachment in the notification. An user first time see the notification and open the attachment. But from the second time user can not open the attachment. It gives the error 'Authentication failed error'

Solution:The attachment output files stores in the temporary file fnd_file_temp. 

Once you view this file and then close the file , system automatically delete the record from the table fnd_file_temp. So second time onwards no file found in the table.That is why it gives error 'Authentication failed'

 

Go to package fnd_webfilepub and comment out all the 'delete from fnd_file_temp' and check.

As you are commenting the code 'delete from fnd_file_temp'  So the all attachment files will not be deleted.

In that case it is better to delete the fnd_file_temp periodically.

Steps to remove the locked database object

--get object id
SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'XX_RES_ALLOC_DM%';

select * from all_objects where object_id IN (323889) ;

--find locked or not , if found then find session id
select * from v$locked_object;

 WHERE object_id IN (1193375,1169419,1169514)  ;        

---from the session id(SID), find serail
select * from v$session where sid=3250;

---kill the session 'session id, serail '
alter system kill session (:sid, :SERIAL#) ;
alter system kill session '3120,9499' immediate;