[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Into_Lines_Gt
(
p_txn_id IN NUMBER
);
PROCEDURE Insert_Into_Headers_Gt
(
p_txn_id IN NUMBER
);
PROCEDURE Insert_Into_Gt
(
p_txn_id IN NUMBER
);
'select XMLElement("FundsCapturePayerNotification", ' ||
' XMLElement("FormatProgramRequestID", fnd_global.CONC_REQUEST_ID), ' ||
' XMLAgg(xml_order.FNDCPT_ORDER)) ' ||
' from iby_trxn_summaries_all txn, IBY_XML_FNDCPT_ORDER_PN_1_0_V xml_order ' ||
' where txn.trxnmid = xml_order.trxnmid ' ||
' and nvl(txn.payer_notification_required, ''N'') = ''Y'' ';
-- Calling Insert_Into_Gt procedure to include Document Receivable tag
-- in FC Payer Notification Extract.
FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Call to Insert_Into_Gt start, TimeStamp:' ||systimestamp);
Insert_Into_Gt(p_mbatchid);
FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Call to Insert_Into_Gt end, TimeStamp:' ||systimestamp);
FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Call to Insert_Into_Gt start, TimeStamp:' ||systimestamp);
Insert_Into_Gt(p_txn_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Call to Insert_Into_Gt end, TimeStamp:' ||systimestamp);
SELECT
XMLType.getClobVal(instruction)
INTO x_extract_doc
FROM iby_xml_batch_fci_1_0_v
WHERE mbatchid=p_txn_id
AND rownum=1;
SELECT
XMLType.getClobVal(instruction)
INTO x_extract_doc
FROM iby_xml_online_fci_1_0_v
WHERE trxnmid=p_txn_id
AND rownum=1;
SELECT
XMLElement("PayeeAccount",
XMLElement("PaymentSystemAccount",
XMLElement("AccountName",txn.bepkey),
Extract(XMLAgg(XMLElement("OptionSet",opts.account_options)),
'OptionSet[1]/*')
),
CASE WHEN (NOT xml_bank.instrid IS NULL) THEN
Extract(XMLAgg(XMLElement("BankAccount",xml_bank.bank_account_content)),
'/BankAccount[1]')
END,
XMLElement("Payee",
XMLElement("Name",payee.name),
XMLElement("Address",
XMLElement("AddressLine1",null),
XMLForest(null AS "AddressLine2",null AS "AddressLine3"),
XMLElement("City",null),
XMLElement("State",null),
XMLElement("Country",null),
XMLElement("PostalCode",null)
),
XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC")
),
XMLElement("OrderCount",count(txn.trxnmid)),
XMLElement("AccountTotals",
XMLElement("AuthorizationsTotal",
XMLElement("Value",
DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
'BANKACCOUNT', 0,
SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)) )),
XMLElement("Currency",
XMLElement("Code",MAX(txn.currencynamecode))
)
),
XMLElement("CapturesTotal",
XMLElement("Value",
DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)),
'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTREQ',txn.amount, 0)),
SUM(DECODE(txn.trxntypeid, 3,txn.amount, 8,txn.amount, 0)) )),
XMLElement("Currency",
XMLElement("Code",MAX(txn.currencynamecode))
)
),
XMLElement("CreditsTotal",
XMLElement("Value",
DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTCREDIT',txn.amount, 0)),
SUM(DECODE(txn.trxntypeid, 5,txn.amount, 11,txn.amount, 0)) )),
XMLElement("Currency",
XMLElement("Code",MAX(txn.currencynamecode))
)
)
),
XMLAgg(xml_order.fndcpt_order)
)--,
--txn.mbatchid,
--txn.payeeinstrid
FROM
iby_trxn_summaries_all txn,
iby_payee payee,
iby_bepkeys keys,
iby_xml_fndcpt_bankaccount_v xml_bank,
iby_xml_bep_acct_options_v opts,
iby_xml_fndcpt_order_1_0_v xml_order
WHERE (txn.payeeid = payee.payeeid)
AND (txn.payeeinstrid = xml_bank.instrid(+))
AND (txn.payeeid = keys.ownerid)
AND (txn.bepkey = keys.key)
AND (keys.ownertype = 'PAYEE')
AND (keys.bep_account_id = opts.bep_account_id(+))
AND (txn.trxnmid = xml_order.trxnmid)
AND txn.trxnmid = G_Extract_Run_Payment_id
AND txn.mbatchid = p_mbatch_id
GROUP BY
txn.mbatchid, txn.payeeinstrid, txn.instrtype, txn.bepkey,
payee.name, payee.mcc_code, opts.bep_account_id,
xml_bank.instrid;
SELECT
XMLElement("PayeeAccount",
XMLElement("PaymentSystemAccount",
XMLElement("AccountName",txn.bepkey),
Extract(XMLAgg(XMLElement("OptionSet",opts.account_options)),
'OptionSet[1]/*')
),
CASE WHEN (NOT xml_bank.instrid IS NULL) THEN
Extract(XMLAgg(XMLElement("BankAccount",xml_bank.bank_account_content)),
'/BankAccount[1]')
END,
XMLElement("Payee",
XMLElement("Name",payee.name),
XMLElement("Address",
XMLElement("AddressLine1",null),
XMLForest(null AS "AddressLine2",null AS "AddressLine3"),
XMLElement("City",null),
XMLElement("State",null),
XMLElement("Country",null),
XMLElement("PostalCode",null)
),
XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC")
),
XMLElement("OrderCount",count(txn.trxnmid)),
XMLElement("AccountTotals",
XMLElement("AuthorizationsTotal",
XMLElement("Value",
DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
'BANKACCOUNT', 0,
SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)) )),
XMLElement("Currency",
XMLElement("Code",MAX(txn.currencynamecode))
)
),
XMLElement("CapturesTotal",
XMLElement("Value",
DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)),
'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTREQ',txn.amount, 0)),
SUM(DECODE(txn.trxntypeid, 3,txn.amount, 8,txn.amount, 0)) )),
XMLElement("Currency",
XMLElement("Code",MAX(txn.currencynamecode))
)
),
XMLElement("CreditsTotal",
XMLElement("Value",
DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTCREDIT',txn.amount, 0)),
SUM(DECODE(txn.trxntypeid, 5,txn.amount, 11,txn.amount, 0)) )),
XMLElement("Currency",
XMLElement("Code",MAX(txn.currencynamecode))
)
)
),
XMLAgg(xml_order.fndcpt_order)
)--,
--txn.mbatchid,
--txn.payeeinstrid
FROM
iby_trxn_summaries_all txn,
iby_payee payee,
iby_bepkeys keys,
iby_xml_fndcpt_bankaccount_v xml_bank,
iby_xml_bep_acct_options_v opts,
iby_xml_fndcpt_order_1_0_v xml_order
WHERE (txn.payeeid = payee.payeeid)
AND (txn.payeeinstrid = xml_bank.instrid(+))
AND (txn.payeeid = keys.ownerid)
AND (txn.bepkey = keys.key)
AND (keys.ownertype = 'PAYEE')
AND (keys.bep_account_id = opts.bep_account_id(+))
AND (txn.trxnmid = xml_order.trxnmid)
AND Get_SRA_Attribute(txn.trxnmid, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
AND txn.mbatchid = p_mbatch_id
GROUP BY
txn.mbatchid, txn.payeeinstrid, txn.instrtype, txn.bepkey,
payee.name, payee.mcc_code, opts.bep_account_id,
xml_bank.instrid;
SELECT bat.instrument_type
FROM iby_trxn_summaries_all txn, iby_batches_all bat
WHERE txn.trxnmid = p_trxnmid
AND txn.mbatchid = bat.mbatchid;
SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
sys_pf.payer_notification_format, sys_pf.PAYER_NOTIFICATION_CONDITION, sys_pf.PN_COND_NUM_OF_RECEIPTS
FROM iby_trxn_summaries_all txn, iby_batches_all bat,
iby_fndcpt_sys_eft_pf_b sys_pf, iby_fndcpt_user_eft_pf_b user_pf
WHERE txn.trxnmid = p_trxnmid
AND txn.mbatchid = bat.mbatchid
AND bat.process_profile_code = user_pf.user_eft_profile_code
AND user_pf.sys_eft_profile_code = sys_pf.sys_eft_profile_code;
SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
sys_pf.payer_notification_format, sys_pf.PAYER_NOTIFICATION_CONDITION, sys_pf.PN_COND_NUM_OF_RECEIPTS
FROM iby_trxn_summaries_all txn, iby_batches_all bat,
iby_fndcpt_sys_cc_pf_b sys_pf, iby_fndcpt_user_cc_pf_b user_pf
WHERE txn.trxnmid = p_trxnmid
AND txn.mbatchid = bat.mbatchid
AND bat.process_profile_code = user_pf.user_cc_profile_code
AND user_pf.sys_cc_profile_code = sys_pf.sys_cc_profile_code;
SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
sys_pf.payer_notification_format, sys_pf.PAYER_NOTIFICATION_CONDITION, sys_pf.PN_COND_NUM_OF_RECEIPTS
FROM iby_trxn_summaries_all txn, iby_batches_all bat,
iby_fndcpt_sys_dc_pf_b sys_pf, iby_fndcpt_user_dc_pf_b user_pf
WHERE txn.trxnmid = p_trxnmid
AND txn.mbatchid = bat.mbatchid
AND bat.process_profile_code = user_pf.user_dc_profile_code
AND user_pf.sys_dc_profile_code = sys_pf.sys_dc_profile_code;
SELECT loc.language, loc.country
FROM hz_party_sites ps, hz_locations loc,
iby_trxn_summaries_all txn, hz_cust_site_uses_all hz_csu,
hz_cust_acct_sites_all hz_cs
where txn.trxnmid = p_trxnmid
and hz_csu.cust_acct_site_id = hz_cs.cust_acct_site_id
and hz_cs.party_site_id = ps.party_site_id
AND txn.acct_site_use_id = hz_csu.site_use_id(+)
AND loc.location_id = ps.location_id;
SELECT tangibleid
FROM IBY_TRXN_SUMMARIES_ALL
WHERE TRXNMID = p_trxnmid;
SELECT payer.debit_advice_delivery_method,
payer.debit_advice_email,
payer.debit_advice_fax
FROM iby_external_payers_all payer,
iby_trxn_summaries_all txn
WHERE payer.party_id = txn.payer_party_id
AND (payer.org_id is NULL OR (payer.org_id = txn.org_id AND payer.org_type = txn.org_type))
AND (payer.cust_account_id is NULL OR payer.cust_account_id = txn.cust_account_id)
AND (payer.acct_site_use_id is NULL OR payer.acct_site_use_id = txn.acct_site_use_id)
AND txn.trxnmid = p_trxnmid
ORDER BY payer.acct_site_use_id, payer.cust_account_id, payer.org_id;
SELECT bat.instrument_type
FROM iby_batches_all bat
WHERE bat.batchid = p_batchid;
SELECT sys_pf.PAYER_NOTIFICATION_FORMAT, sys_pf.ACCOMPANY_LETTER_FORMAT
FROM iby_batches_all bat,
iby_fndcpt_sys_eft_pf_b sys_pf, iby_fndcpt_user_eft_pf_b user_pf
WHERE bat.batchid = p_batchid
AND bat.process_profile_code = user_pf.user_eft_profile_code
AND user_pf.sys_eft_profile_code = sys_pf.sys_eft_profile_code;
SELECT sys_pf.PAYER_NOTIFICATION_FORMAT
FROM iby_batches_all bat,
iby_fndcpt_sys_cc_pf_b sys_pf, iby_fndcpt_user_cc_pf_b user_pf
WHERE bat.batchid = p_batchid
AND bat.process_profile_code = user_pf.user_cc_profile_code
AND user_pf.sys_cc_profile_code = sys_pf.sys_cc_profile_code;
SELECT sys_pf.PAYER_NOTIFICATION_FORMAT
FROM iby_batches_all bat,
iby_fndcpt_sys_dc_pf_b sys_pf, iby_fndcpt_user_dc_pf_b user_pf
WHERE bat.batchid = p_batchid
AND bat.process_profile_code = user_pf.user_dc_profile_code
AND user_pf.sys_dc_profile_code = sys_pf.sys_dc_profile_code;
PROCEDURE Update_Pmt_SRA_Attr_Prt
(
p_mbatchid IN VARCHAR2,
p_fromDate IN VARCHAR2,
p_toDate IN VARCHAR2,
p_fromPSON IN VARCHAR2,
p_toPSON IN VARCHAR2,
p_delivery_method IN VARCHAR2,
p_format_code IN VARCHAR2
)
IS
l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Update_Pmt_SRA_Attr_Prt';
l_update_stmt VARCHAR2(4000) :=
'UPDATE iby_trxn_summaries_all txn SET ' ||
' debit_advice_delivery_method = ''PRINTED'', ' ||
' debit_advice_email = null, ' ||
' debit_advice_fax = null, ' ||
' payer_notification_created = ''Y'', ' ||
' object_version_number = object_version_number + 1, ' ||
' last_updated_by = fnd_global.user_id, ' ||
' last_update_date = SYSDATE, ' ||
' last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id) ' ||
'WHERE nvl(txn.payer_notification_required, ''N'') = ''Y'' ';
l_update_stmt := l_update_stmt || l_where_clause;
iby_debug_pub.add(debug_msg => 'Before executing dynamic update statement.',
debug_level => FND_LOG.LEVEL_STATEMENT,
module => l_Debug_Module);
iby_debug_pub.add(debug_msg => 'l_update_stmt: ' || l_update_stmt,
debug_level => FND_LOG.LEVEL_STATEMENT,
module => l_Debug_Module);
EXECUTE IMMEDIATE l_update_stmt;
END Update_Pmt_SRA_Attr_Prt;
PROCEDURE Update_Pmt_SRA_Attr_Ele
(
p_trxnmid IN NUMBER,
p_delivery_method IN VARCHAR2,
p_recipient_email IN VARCHAR2,
p_recipient_fax IN VARCHAR2
)
IS
BEGIN
IF p_delivery_method = 'EMAIL' THEN
UPDATE
iby_trxn_summaries_all
SET
debit_advice_delivery_method = p_delivery_method,
debit_advice_email = p_recipient_email,
debit_advice_fax = null,
payer_notification_created = 'Y',
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
WHERE trxnmid = p_trxnmid;
UPDATE
iby_trxn_summaries_all
SET
debit_advice_delivery_method = p_delivery_method,
debit_advice_email = null,
debit_advice_fax = p_recipient_fax,
payer_notification_created = 'Y',
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
WHERE trxnmid = p_trxnmid;
UPDATE
iby_trxn_summaries_all
SET
debit_advice_delivery_method = p_delivery_method,
debit_advice_email = NULL,
debit_advice_fax = NULL,
payer_notification_created = 'Y',
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
WHERE trxnmid = p_trxnmid;
END Update_Pmt_SRA_Attr_Ele;
SELECT MIN(reqdate)
INTO l_reqdate
FROM iby_trxn_summaries_all
WHERE batchid = p_settlement_batch
AND NVL(payer_notification_required, 'N') = 'Y';
SELECT count(*) INTO l_count
FROM iby_debit_authorizations
WHERE initial_debit_authorization_id = ( SELECT initial_debit_authorization_id
FROM iby_debit_authorizations
WHERE debit_authorization_id = p_mandate_id );
SELECT iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.iban, ext_ba.iban_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'),
ibk.subkey_cipher_text, ibs.segment_cipher_text, ibs.encoding_scheme, ext_ba.ba_mask_setting, ext_ba.ba_unmask_length)
INTO l_iban
FROM iby_pmt_instr_uses_all iu
,iby_ext_bank_accounts ext_ba
,iby_sys_security_subkeys ibk
,iby_security_segments ibs
WHERE iu.instrument_payment_use_id = p_assign_id
AND iu.instrument_type = 'BANKACCOUNT'
AND iu.instrument_id = ext_ba.ext_bank_account_id
AND (ext_ba.iban_sec_segment_id = ibs.sec_segment_id(+))
AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+));
SELECT MandateDetails from (
SELECT debit_id, XMLElement("MandateDetails", XMLConcat( XMLElement("AuthorizationReference", curr_auth_ref)
, XMLElement("AuthorizationSignDate", curr_sign_date)
, XMLElement("AmendmentIndicator", amend_indicator) , XMLElement("OrgnlAuthReference", prev_auth_ref)
, XMLElement("OrgnlCreditor", prev_cred_name) , XMLElement("OrgnlCreditorId", prev_cred_id)
, XMLElement("IBAN", iban) , XMLElement("CreditorName", curr_cred_name), XMLElement("CreditorIdentifier", curr_cred_id) ) ) MandateDetails
FROM (
SELECT curr_mandate.debit_authorization_id debit_id
, curr_mandate.authorization_reference_number curr_auth_ref, curr_mandate.auth_sign_date curr_sign_date
,is_amended(curr_mandate.debit_authorization_id) amend_indicator
, prev_mandate.authorization_reference_number prev_auth_ref , prev_mandate.creditor_le_name prev_cred_name
, prev_mandate.creditor_identifier prev_cred_id
,get_assignment_iban(prev_mandate.external_bank_account_use_id) iban
,curr_mandate.creditor_le_name curr_cred_name , curr_mandate.creditor_identifier curr_cred_id
FROM iby_debit_authorizations curr_mandate , iby_debit_authorizations prev_mandate
WHERE curr_mandate.initial_debit_authorization_id = prev_mandate.initial_debit_authorization_id(+)
AND curr_mandate.debit_authorization_id <> prev_mandate.debit_authorization_id(+)
ORDER BY prev_mandate.authorization_revision_number DESC )
WHERE debit_id = c_mandate_id
AND ROWNUM < 2 );
PROCEDURE Insert_Into_Gt
(
p_txn_id IN NUMBER
)
IS
l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Into_Gt';
DELETE FROM IBY_AR_DOC_HEADER_GT;
DELETE FROM IBY_AR_DOC_LINES_GT;
Insert_Into_Headers_Gt(p_txn_id);
Insert_Into_Lines_Gt(p_txn_id);
END Insert_Into_Gt;
PROCEDURE Insert_Into_Headers_Gt
(
p_txn_id IN NUMBER
)
IS
TYPE dyn_header_select IS REF CURSOR;
l_header_cursor dyn_header_select;
l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Into_Headers_Gt';
SELECT payment_channel_code
INTO l_payment_channel_code
FROM iby_trxn_summaries_all txn
WHERE txn.mbatchid = p_txn_id
AND rownum = 1;
l_headers_query := 'SELECT
inv_header.calling_app_doc_ref_number,
inv_header.document_date,
inv_header.creation_date,
inv_header.document_type,
inv_header.document_description,
inv_header.document_amount,
inv_header.document_currency_code,
inv_header.settlement_amount,
inv_header.discount_amount,
inv_header.freight_amount,';
INSERT INTO IBY_AR_DOC_HEADER_GT VALUES l_DocHeadersTab(j);
select count(*) into l_count from IBY_AR_DOC_HEADER_GT;
END Insert_Into_Headers_Gt;
PROCEDURE Insert_Into_Lines_Gt
(
p_txn_id IN NUMBER
)
IS
TYPE dyn_lines_select IS REF CURSOR;
l_lines_cursor dyn_lines_select;
l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Into_Lines_Gt';
SELECT payment_channel_code
INTO l_payment_channel_code
FROM iby_trxn_summaries_all txn
WHERE txn.mbatchid = p_txn_id
AND rownum = 1;
l_lines_query := 'SELECT
inv_line.line_number,
inv_line.po_number,
inv_line.line_type,
inv_line.description,
inv_line.extended_amount,';
INSERT INTO IBY_AR_DOC_LINES_GT VALUES l_DocLinesTab(j);
select count(*) into l_count from IBY_AR_DOC_LINES_GT;
END Insert_Into_Lines_Gt;