[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REQ_STATUS_INSERTED CONSTANT VARCHAR2(100) := 'INSERTED';
| simply inserted into IBY tables.
|
| p_payment_function
| Payment function. Used in setting the payee context.
|
| p_internal_bank_account_id
| The internal bank account to pay from.
|
| p_pay_process_profile_id
| Payment process profile. The payment profile drives how this
| payment is processed in IBY.
|
| p_payment_method_cd
| The payment method.
|
| p_legal_entity_id
| Legal entity.
|
| p_organization_id
| Org id. Used in setting the payee context.
|
| p_organization_type
| Org type. Used in setting the payee context.
|
| p_payment_date
| The payment date.
| Currently not used.
|
| p_payment_amount
| The payment amount.
|
| p_payment_currency
| The payment currency.
|
| p_payee_party_id
| Payee party id. Used in setting the payee context.
|
| p_payee_party_site_id
| Payee party site id. Used in setting the payee context.
|
| p_supplier_site_id
| Supplier site id. Used in setting the payee context.
|
| p_payee_bank_account_id
| Payee bank account id. Only relevant for electronic single payments.
| Currently not used.
|
| p_override_pmt_complete_pt
| Override completion point flag. If this flag is set to 'Y', IBY
| will immediately mark the single payment as completed without
| waiting for the pre-set completion event.
|
| p_bill_payable_flag
| Indicates whether this payment is a future-dated payment.
| Currently not used.
|
| p_anticipated_value_date
| Anticipated value date.
| Currently not used.
|
| p_maturity_date
| Payment maturity date/
| Required parameter if the payment is a future-dated payment.
| Currently not used.
|
| p_payment_document_id
| The paper document (check stock) to be used for numbering and
| printing of the payment. Only relevant for printed payments.
| If not provided, this value will be derived from the payment
| process profile.
|
| p_paper_document_number
| The number of the paper document (check number). Only relevant
| for printed single payments. If this value is not provided
| the next available paper document number will be used.
|
| p_printer_name
| Printer name is required if the payment needs to be printed
| immediately.
|
| p_print_immediate_flag
| Whether to print the payment immediately. If set to N, user
| will have to initiate printing from the IBY UI.
|
| p_transmit_immediate_flag
| Flag indicating whether this payment needs to be transmitted
| to the bank immediately upon formatting. Only relevant for
| electronic payments. If this param is set to N, user will have
| to initiate transmission from the IBY UI.
|
| p_payee_address_line1 .. p_payee_address_line4
| Payee address lines. If payee address information is
| provided as API params, then these would be used to create
| the payment. If not provided, the payment would be stamped
| with the address information derived from payee party site id.
|
| p_payee_address_city
| Payee city.
|
| p_payee_address_county
| Payee county.
|
| p_payee_address_state
| Payee state.
|
| p_payee_address_zip
| Payee postal code.
|
| p_payee_address_country
| Payee country.
|
| p_attribute_category
| Descriptive flex fields category.
| Currently not used.
|
| p_attribute1 .. p_attribute15
| Descriptive flex field attributes.
| Currently not used.
|
| OUT
|
| x_num_printed_docs
| Total number of printed documents generated after numbering.
| This will include the actual single payment [1 document] plus
| any setup and overflow documents.
|
| x_payment_id
| Payment id of the actual single payment. This value maps to
| IBY_PAYMENTS_ALL.payment_id.
|
| x_paper_doc_num
| Paper document number of the actual single payment. This could be
| a check number, for example.
|
| x_pmt_ref_num
| Payment reference number stamped by IBY on the actual single
| payment. Use this payment reference number when interacting with
| third parties e.g., banks.
|
| x_return_status
| Return status of the API.
|
| S - Success
| E - Error / failure
| U - Unexpected / system error
|
| x_error_ids_tab
| List of validation error ids that map to
| IBY_TRANSACTION_ERRORS.transaction_error_id. Use these
| error ids to look up this table for list of validation errors.
|
| This parameter is only relevant when the return status is E.
|
| x_msg_count
| Generated FND messages count.
|
| x_msg_data
| Generated FND messages. This param is only relevant in case
| the return status is U. Unwind the message stack to see list
| of exceptions / system errors.
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE submit_single_payment(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_calling_app_id IN NUMBER,
p_calling_app_payreq_cd IN VARCHAR2,
p_is_manual_payment_flag IN VARCHAR2,
p_payment_function IN VARCHAR2,
p_internal_bank_account_id IN NUMBER,
p_pay_process_profile_id IN NUMBER,
p_payment_method_cd IN VARCHAR2,
p_legal_entity_id IN NUMBER,
p_organization_id IN NUMBER,
p_organization_type IN VARCHAR2,
p_payment_date IN DATE,
p_payment_amount IN NUMBER,
p_payment_currency IN VARCHAR2,
p_payee_party_id IN NUMBER,
p_payee_party_site_id IN NUMBER DEFAULT NULL,
p_supplier_site_id IN NUMBER DEFAULT NULL,
p_payee_bank_account_id IN NUMBER,
p_override_pmt_complete_pt IN VARCHAR2,
p_bill_payable_flag IN VARCHAR2,
p_anticipated_value_date IN DATE DEFAULT NULL,
p_maturity_date IN DATE,
p_payment_document_id IN NUMBER,
p_paper_document_number IN NUMBER,
p_printer_name IN VARCHAR2,
p_print_immediate_flag IN VARCHAR2,
p_transmit_immediate_flag IN VARCHAR2,
p_payee_address_line1 IN VARCHAR2 DEFAULT NULL,
p_payee_address_line2 IN VARCHAR2 DEFAULT NULL,
p_payee_address_line3 IN VARCHAR2 DEFAULT NULL,
p_payee_address_line4 IN VARCHAR2 DEFAULT NULL,
p_payee_address_city IN VARCHAR2 DEFAULT NULL,
p_payee_address_county IN VARCHAR2 DEFAULT NULL,
p_payee_address_state IN VARCHAR2 DEFAULT NULL,
p_payee_address_zip IN VARCHAR2 DEFAULT NULL,
p_payee_address_country IN VARCHAR2 DEFAULT NULL,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_attribute6 IN VARCHAR2 DEFAULT NULL,
p_attribute7 IN VARCHAR2 DEFAULT NULL,
p_attribute8 IN VARCHAR2 DEFAULT NULL,
p_attribute9 IN VARCHAR2 DEFAULT NULL,
p_attribute10 IN VARCHAR2 DEFAULT NULL,
p_attribute11 IN VARCHAR2 DEFAULT NULL,
p_attribute12 IN VARCHAR2 DEFAULT NULL,
p_attribute13 IN VARCHAR2 DEFAULT NULL,
p_attribute14 IN VARCHAR2 DEFAULT NULL,
p_attribute15 IN VARCHAR2 DEFAULT NULL,
x_num_printed_docs OUT NOCOPY NUMBER,
x_payment_id OUT NOCOPY NUMBER,
x_paper_doc_num OUT NOCOPY NUMBER,
x_pmt_ref_num OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_ids_tab OUT NOCOPY trxnErrorIdsTab,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2 (100);
SELECT
err.transaction_error_id
FROM
IBY_TRANSACTION_ERRORS err,
IBY_DOCS_PAYABLE_ALL doc,
IBY_PAY_SERVICE_REQUESTS prq
WHERE
err.transaction_id = doc.document_payable_id AND
err.transaction_type = TRXN_TYPE_DOC AND
doc.payment_service_request_id = prq.payment_service_request_id AND
prq.payment_service_request_id = p_payreq_id
;
SELECT
err.transaction_error_id
FROM
IBY_TRANSACTION_ERRORS err,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS prq
WHERE
err.transaction_id = pmt.payment_id AND
err.transaction_type = TRXN_TYPE_PMT AND
pmt.payment_service_request_id = prq.payment_service_request_id AND
prq.payment_service_request_id = p_payreq_id
;
SELECT
err.transaction_error_id
FROM
IBY_TRANSACTION_ERRORS err,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_INSTRUCTIONS_ALL ins,
IBY_PAY_SERVICE_REQUESTS prq
WHERE
err.transaction_id = ins.payment_instruction_id AND
err.transaction_type = TRXN_TYPE_INS AND
pmt.payment_service_request_id = prq.payment_service_request_id AND
pmt.payment_instruction_id = ins.payment_instruction_id AND
prq.payment_service_request_id = p_payreq_id
;
print_debuginfo(l_module_name, '|STEP 1: Insert Payment Service Request|');
* Insert payment request into IBY_PAY_SERVICE_REQUESTS
* table and generate payment request id.
*/
BEGIN
/*
* First check whether this is a duplicate request.
*
* In the case a duplicate request, this function will
* return the previously generated payment request id.
*
* In the case of a new request, this function will
* return 0
*/
l_payreq_id := IBY_DISBURSE_SUBMIT_PUB_PKG.
checkIfDuplicate(
p_calling_app_id,
p_calling_app_payreq_cd);
* Insert the payment request only if it is not a duplicate.
*/
IF (l_is_duplicate = FALSE) THEN
l_payreq_id := insert_payreq(
p_calling_app_id,
p_calling_app_payreq_cd,
p_internal_bank_account_id,
p_pay_process_profile_id,
p_is_manual_payment_flag
);
print_debuginfo(l_module_name, 'Could not insert payment '
|| 'service request for calling app id '
|| p_calling_app_id
|| ', calling app payment service request cd '
|| p_calling_app_payreq_cd
);
FND_MESSAGE.SET_NAME('IBY', 'IBY_SINGPAY_INSERT_FAILED');
* Payment service request as successfully inserted
* into the DB. Commit at this point.
*/
print_debuginfo(l_module_name, 'Payment service request '
|| 'inserted successfully into the database.'
|| 'Payment request id: '
|| l_payreq_id);
|| ' is a duplicate. Skipping insert of request '
);
* to insert documents payable of the manual payment.
*/
IF (UPPER(p_is_manual_payment_flag) = 'Y') THEN
print_debuginfo(l_module_name, 'This is a manual payment.');
|| 'not be inserted into database. Returning failure '
|| 'response.'
);
* Update the last issued document number in
* CE_PAYMENT_DOCUMENTS table if the user
* provided paper document number is greater
* than the existing last issued paper doc num.
*
* Calling the validate_paper_doc_number(..) with
* a null value will provide us the next available
* paper doc number. By subtracting 1 from this value
* we get the last issued paper doc number.
*/
print_debuginfo(l_module_name, 'Determining whether to update '
|| 'last issued paper doc num for provided check stock ..'
);
* Update the check stock to reflect the latest used
* check number.
*/
UPDATE
CE_PAYMENT_DOCUMENTS
SET
last_issued_document_number = l_paper_doc_num
WHERE
payment_document_id = p_payment_document_id
;
print_debuginfo(l_module_name, 'Updated CE_PAYMENT_DOCUMENTS '
|| 'table to use '
|| l_paper_doc_num
|| ' as last issued paper document number.'
);
|| ' will not be updated.'
);
* Insert the used document number into the used
* payment documents table.
*/
print_debuginfo(l_module_name, 'Inserting paper document '
|| 'number '
|| l_paper_doc_num
|| ' into IBY_USED_PAYMENT_DOCS table.'
);
INSERT INTO IBY_USED_PAYMENT_DOCS (
PAYMENT_DOCUMENT_ID,
USED_DOCUMENT_NUMBER,
DATE_USED,
DOCUMENT_USE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
VALUES (
p_payment_document_id,
l_paper_doc_num,
sysdate,
DOC_USE_ISSUED,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1
);
|| 'not be inserted into database. Returning failure '
|| 'response.'
);
* Insert manual payment into IBY_PAYMENTS_ALL
* table.
*/
IBY_PAYGROUP_PUB.insertPayments(l_pmts_tab);
* payment on this request and insert them as process
* functions and process orgs associated with this
* request.
*
* If this is not done, the UI will not allow the user
* to see the manual payment.
*/
/* process function for this manual payment */
l_process_func_rec.payment_function := p_payment_function;
INSERT INTO IBY_PROCESS_FUNCTIONS
(
object_id,
object_type,
payment_function
)
VALUES
(
l_process_func_rec.object_id,
l_process_func_rec.object_type,
l_process_func_rec.payment_function
)
;
INSERT INTO IBY_PROCESS_ORGS
(
object_id,
object_type,
org_id,
org_type
)
VALUES
(
l_process_org_rec.object_id,
l_process_org_rec.object_type,
l_process_org_rec.org_id,
l_process_org_rec.org_type
)
;
print_debuginfo(l_module_name, 'Finished inserting '
|| 'access types for this manual payment ..'
);
print_debuginfo(l_module_name, '|STEP 2: Insert Documents|');
* Insert the documents of this payment request into the
* IBY_DOCS_PAYABLE_ALL table.
*/
BEGIN
/*
* Insert the payment request documents only if the
* request is not a duplicate.
*/
IF (l_is_duplicate = FALSE) THEN
l_payreq_status := IBY_DISBURSE_SUBMIT_PUB_PKG.
get_payreq_status(l_payreq_id);
IF (l_payreq_status = REQ_STATUS_INSERTED) THEN
l_ret_status := IBY_DISBURSE_SUBMIT_PUB_PKG.
insert_payreq_documents(
p_calling_app_id,
p_calling_app_payreq_cd,
l_payreq_id
);
print_debuginfo(l_module_name, 'Could not insert '
|| 'documents payable for payment service '
|| 'request. Calling app id '
|| p_calling_app_id
|| ', calling app payment service request cd '
|| p_calling_app_payreq_cd
);
|| ' is a duplicate. Skipping insert of documents '
);
* inserted payment.
*/
IBY_PAYINSTR_PUB.createPaymentInstructions(
l_profile_attribs.processing_type,
p_payment_document_id,
p_printer_name,
p_print_immediate_flag,
l_transmit_now_flag,
NULL, /* admin assigned ref */
NULL, /* comments */
NULL, /* pmt profile id */
p_calling_app_id,
p_calling_app_payreq_cd,
l_payreq_id,
p_internal_bank_account_id,
p_payment_currency,
p_legal_entity_id,
p_organization_id,
p_organization_type,
NULL,
NULL,
'Y', /* single payments flow flag */
l_pmtInstrTab,
l_return_status,
x_msg_count,
x_msg_data
);
| insert_payreq
|
|
| PURPOSE:
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
FUNCTION insert_payreq (
p_calling_app_id IN IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
p_calling_app_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
call_app_pay_service_req_code%TYPE,
p_internal_bank_account_id
IN IBY_PAY_SERVICE_REQUESTS.
internal_bank_account_id%TYPE,
p_pay_process_profile_id
IN IBY_PAY_SERVICE_REQUESTS.
payment_profile_id%TYPE,
p_is_manual_payment_flag IN VARCHAR2
)
RETURN NUMBER
IS
l_payreq_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
l_module_name VARCHAR2(200) := G_PKG_NAME || '.insert_payreq';
* Insert the payment request into IBY_PAY_SERVICE_REQUESTS
* table. Supply defaults for values not provided by the
* calling app.
*/
INSERT INTO IBY_PAY_SERVICE_REQUESTS (
CALLING_APP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
CALL_APP_PAY_SERVICE_REQ_CODE,
PAYMENT_SERVICE_REQUEST_STATUS,
PAYMENT_SERVICE_REQUEST_ID,
PROCESS_TYPE,
INTERNAL_BANK_ACCOUNT_ID,
PAYMENT_PROFILE_ID,
ALLOW_ZERO_PAYMENTS_FLAG
)
VALUES(
p_calling_app_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1,
p_calling_app_payreq_cd,
REQ_STATUS_INSERTED,
l_payreq_id,
DECODE(p_is_manual_payment_flag, 'Y', 'MANUAL', 'IMMEDIATE'),
p_internal_bank_account_id,
p_pay_process_profile_id,
'Y'
);
|| 'inserting payment request status for '
|| 'calling app id '
|| p_calling_app_id
|| ', calling app payment service request cd '
|| p_calling_app_payreq_cd
);
END insert_payreq;
SELECT
pmt.payment_id,
pmt.payment_reference_number
INTO
x_payment_id,
x_pmt_ref_num
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_service_request_id = p_payreq_id AND
pmt.payment_status IN
(
PMT_STATUS_FORMATTED,
PMT_STATUS_INS_CREATED
)
;
* By selecting the payment with status
* INSTRUCTION_CREATED we will be picking up only
* the actual payment.
*/
SELECT
pmt.payment_id,
pmt.payment_reference_number,
pmt.paper_document_number
INTO
x_payment_id,
x_pmt_ref_num,
x_paper_doc_num
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_service_request_id = p_payreq_id AND
pmt.payment_status = PMT_STATUS_INS_CREATED
;
SELECT
count(*)
INTO
x_num_printed_docs
FROM
IBY_PAYMENTS_ALL
WHERE
payment_service_request_id = p_payreq_id AND
payment_status IN (
PMT_STATUS_INS_CREATED,
PMT_STATUS_SETUP,
PMT_STATUS_OVERFLOW)
;
SELECT
err.transaction_error_id
FROM
IBY_TRANSACTION_ERRORS err,
IBY_DOCS_PAYABLE_ALL doc,
IBY_PAY_SERVICE_REQUESTS prq
WHERE
err.transaction_id = doc.document_payable_id AND
err.transaction_type = TRXN_TYPE_DOC AND
doc.payment_service_request_id = prq.payment_service_request_id AND
prq.payment_service_request_id = p_payreq_id
;
SELECT
err.transaction_error_id
FROM
IBY_TRANSACTION_ERRORS err,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS prq
WHERE
err.transaction_id = pmt.payment_id AND
err.transaction_type = TRXN_TYPE_PMT AND
pmt.payment_service_request_id = prq.payment_service_request_id AND
prq.payment_service_request_id = p_payreq_id
;
SELECT
err.transaction_error_id
FROM
IBY_TRANSACTION_ERRORS err,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_INSTRUCTIONS_ALL ins,
IBY_PAY_SERVICE_REQUESTS prq
WHERE
err.transaction_id = ins.payment_instruction_id AND
err.transaction_type = TRXN_TYPE_INS AND
pmt.payment_service_request_id = prq.payment_service_request_id AND
pmt.payment_instruction_id = ins.payment_instruction_id AND
prq.payment_service_request_id = p_payreq_id
;
* Select the payment reference information from
* the IBY_PAYMENT_REFERENCES table.
*/
SELECT
NVL(last_used_ref_number, -1)
INTO
l_last_used_ref_num
FROM
IBY_PAYMENT_REFERENCES
FOR UPDATE
;
* Update the last used ref number and commit. So that
* other concurrent instances, now get the updated last
* used ref number.
*/
UPDATE
IBY_PAYMENT_REFERENCES
SET
last_used_ref_number = l_anticipated_last_ref_num;