2 Replies Latest reply: Feb 4, 2013 2:49 AM by AlexAnd RSS

    Create a new Grouping in IBY_FD_EXTRACT_EXT_PUB

    user609291
      Hi,

      I need to create a new group in  IBY_FD_EXTRACT_EXT_PUB like below.

      eg:

      +*<OutboundPaymentInstruction>*+
      ---+
      +*<OutboundPayment>*+
      ---+
      +*<ExtendValue>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 1</OUTPUTVALUE>+
      +*</Extend>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 2</OUTPUTVALUE>+
      +*</Extend>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 3</OUTPUTVALUE>+
      +*</Extend>*+
      *</ExtendValue*

      +*<OutboundPayment>*+
      ---+
      +*<ExtendValue>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 1</OUTPUTVALUE>+
      +*</Extend>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 2</OUTPUTVALUE>+
      +*</Extend>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 3</OUTPUTVALUE>+
      +*</Extend>*+
      +*</ExtendValue>*+   


      I have got the Concatenated value inside a loop, but I need to split each of the values into 80 characters and then insert it into <OUTPUTVALUE>+
      So, if I have 240 characters, then I need it like+

      +*<OutboundPayment>*+
      ---+
      +*<ExtendValue>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 80 characters*</OUTPUTVALUE>*+
      +*</Extend>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 80 characters*</OUTPUTVALUE>*+
      +*</Extend>*+
      -- <Extend>+
      ---<OUTPUTVALUE>Concatenated Value - 80 characters*</OUTPUTVALUE>*+
      +*</Extend>*+
      +*</ExtendValue>*+







      What I have tried is

      FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER)
      RETURN XMLTYPE
      IS
      l_ins_ext_agg XMLTYPE;


      CURSOR get_invoices_cur(cp_payment_id in number) is
      SELECT IDP.document_payable_id DOCUMENT_PAYABLE_ID
      ,SUBSTR(IDP.calling_app_doc_ref_number,1,30) REFERENCE_NUMBER
      ,IDP.payment_amount PAYMENT_AMOUNT
      ,IDP.document_amount DOCUMENT_AMOUNT
      ,IDP.payment_curr_discount_taken DISCOUNT_AMOUNT
      ,SUBSTR(IDP.po_number,1,30) PO_NUMBER
      ,IPA.payment_amount PAYMENT_AMNT
      ,IPA.ext_branch_number EXT_BRANCH_NUMBER
      ,IPA.ext_bank_account_number EXT_BANK_ACCOUNT_NUMBER
      ,IPA.payee_name PAYEE_NAME
      ,IPA.int_bank_name INT_BANK_NAME
      ,IPA.payment_reference_number PAYMENT_REFERENCE_NUMBER
      ,IPA.creation_date CREATION_DATE
      ,IPA.payment_instruction_id PAYMENT_INSTRUCTION_ID
      FROM iby_docs_payable_all IDP
      ,ap_invoices_all AIA
      ,iby_payments_all IPA
      WHERE AIA.invoice_id = idp.calling_app_doc_unique_ref2
      AND IPA.payment_id = IDP.payment_id
      AND IDP.payment_id = cp_payment_id;

      BEGIN

      <Initialize all valiables>

      FOR get_invoices_rec IN get_invoices_cur(p_payment_id)
      LOOP

      lc_concatenate: = <Get the concanated Value as required>;

      lc_split:= < Written some logic to split it into char >

      BEGIN

      SELECT XMLConcat(
      XMLElement("Extend",
      XMLElement("lc_value1" lc_split))

      )
      INTO lc_extendval
      FROM dual

      END;

      END LOOP;

      BEGIN

      SELECT XMLConcat(
      XMLElement("ExtendVal",lc_extendval),
      )
      INTO l_ins_ext_agg
      FROM dual;
      END;

      RETURN l_ins_ext_agg;
      EXCEPTION
      WHEN OTHERS THEN
      RETURN NULL;
      END Get_Pmt_Ext_Agg;


      Now, my output comes only as the last 80 characters, I do not have the first 80 and second 80 characters.
      ANy inputs to achieve all the 240 characters split into 3 rows like described above would be helpful.

      Thanks
        • 1. Re: Create a new Grouping in IBY_FD_EXTRACT_EXT_PUB
          user609291
          Does anyone have any ideas/solution to achieve this??
          • 2. Re: Create a new Grouping in IBY_FD_EXTRACT_EXT_PUB
            AlexAnd
            >
            Now, my output comes only as the last 80 characters, I do not have the first 80 and second 80 characters.
            >
            based on
            >
            lc_split:= < Written some logic to split it into char >
            >
            so check logic for splitting

            i can't test on your data but

            >
            SELECT XMLConcat(
            XMLElement("Extend",
            XMLElement("lc_value1" lc_split))

            )
            INTO lc_extendval
            FROM dual
            >
            what's code doing?
            looks like it's wrong because it create tag "lc_value1"


            >
            BEGIN

            SELECT XMLConcat(
            XMLElement("ExtendVal",lc_extendval),
            )
            INTO l_ins_ext_agg
            FROM dual;
            END;

            RETURN l_ins_ext_agg;
            >
            what's code doing?


            so try another way
            FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER)
            RETURN XMLTYPE
            IS
            l_ins_ext_agg XMLTYPE;
            
            CURSOR get_invoices_cur(cp_payment_id in number) is
            SELECT IDP.document_payable_id DOCUMENT_PAYABLE_ID
            ,SUBSTR(IDP.calling_app_doc_ref_number,1,30) REFERENCE_NUMBER
            ,IDP.payment_amount PAYMENT_AMOUNT
            ,IDP.document_amount DOCUMENT_AMOUNT
            ,IDP.payment_curr_discount_taken DISCOUNT_AMOUNT
            ,SUBSTR(IDP.po_number,1,30) PO_NUMBER
            ,IPA.payment_amount PAYMENT_AMNT
            ,IPA.ext_branch_number EXT_BRANCH_NUMBER
            ,IPA.ext_bank_account_number EXT_BANK_ACCOUNT_NUMBER
            ,IPA.payee_name PAYEE_NAME
            ,IPA.int_bank_name INT_BANK_NAME
            ,IPA.payment_reference_number PAYMENT_REFERENCE_NUMBER
            ,IPA.creation_date CREATION_DATE
            ,IPA.payment_instruction_id PAYMENT_INSTRUCTION_ID
            FROM iby_docs_payable_all IDP
            ,ap_invoices_all AIA
            ,iby_payments_all IPA
            WHERE AIA.invoice_id = idp.calling_app_doc_unique_ref2
            AND IPA.payment_id = IDP.payment_id
            AND IDP.payment_id = cp_payment_id;
            
            BEGIN
            
            <Initialize all valiables>
            
            FOR get_invoices_rec IN get_invoices_cur(p_payment_id)
            LOOP
            
            lc_concatenate: = <Get the concanated Value as required>;
            and add logic for splitting like
            select xmlelement("ExtendValue", xmlagg(xmlelement("Extend", substr(lc_concatenate,1+80*(level-1),80) )))
            INTO lc_extendval
            from dual
            connect by level <= ceil(length(lc_concatenate)/80)
            so example
            SQL> 
            SQL> with t as
              2  (
              3  select 1 id, lpad('a',80,'a') ||lpad('b',80,'b') ||lpad('c',80,'c') str from dual
              4  )
              5  --
              6  select xmlelement("ExtendValue", xmlagg(xmlelement("Extend", substr(str,1+80*(level-1),80) )))
              7  from t
              8  connect by level <= ceil(length(str)/80)
              9  /
             
            XMLELEMENT("EXTENDVALUE",XMLAG
            --------------------------------------------------------------------------------
            <ExtendValue><Extend>aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
             
            SQL> 
            if you have some problem then plz post test data