eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IBY_FD_EXTRACT_GEN_PVT dependencies on XMLAGG

Line 725: SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))

721: l_to_date VARCHAR2(255);
722: l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Pos_Pay_Extract_1_0';
723:
724: CURSOR l_pospay_ins_csr IS
725: SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))
726: FROM
727: iby_xml_fd_pmt_1_0_v xml_pmt_lvl
728: WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
729: AND xml_pmt_lvl.payment_status = 'ISSUED'

Line 733: SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))

729: AND xml_pmt_lvl.payment_status = 'ISSUED'
730: AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL);
731:
732: CURSOR l_pospay_appp_csr (p_to_date IN VARCHAR2) IS
733: SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))
734: FROM
735: iby_xml_fd_pmt_1_0_v xml_pmt_lvl
736: WHERE xml_pmt_lvl.payment_profile_id = p_payment_profile_id
737: AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)

Line 906: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))

902: x_extract_doc_11i CLOB;
903:
904: --cursor for - pmt instr id supplied,negotiable payments, reselect - no
905: CURSOR l_pospay_ins_csr_1_1 IS
906: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
907: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
908: WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
909: AND xml_pmt_lvl.payment_status IN ('ISSUED','PAID')
910: AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)

Line 915: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))

911: ;
912:
913: --cursor for - pmt instr id supplied,negotiable payments, reselect - yes
914: CURSOR l_pospay_ins_csr_1_2 IS
915: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
916: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
917: WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
918: AND xml_pmt_lvl.payment_status IN ('ISSUED','PAID')
919: ;

Line 923: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))

919: ;
920:
921: --cursor for - pmt instr id supplied,voided payments, reselect - no
922: CURSOR l_pospay_ins_csr_2_1 IS
923: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
924: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
925: WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
926: AND xml_pmt_lvl.payment_status IN('VOID')
927: AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)

Line 932: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))

928: ;
929:
930: --cursor for - pmt instr id supplied,voided payments, reselect - yes
931: CURSOR l_pospay_ins_csr_2_2 IS
932: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
933: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
934: WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
935: AND xml_pmt_lvl.payment_status IN('VOID')
936: ;

Line 940: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))

936: ;
937:
938: --cursor for - pmt instr id supplied,negotiable and voided payments, reselect - no
939: CURSOR l_pospay_ins_csr_3_1 IS
940: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
941: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
942: WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
943: AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
944: AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)

Line 949: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))

945: ;
946:
947: --cursor for - pmt instr id supplied,negotiable and voided payments, reselect - yes
948: CURSOR l_pospay_ins_csr_3_2 IS
949: SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
950: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
951: WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
952: AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
953: ;

Line 959: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))

955:
956:
957: --cursor for - pmt instr id not supplied,negotiable payments, reselect - no
958: CURSOR l_pospay_appp_csr_1_1 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
959: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
960: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
961: WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
962: AND ppp.positive_pay_format_code IN
963: (SELECT ppfformat.format_code

Line 978: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))

974: ;
975:
976: --cursor for - pmt instr id not supplied,negotiable payments, reselect - yes
977: CURSOR l_pospay_appp_csr_1_2 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
978: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
979: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
980: WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
981: AND ppp.positive_pay_format_code IN
982: (SELECT ppfformat.format_code

Line 996: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))

992: ;
993:
994: --cursor for - pmt instr id not supplied,voided payments, reselect - no
995: CURSOR l_pospay_appp_csr_2_1 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
996: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
997: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
998: WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
999: AND ppp.positive_pay_format_code IN
1000: (SELECT ppfformat.format_code

Line 1015: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))

1011: ;
1012:
1013: --cursor for - pmt instr id not supplied,voided payments, reselect - yes
1014: CURSOR l_pospay_appp_csr_2_2 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
1015: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
1016: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
1017: WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
1018: AND ppp.positive_pay_format_code IN
1019: (SELECT ppfformat.format_code

Line 1033: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))

1029: ;
1030:
1031: --cursor for - pmt instr id not supplied,negotiable and voided payments, reselect - no
1032: CURSOR l_pospay_appp_csr_3_1 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
1033: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
1034: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
1035: WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
1036: AND ppp.positive_pay_format_code IN
1037: (SELECT ppfformat.format_code

Line 1052: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))

1048: ;
1049:
1050: --cursor for - pmt instr id not supplied,negotiable and voided payments, reselect - yes
1051: CURSOR l_pospay_appp_csr_3_2 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
1052: SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
1053: FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
1054: WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
1055: AND ppp.positive_pay_format_code IN
1056: (SELECT ppfformat.format_code

Line 1069: SELECT xmltype.getclobval(xmlagg(pmt.payment))

1065: WHERE ba.bank_account_name = p_internal_bank_account_name)
1066: ;
1067:
1068: CURSOR l_pospay_appp_csr_11i (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
1069: SELECT xmltype.getclobval(xmlagg(pmt.payment))
1070: FROM IBY_XML_FD_11iPMT_1_0_V pmt
1071: WHERE pmt.internal_bank_account_id IN (SELECT ba.bank_account_id FROM ce_bank_accounts ba WHERE ba.bank_account_name = p_internal_bank_account_name)
1072: AND pmt.payment_date >= NVL(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
1073: AND pmt.payment_date <= NVL(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)

Line 2807: SELECT XMLAgg(

2803: l_fv_treasury_symbol_agg XMLTYPE;
2804: l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_FVFieldsAgg';
2805:
2806: CURSOR l_fv_treasury_symbol_csr (p_payment_instruction_id IN NUMBER) IS
2807: SELECT XMLAgg(
2808: XMLElement("TreasurySymbol",
2809: XMLElement("Name", fv.treasury_symbol),
2810: XMLElement("Amount",
2811: XMLElement("Value", fv.amount),

Line 2861: SELECT XMLAgg(xml_pmt_lvl.payment)

2857:
2858:
2859: -- for payment format: normal and reprint entire instruction
2860: CURSOR l_payerinstr_csr (p_payment_instruction_id IN NUMBER) IS
2861: SELECT XMLAgg(xml_pmt_lvl.payment)
2862: FROM
2863: iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
2864: IBY_PAY_INSTRUCTIONS_ALL ins
2865: WHERE

Line 2874: SELECT XMLAgg(xml_pmt_lvl.payment)

2870: ins.process_type = 'IMMEDIATE');
2871:
2872: -- for payment format: reprint individual and ranges
2873: CURSOR l_payerinstr_reprt_csr (p_payment_instruction_id IN NUMBER) IS
2874: SELECT XMLAgg(xml_pmt_lvl.payment)
2875: FROM
2876: iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
2877: IBY_PAY_INSTRUCTIONS_ALL ins
2878: WHERE

Line 2888: SELECT XMLAgg(xml_pmt_lvl.payment)

2884:
2885: -- for payment instruction register
2886: -- we are extract payments in all statuses
2887: CURSOR l_payerinstr_rpt_csr (p_payment_instruction_id IN NUMBER) IS
2888: SELECT XMLAgg(xml_pmt_lvl.payment)
2889: FROM
2890: iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2891: WHERE
2892: xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id;

Line 2897: SELECT XMLAgg(xml_pmt_lvl.DOCS_AGG)

2893:
2894:
2895: -- for AMEX REMITTANCE PROGRAM
2896: CURSOR l_payerinstr_amex_csr (p_payment_instruction_id IN NUMBER) IS
2897: SELECT XMLAgg(xml_pmt_lvl.DOCS_AGG)
2898: FROM
2899: IBY_XML_FD_AMEX_PMT_1_0_V xml_pmt_lvl
2900: WHERE
2901: xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id

Line 2907: SELECT XMLAgg(xml_pmt_lvl.payment)

2903:
2904:
2905: -- for other auxiliary formats
2906: CURSOR l_payerinstr_aux_csr (p_payment_instruction_id IN NUMBER) IS
2907: SELECT XMLAgg(xml_pmt_lvl.payment)
2908: FROM
2909: iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2910: WHERE
2911: xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id

Line 2917: SELECT XMLAgg(xml_pmt_lvl.payment)

2913: 'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED', 'PAID');
2914:
2915: -- for separate remittance advice electronic delivery: email and fax
2916: CURSOR l_payerinstr_sra_ele_csr (p_payment_instruction_id IN NUMBER) IS
2917: SELECT XMLAgg(xml_pmt_lvl.payment)
2918: FROM
2919: iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2920: WHERE
2921: xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id

Line 2927: SELECT XMLAgg(xml_pmt_lvl.payment)

2923: -- note the status qualification is done in Java CP main driver cursor
2924:
2925: -- for separate remittance advice print delivery
2926: CURSOR l_payerinstr_sra_prt_csr (p_payment_instruction_id IN NUMBER, p_from_pmt_ref IN NUMBER, p_to_pmt_ref IN NUMBER) IS
2927: SELECT XMLAgg(xml_pmt_lvl.payment)
2928: FROM
2929: iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2930: WHERE
2931: xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id

Line 3423: SELECT XMLAgg(account_setting)

3419: IS
3420: l_acctsettings_agg XMLTYPE;
3421:
3422: CURSOR l_acctsettings_csr (p_bep_account_id IN NUMBER) IS
3423: SELECT XMLAgg(account_setting)
3424: FROM iby_xml_fd_acct_settings_1_0_v
3425: WHERE bep_account_id = p_bep_account_id;
3426:
3427: BEGIN

Line 3446: SELECT XMLAgg(doc_payable)

3442: l_docpayable_agg XMLTYPE;
3443: l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Pmt_DocPayableAgg';
3444:
3445: CURSOR l_docpayable_csr (p_payment_id IN NUMBER) IS
3446: SELECT XMLAgg(doc_payable)
3447: FROM iby_xml_fd_doc_1_0_v
3448: WHERE formatting_payment_id = p_payment_id --bug 7006504
3449: AND document_status <> 'REMOVED';
3450:

Line 3454: SELECT XMLAgg(doc_payable)

3450:
3451: -- bug 11839898
3452: -- void by overflow documents should be printed under the real paying check
3453: CURSOR l_docpayable_sra_csr (p_payment_id IN NUMBER) IS
3454: SELECT XMLAgg(doc_payable)
3455: FROM iby_xml_fd_doc_1_0_v
3456: WHERE payment_id = p_payment_id
3457: AND document_status <> 'REMOVED';
3458:

Line 3461: SELECT XMLAgg(doc_payable)

3457: AND document_status <> 'REMOVED';
3458:
3459: -- for ppr report we need to filter the docs by MOAC accessibility check
3460: CURSOR l_docpayable_ppr_rpt_csr (p_payment_id IN NUMBER) IS
3461: SELECT XMLAgg(doc_payable)
3462: FROM iby_xml_fd_doc_1_0_v xml_doc, iby_docs_payable_all doc, ce_security_profiles_v ce_sp
3463: WHERE xml_doc.payment_id = p_payment_id --bug 7459662
3464: AND xml_doc.document_payable_id = doc.document_payable_id
3465: AND ce_sp.organization_type = doc.org_type

Line 3470: SELECT XMLAgg(doc_payable)

3466: AND ce_sp.organization_id = doc.org_id
3467: AND xml_doc.document_status <> 'REMOVED';
3468:
3469: CURSOR l_docpayable_amex_csr (p_payment_id IN NUMBER) IS
3470: SELECT XMLAgg(doc_payable)
3471: FROM iby_xml_fd_doc_1_0_v
3472: WHERE formatting_payment_id = p_payment_id
3473: AND document_status = 'PAYMENT_CREATED';
3474:

Line 4076: SELECT XMLAgg(

4072: -- Bug 6321384 Added nvl to check for NULL values
4073: CURSOR l_docline_csr (p_document_payable_id IN NUMBER,
4074: p_conc_invalid_chars IN VARCHAR2,
4075: p_conc_replacement_chars IN VARCHAR2) IS
4076: SELECT XMLAgg(
4077: XMLElement("DocumentPayableLine",
4078: XMLElement("LineNumber", ail.line_number),
4079: XMLElement("PONumber", ph.segment1),
4080: XMLElement("LineType",

Line 4173: SELECT XMLAgg(

4169: p_call_app_doc_unique_ref2 IN ap_invoices_all.invoice_id%TYPE,
4170: p_doc_currency_code IN iby_docs_payable_all.document_currency_code%TYPE,
4171: p_calling_app_id IN iby_docs_payable_all.calling_app_id%TYPE)
4172: IS
4173: SELECT XMLAgg(
4174: XMLElement("DocumentPayableLine",
4175: XMLElement("LineNumber", ail.line_number),
4176: XMLElement("PONumber", ph.segment1),
4177: XMLElement("LineType",

Line 4697: SELECT XMLAgg(payment)

4693: IS
4694: l_ppr_pmt_agg XMLTYPE;
4695:
4696: CURSOR l_ppr_pmt_csr (p_payment_service_request_id IN NUMBER) IS
4697: SELECT XMLAgg(payment)
4698: FROM iby_xml_fd_pmt_1_0_v
4699: WHERE payment_service_request_id = p_payment_service_request_id;
4700:
4701: BEGIN

Line 4738: SELECT XMLAgg(doc_payable)

4734: IS
4735: l_docpayable_agg XMLTYPE;
4736:
4737: CURSOR l_baddoc_csr (p_payment_service_request_id IN NUMBER) IS
4738: SELECT XMLAgg(doc_payable)
4739: FROM iby_xml_fd_doc_1_0_v
4740: WHERE payment_service_request_id = p_payment_service_request_id
4741: AND payment_id is null;
4742: -- AND document_status in ('REJECTED', 'FAILED_VALIDATION');

Line 4783: SELECT XMLAgg(payment_error)

4779: IS
4780: l_pmterr_agg XMLTYPE;
4781: l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Pmt_PmtErrAgg';
4782: CURSOR l_pmterr_csr (p_payment_id IN NUMBER) IS
4783: SELECT XMLAgg(payment_error)
4784: FROM iby_xml_fd_pmt_err_1_0_v
4785: WHERE payment_id = p_payment_id;
4786:
4787: BEGIN

Line 4812: SELECT XMLAgg(doc_payable_error)

4808: IS
4809: l_docerr_agg XMLTYPE;
4810:
4811: CURSOR l_docerr_csr (p_document_payable_id IN NUMBER) IS
4812: SELECT XMLAgg(doc_payable_error)
4813: FROM iby_xml_fd_doc_err_1_0_v
4814: WHERE document_payable_id = p_document_payable_id;
4815:
4816: BEGIN

Line 5528: Select XMLAgg(

5524: l_card_num VARCHAR2(30);
5525: l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_CC_Num_For_Issuer_Pmt_Agg';
5526:
5527: CURSOR cc_num_ext_agg(p_card_num varchar2) is
5528: Select XMLAgg(
5529: XMLElement("CreditCardNumber",p_card_num))
5530: from dual;
5531:
5532: BEGIN