This discussion is archived
2 Replies Latest reply: Feb 4, 2013 12:49 AM by AlexAnd RSS

Create a new Grouping in IBY_FD_EXTRACT_EXT_PUB

user349065 - oracle Newbie
Currently Being Moderated
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
    user349065 - oracle Newbie
    Currently Being Moderated
    Does anyone have any ideas/solution to achieve this??
  • 2. Re: Create a new Grouping in IBY_FD_EXTRACT_EXT_PUB
    AlexAnd Guru
    Currently Being Moderated
    >
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points