XMLP Report Generated through PL SQL

Generate a XML Report from PL SQL Package.

CREATE OR REPLACE PACKAGE BODY XXORT_TEST_USER

AS

  PROCEDURE XXORT_USER (ERRBUF  VARCHAR2,

                        RETCODE  NUMBER,

                        XORDER_NUM IN VARCHAR2)

     IS

     CURSOR C_HEAD IS

        SELECT ORDER_NUMBER,

               HEADER_ID,

               FLOW_STATUS_CODE

         FROM OE_ORDER_HEADERS_ALL

         WHERE ORDER_NUMBER=XORDER_NUM;

        

     CURSOR C_LINES (P_HEADER_ID IN VARCHAR2)

     IS

        SELECT  ORDERED_ITEM, ORDERED_QUANTITY , LINE_ID

        FROM OE_ORDER_LINES_ALL

        WHERE HEADER_ID= P_HEADER_ID;

       

       

      LV_LOT_NUMBER VARCHAR2(100);

    

     BEGIN

    

       FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<?xml version="1.0" encoding="UTF-8"?>');

       FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDER_INFO>');

         

            FOR RC_HEAD IN C_HEAD

             LOOP

            

              FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<P_ORDER_HEADER>');

              FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDER_NUMBER>' || RC_HEAD.ORDER_NUMBER || '</ORDER_NUMBER>');

              FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<HEADER_ID>' || RC_HEAD.HEADER_ID || '</HEADER_ID>');

              FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<FLOW_STATUS_CODE>' || RC_HEAD.FLOW_STATUS_CODE || '</FLOW_STATUS_CODE>');

            

            

                  FOR RC_LINES IN C_LINES (RC_HEAD.HEADER_ID)

                         LOOP

                                 

                        

                         BEGIN

                        

                         SELECT LOT_NUMBER

                         INTO LV_LOT_NUMBER

                         FROM WSH_DELIVERY_DETAILS

                         WHERE SOURCE_LINE_ID = RC_LINES.LINE_ID;

                        

                         EXCEPTION

                         WHEN OTHERS

                         THEN NULL;

                        

                         END ;                

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<P_ORDER_LINES>');

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDERED_ITEM>' || RC_LINES.ORDERED_ITEM || '</ORDERED_ITEM>');

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDERED_QUANTITY>' || RC_LINES.ORDERED_QUANTITY ||  '</ORDERED_QUANTITY>');

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<LOT_NUMBER>' || LV_LOT_NUMBER ||  '</LOT_NUMBER>');

                         

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</P_ORDER_LINES>');

                        

                         END LOOP;

                    FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</P_ORDER_HEADER>');    

             END LOOP;

    

     FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</ORDER_INFO>');

     END ;

    

                       

                   

 

END XXORT_TEST_USER;