The following lines contain the word 'select', 'insert', 'update' or 'delete':
REQ_STATUS_INSERTED CONSTANT VARCHAR2(100) := 'INSERTED';
SELECT DISTINCT payment_profile_id, internal_bank_account_id /*Internal Bank Account ID Added for Bug 14122666*/
FROM iby_docs_payable_all
WHERE payment_service_request_id = l_payrequest_id;
print_log(l_module_name, '|STEP 1: Insert Payment Service Request:: Timestamp:'||systimestamp ||'|');
* 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 := 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_payment_profile_id,
p_allow_zero_payments_flag,
p_maximum_payment_amount,
p_minimum_payment_amount,
p_document_rejection_level,
p_payment_rejection_level,
p_review_proposed_pmts_flag,
p_create_instrs_flag,
p_payment_document_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15
);
print_log(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
);
x_errbuf := 'BUILD PROGRAM ERROR - CANNOT INSERT '
|| 'PAYMENT SERVICE REQUEST';
FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_REQ_INSERT_ERROR');
* Payment service request as successfully inserted
* into the DB. Commit at this point.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
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 '
);
print_log(l_module_name, '|STEP 2: Insert Documents :Timestamp:'||systimestamp ||'|');
* 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 := get_payreq_status(l_payreq_id);
IF (l_payreq_status = REQ_STATUS_INSERTED) THEN
--ebs_pga_mem('Before insert_payreq_documents');
l_ret_status := insert_payreq_documents(p_calling_app_id,
p_calling_app_payreq_cd,
l_payreq_id
);
print_log(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
);
x_errbuf := 'BUILD PROGRAM ERROR - CANNOT INSERT '
|| 'DOCUMENTS FOR PAYMENT SERVICE REQUEST';
FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_DOC_INSERT_ERROR');
update_payreq_status(l_payreq_id,
REQ_STATUS_BUILD_ERROR,
l_return_status);
* Payment service request documents successfully inserted
* into the DB. Commit at this point.
*/
COMMIT;
|| ' is a duplicate. Skipping insert of documents '
);
SELECT payment_service_request_id
INTO l_req_id
FROM iby_pay_service_requests
WHERE payment_service_request_id = l_payreq_id
AND payment_service_request_status = l_payreq_status
FOR UPDATE SKIP LOCKED;
update_payreq_status(l_payreq_id,
REQ_STATUS_BUILD_ERROR,
l_return_status);
SELECT payment_service_request_id
INTO l_req_id
FROM iby_pay_service_requests
WHERE payment_service_request_id = l_payreq_id
AND payment_service_request_status = l_payreq_status
FOR UPDATE SKIP LOCKED;
update_payreq_status(l_payreq_id,
REQ_STATUS_BUILD_ERROR,
l_return_status);
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_RDY_FOR_VAL
WHERE
payment_service_request_id = l_payreq_id AND
document_status = DOC_STATUS_FAILED_VAL
;
update_payreq_status(l_payreq_id,
REQ_STATUS_BUILD_ERROR,
l_return_status);
SELECT payment_service_request_id
INTO l_req_id
FROM iby_pay_service_requests
WHERE payment_service_request_id = l_payreq_id
AND payment_service_request_status = l_payreq_status
FOR UPDATE SKIP LOCKED;
update_payreq_status(l_payreq_id,
REQ_STATUS_BUILD_ERROR,
l_return_status);
* Update the request status in the database so that
* it is visible to the payment creation module.
*/
UPDATE
IBY_PAY_SERVICE_REQUESTS
SET
payment_service_request_status =
REQ_STATUS_RETRY_PMT_CREAT
WHERE
payment_service_request_id = l_payreq_id;
|| ' updated in DB. '
);
|| 'account" status will be updated for request '
|| l_payreq_id
);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_CREATED
WHERE
payment_service_request_id = l_payreq_id AND
payment_status = PAY_STATUS_MOD_BNK_ACC
;
|| 'account" status will be updated for request '
|| l_payreq_id
);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_CREATED
WHERE
payment_service_request_id = l_payreq_id AND
payment_status = PAY_STATUS_MOD_BNK_ACC
;
UPDATE
IBY_PAY_SERVICE_REQUESTS
SET
payment_service_request_status = REQ_STATUS_PMT_CRTD
WHERE
payment_service_request_id = l_payreq_id;
print_debuginfo(l_module_name, 'Updated status of'
|| ' payment request '
|| l_payreq_id
|| ' to "created"'
);
update_payreq_status(l_payreq_id,
REQ_STATUS_BUILD_ERROR,
l_return_status);
SELECT
NVL(create_pmt_instructions_flag, 'N')
INTO
l_create_instrs_flag
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
PAYMENT_SERVICE_REQUEST_ID = l_payreq_id
;
SELECT
payment_document_id
INTO
l_payment_doc_id
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
PAYMENT_SERVICE_REQUEST_ID = l_payreq_id
;
SELECT Count(pmts.payment_id)
INTO l_pmts_invalid_pdoc
FROM iby_payments_all pmts, iby_payment_profiles prof
WHERE pmts.payment_profile_id = prof.payment_profile_id
AND prof.processing_type = 'PRINTED'
AND pmts.payment_service_request_id = l_payreq_id
AND NOT EXISTS (SELECT 'Default Doc in Sync with Acc'
FROM ce_payment_documents pdoc
WHERE pmts.internal_bank_account_id = pdoc.internal_bank_account_id
AND pdoc.payment_document_id = prof.default_payment_document_id);
SELECT COUNT(*) distinct_ppp_count
INTO l_distinct_ppp_count
FROM
( SELECT DISTINCT payment_profile_id
FROM iby_docs_payable_all
WHERE payment_service_request_id = l_payreq_id
) distinctppp;
SELECT payment_profile_id
INTO l_distinct_ppp
FROM iby_docs_payable_all
WHERE payment_service_request_id = l_payreq_id
AND ROWNUM = 1 ;
UPDATE iby_pay_service_requests
SET payment_profile_id = l_distinct_ppp
WHERE payment_service_request_id = l_payreq_id;
update_payreq_status(l_payreq_id,
REQ_STATUS_BUILD_ERROR,
l_return_status);
SELECT
payment_service_request_id
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
payment_service_request_status=c_status;
SELECT
payment_service_request_status
INTO
l_payreq_status
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
payment_service_request_id = l_payreq_id;
SELECT
'TRUE'
INTO
l_test
FROM
DUAL
WHERE
EXISTS
(
SELECT
payment_id
FROM
IBY_PAYMENTS_ALL
WHERE
payment_service_request_id = l_payreq_id AND
payment_status = PAY_STATUS_MODIFIED
)
;
SELECT
'TRUE'
INTO
l_test
FROM
DUAL
WHERE
EXISTS
(
SELECT
payment_id
FROM
IBY_PAYMENTS_ALL
WHERE
payment_service_request_id = l_payreq_id AND
payment_status = PAY_STATUS_MOD_BNK_ACC
)
;
| 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_payment_profile_id
IN IBY_PAY_SERVICE_REQUESTS.
payment_profile_id%TYPE,
p_allow_zero_payments_flag
IN IBY_PAY_SERVICE_REQUESTS.
allow_zero_payments_flag%TYPE,
p_maximum_payment_amount IN IBY_PAY_SERVICE_REQUESTS.
maximum_payment_amount%TYPE,
p_minimum_payment_amount IN IBY_PAY_SERVICE_REQUESTS.
minimum_payment_amount%TYPE,
p_doc_rej_level IN IBY_PAY_SERVICE_REQUESTS.
document_rejection_level_code%TYPE,
p_pmt_rej_level IN IBY_PAY_SERVICE_REQUESTS.
payment_rejection_level_code%TYPE,
p_revw_prop_pmts_flag IN IBY_PAY_SERVICE_REQUESTS.
require_prop_pmts_review_flag%TYPE,
p_create_instrs_flag IN IBY_PAY_SERVICE_REQUESTS.
create_pmt_instructions_flag%TYPE,
p_payment_document_id IN IBY_PAY_SERVICE_REQUESTS.
payment_document_id%TYPE,
p_attribute_category IN IBY_PAY_SERVICE_REQUESTS.
attribute_category%TYPE,
p_attribute1 IN IBY_PAY_SERVICE_REQUESTS.
attribute1%TYPE,
p_attribute2 IN IBY_PAY_SERVICE_REQUESTS.
attribute2%TYPE,
p_attribute3 IN IBY_PAY_SERVICE_REQUESTS.
attribute3%TYPE,
p_attribute4 IN IBY_PAY_SERVICE_REQUESTS.
attribute4%TYPE,
p_attribute5 IN IBY_PAY_SERVICE_REQUESTS.
attribute5%TYPE,
p_attribute6 IN IBY_PAY_SERVICE_REQUESTS.
attribute6%TYPE,
p_attribute7 IN IBY_PAY_SERVICE_REQUESTS.
attribute7%TYPE,
p_attribute8 IN IBY_PAY_SERVICE_REQUESTS.
attribute8%TYPE,
p_attribute9 IN IBY_PAY_SERVICE_REQUESTS.
attribute9%TYPE,
p_attribute10 IN IBY_PAY_SERVICE_REQUESTS.
attribute10%TYPE,
p_attribute11 IN IBY_PAY_SERVICE_REQUESTS.
attribute11%TYPE,
p_attribute12 IN IBY_PAY_SERVICE_REQUESTS.
attribute12%TYPE,
p_attribute13 IN IBY_PAY_SERVICE_REQUESTS.
attribute13%TYPE,
p_attribute14 IN IBY_PAY_SERVICE_REQUESTS.
attribute14%TYPE,
p_attribute15 IN IBY_PAY_SERVICE_REQUESTS.
attribute15%TYPE
)
RETURN NUMBER
IS
l_payreq_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
l_module_name CONSTANT 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,
ALLOW_ZERO_PAYMENTS_FLAG,
MAXIMUM_PAYMENT_AMOUNT,
MINIMUM_PAYMENT_AMOUNT,
INTERNAL_BANK_ACCOUNT_ID,
PAYMENT_PROFILE_ID,
DOCUMENT_REJECTION_LEVEL_CODE,
PAYMENT_REJECTION_LEVEL_CODE,
REQUIRE_PROP_PMTS_REVIEW_FLAG,
CREATE_PMT_INSTRUCTIONS_FLAG,
PAYMENT_DOCUMENT_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
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,
'STANDARD', -- hardcode to 'standard' in the build program
DECODE(
p_allow_zero_payments_flag, NULL, 'N', p_allow_zero_payments_flag
),
p_maximum_payment_amount,
p_minimum_payment_amount,
p_internal_bank_account_id,
p_payment_profile_id,
p_doc_rej_level,
p_pmt_rej_level,
p_revw_prop_pmts_flag,
p_create_instrs_flag,
p_payment_document_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15
);
|| '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
payment_service_request_id INTO l_payreq_id
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
calling_app_id = p_calling_app_id
AND
call_app_pay_service_req_code = p_calling_app_payreq_cd;
SELECT
ext_payee_id INTO l_payee_id
FROM
IBY_EXTERNAL_PAYEES_ALL
WHERE
payee_party_id = p_payee_party_id AND
payment_function = p_pmt_function AND
NVL(party_site_id, '0') = NVL(l_payee_party_site_id, '0') AND
NVL(supplier_site_id, '0') = NVL(p_supplier_site_id, '0') AND
NVL(org_id, '0') = NVL(p_org_id, '0') AND
NVL(org_type, '0') = NVL(p_org_type, '0') /*AND
--NVL(INACTIVE_DATE,SYSDATE) >= SYSDATE*/
;
SELECT
ext_payee_id INTO l_payee_id
FROM
IBY_EXTERNAL_PAYEES_ALL
WHERE
payee_party_id = p_payee_party_id AND
payment_function = p_pmt_function AND
NVL(party_site_id, '0') = NVL(l_payee_party_site_id, '0') AND
supplier_site_id IS NULL AND
org_id IS NULL AND
org_type IS NULL /*AND
NVL(INACTIVE_DATE,SYSDATE) >= SYSDATE*/
;
SELECT
ext_payee_id INTO l_payee_id
FROM
IBY_EXTERNAL_PAYEES_ALL
WHERE
payee_party_id = p_payee_party_id AND
payment_function = p_pmt_function AND
party_site_id IS NULL AND
supplier_site_id IS NULL AND
org_id IS NULL AND
org_type IS NULL /*AND
NVL(INACTIVE_DATE,SYSDATE) >= SYSDATE*/
;
SELECT
ext_payee_id INTO l_payee_id
FROM
IBY_EXTERNAL_PAYEES_ALL
WHERE
payee_party_id = p_payee_party_id AND
payment_function = p_pmt_function AND
NVL(party_site_id, '0') = NVL(l_payee_party_site_id, '0') AND
NVL(supplier_site_id, '0') = NVL(p_supplier_site_id, '0') AND
NVL(org_id, '0') = NVL(p_org_id, '0') AND
NVL(org_type, '0') = NVL(p_org_type, '0') /*AND
NVL(INACTIVE_DATE,SYSDATE) >= SYSDATE*/
;
SELECT IBY_PAY_SERVICE_REQUESTS_S.nextval INTO l_payreq_id
FROM DUAL;
| insert_payreq_documents
|
|
| PURPOSE:
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
FUNCTION insert_payreq_documents (
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_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
payment_service_request_id%TYPE
)
RETURN NUMBER
IS
l_return_status NUMBER := -1;
'.insert_payreq_documents';
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd
WHERE
fnd.application_id = p_calling_app_id;
/* old select - for reference purposes */
/*----------------------------------
OPEN l_docs_cursor FOR
'SELECT * FROM '
|| l_view_name
|| ' WHERE calling_app_id = :ca_id'
|| ' AND call_app_pay_service_req_code = :ca_payreq_cd'
USING
p_calling_app_id,
p_calling_app_payreq_cd
;
* Ensure that the order of the columns in this SELECT matches
* exactly with the order of the columns of the template table
* IBY_GEN_DOCS_PAYABLE.
*
* By using names columns in this SELECT (instead of select *),
* we are making it possible for the external application to
* have a slightly different column ordering that what is
* present in IBY_GEN_DOCS_PAYABLE (otherwise, the ordering
* becomes strict).
*/
OPEN l_docs_cursor FOR
'SELECT '
|| 'pay_proc_trxn_type_code, '
|| 'calling_app_id, '
|| 'calling_app_doc_unique_ref1, '
|| 'calling_app_doc_unique_ref2, '
|| 'calling_app_doc_unique_ref3, '
|| 'calling_app_doc_unique_ref4, '
|| 'calling_app_doc_unique_ref5, '
|| 'calling_app_doc_ref_number, '
|| 'call_app_pay_service_req_code, '
|| 'IBY_DOCS_PAYABLE_ALL_S.nextval, '
|| 'payment_function, '
|| 'payment_date, '
|| 'document_date, '
|| 'document_type, '
|| 'document_currency_code, '
|| 'document_amount, '
|| 'payment_currency_code, '
|| 'payment_amount, '
|| 'payment_method_code, '
|| 'exclusive_payment_flag, '
|| 'remit_payee_party_id, '
|| 'remit_party_site_id, '
|| 'remit_supplier_site_id, '
|| 'remit_beneficiary_party, '
|| 'legal_entity_id, '
|| 'org_id, '
|| 'org_type, '
|| 'allow_removing_document_flag, '
|| 'created_by, ' -- Ramesh, Why r we selecting this?
|| 'creation_date, '
|| 'last_updated_by, '
|| 'last_update_date, '
|| 'last_update_login, '
|| 'object_version_number, '
|| 'anticipated_value_date, '
|| 'po_number, '
|| 'document_description, '
|| 'document_currency_tax_amount, '
|| 'document_curr_charge_amount, '
|| 'amount_withheld, '
|| 'payment_curr_discount_taken, '
|| 'discount_date, '
|| 'payment_due_date, '
|| 'payment_profile_id, '
|| 'internal_bank_account_id, '
|| 'external_bank_account_id, '
|| 'bank_charge_bearer, '
|| 'interest_rate, '
|| 'payment_grouping_number, '
|| 'payment_reason_code, '
|| 'payment_reason_comments, '
|| 'settlement_priority, '
|| 'remittance_message1, '
|| 'remittance_message2, '
|| 'remittance_message3, '
|| 'unique_remittance_identifier, '
|| 'uri_check_digit, '
|| 'delivery_channel_code, '
|| 'payment_format_code, '
|| 'document_sequence_id, '
|| 'document_sequence_value, '
|| 'document_category_code, '
|| 'bank_assigned_ref_code, '
|| 'remit_to_location_id, '
|| 'attribute_category, '
|| 'attribute1, '
|| 'attribute2, '
|| 'attribute3, '
|| 'attribute4, '
|| 'attribute5, '
|| 'attribute6, '
|| 'attribute7, '
|| 'attribute8, '
|| 'attribute9, '
|| 'attribute10, '
|| 'attribute11, '
|| 'attribute12, '
|| 'attribute13, '
|| 'attribute14, '
|| 'attribute15, '
|| 'address_source, '
|| 'employee_address_code, '
|| 'employee_person_id, '
|| 'employee_payment_flag, '
|| 'employee_address_id, '
|| 'payee_party_id, '
|| 'party_site_id, '
|| 'supplier_site_id, '
|| 'beneficiary_party, '
|| 'relationship_id, '
|| 'global_attribute_category, '
|| 'global_attribute1, '
|| 'global_attribute2, '
|| 'global_attribute3, '
|| 'global_attribute4, '
|| 'global_attribute5, '
|| 'global_attribute6, '
|| 'global_attribute7, '
|| 'global_attribute8, '
|| 'global_attribute9, '
|| 'global_attribute10, '
|| 'global_attribute11, '
|| 'global_attribute12, '
|| 'global_attribute13, '
|| 'global_attribute14, '
|| 'global_attribute15, '
|| 'global_attribute16, '
|| 'global_attribute17, '
|| 'global_attribute18, '
|| 'global_attribute19, '
|| 'global_attribute20, '
|| 'nvl(affects_rejection_level, ''Y'') ' --AWT Enhancement 16296267
|| 'FROM '
|| l_view_name
|| ' WHERE calling_app_id = :ca_id'
|| ' AND call_app_pay_service_req_code = :ca_payreq_cd'
USING
p_calling_app_id,
p_calling_app_payreq_cd
;
delete_docspayTab;
iby_disburse_submit_pub_pkg.docspayTab.last_updated_by ,
iby_disburse_submit_pub_pkg.docspayTab.last_update_date ,
iby_disburse_submit_pub_pkg.docspayTab.last_update_login ,
iby_disburse_submit_pub_pkg.docspayTab.object_version_number ,
iby_disburse_submit_pub_pkg.docspayTab.anticipated_value_date ,
iby_disburse_submit_pub_pkg.docspayTab.po_number ,
iby_disburse_submit_pub_pkg.docspayTab.document_description ,
iby_disburse_submit_pub_pkg.docspayTab.document_currency_tax_amount ,
iby_disburse_submit_pub_pkg.docspayTab.document_curr_charge_amount ,
iby_disburse_submit_pub_pkg.docspayTab.amount_withheld ,
iby_disburse_submit_pub_pkg.docspayTab.payment_curr_discount_taken ,
iby_disburse_submit_pub_pkg.docspayTab.discount_date ,
iby_disburse_submit_pub_pkg.docspayTab.payment_due_date ,
iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id ,
iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id ,
iby_disburse_submit_pub_pkg.docspayTab.external_bank_account_id ,
iby_disburse_submit_pub_pkg.docspayTab.bank_charge_bearer ,
iby_disburse_submit_pub_pkg.docspayTab.interest_rate ,
iby_disburse_submit_pub_pkg.docspayTab.payment_grouping_number ,
iby_disburse_submit_pub_pkg.docspayTab.payment_reason_code ,
iby_disburse_submit_pub_pkg.docspayTab.payment_reason_comments ,
iby_disburse_submit_pub_pkg.docspayTab.settlement_priority ,
iby_disburse_submit_pub_pkg.docspayTab.remittance_message1 ,
iby_disburse_submit_pub_pkg.docspayTab.remittance_message2 ,
iby_disburse_submit_pub_pkg.docspayTab.remittance_message3 ,
iby_disburse_submit_pub_pkg.docspayTab.unique_remittance_identifier ,
iby_disburse_submit_pub_pkg.docspayTab.uri_check_digit ,
iby_disburse_submit_pub_pkg.docspayTab.delivery_channel_code ,
iby_disburse_submit_pub_pkg.docspayTab.payment_format_code ,
iby_disburse_submit_pub_pkg.docspayTab.document_sequence_id ,
iby_disburse_submit_pub_pkg.docspayTab.document_sequence_value ,
iby_disburse_submit_pub_pkg.docspayTab.document_category_code ,
iby_disburse_submit_pub_pkg.docspayTab.bank_assigned_ref_code ,
iby_disburse_submit_pub_pkg.docspayTab.remit_to_location_id ,
iby_disburse_submit_pub_pkg.docspayTab.attribute_category ,
iby_disburse_submit_pub_pkg.docspayTab.attribute1 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute2 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute3 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute4 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute5 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute6 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute7 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute8 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute9 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute10 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute11 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute12 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute13 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute14 ,
iby_disburse_submit_pub_pkg.docspayTab.attribute15 ,
iby_disburse_submit_pub_pkg.docspayTab.address_source ,
iby_disburse_submit_pub_pkg.docspayTab.employee_address_code ,
iby_disburse_submit_pub_pkg.docspayTab.employee_person_id ,
iby_disburse_submit_pub_pkg.docspayTab.employee_payment_flag ,
iby_disburse_submit_pub_pkg.docspayTab.employee_address_id ,
/*TPP-Start*/
iby_disburse_submit_pub_pkg.docspayTab.inv_payee_party_id ,
iby_disburse_submit_pub_pkg.docspayTab.inv_party_site_id ,
iby_disburse_submit_pub_pkg.docspayTab.inv_supplier_site_id ,
iby_disburse_submit_pub_pkg.docspayTab.inv_beneficiary_party ,
iby_disburse_submit_pub_pkg.docspayTab.relationship_id ,
/*TPP-End*/
/*German Format*/
iby_disburse_submit_pub_pkg.docspayTab.global_attribute_category ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute1 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute2 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute3 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute4 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute5 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute6 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute7 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute8 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute9 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute10 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute11 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute12 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute13 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute14 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute15 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute16 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute17 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute18 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute19 ,
iby_disburse_submit_pub_pkg.docspayTab.global_attribute20 ,
iby_disburse_submit_pub_pkg.docspayTab.affects_rejection_level --AWT Enhancement 16296267
/*German Format*/
LIMIT G_LINES_PER_FETCH;
* We will use this structure in doing a bulk insert into the
* IBY_DOCS_PAYABLE_ALL table.
*/
iby_disburse_submit_pub_pkg.docspayTab.payment_service_request_id(l_trx_line_index)
:= p_payreq_id;
iby_disburse_submit_pub_pkg.docspayTab.last_updated_by(l_trx_line_index) := fnd_global.user_id;
iby_disburse_submit_pub_pkg.docspayTab.last_update_date(l_trx_line_index) := sysdate;
iby_disburse_submit_pub_pkg.docspayTab.last_update_login(l_trx_line_index) := fnd_global.login_id;
* Following columns are not selected in the above cursor
* They need to be initialized, otherwise, "No Data Found" is encountered.
*/
iby_disburse_submit_pub_pkg.docspayTab.payment_id(l_trx_line_index) := NULL;
* Insert document info in IBY_DOCS_PAYABLE_ALL
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Before insert ' );
print_debuginfo(l_module_name, '25: ' || iby_disburse_submit_pub_pkg.docspayTab.last_updated_by(l_trx_line_index));
print_debuginfo(l_module_name, '26: ' || iby_disburse_submit_pub_pkg.docspayTab.last_update_date(l_trx_line_index));
print_debuginfo(l_module_name, '33: ' || iby_disburse_submit_pub_pkg.docspayTab.last_update_login(l_trx_line_index));
INSERT INTO IBY_DOCS_PAYABLE_ALL
(
pay_proc_trxn_type_code,
calling_app_id,
calling_app_doc_ref_number,
document_payable_id,
payment_function,
payment_date,
document_date,
document_type,
document_status,
document_currency_code,
document_amount,
payment_currency_code,
payment_amount,
payment_service_request_id,
payment_method_code,
exclusive_payment_flag,
straight_through_flag,
ext_payee_id,
payee_party_id,
legal_entity_id,
org_id,
allow_removing_document_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
object_version_number,
calling_app_doc_unique_ref1,
calling_app_doc_unique_ref2,
calling_app_doc_unique_ref3,
calling_app_doc_unique_ref4,
calling_app_doc_unique_ref5,
last_update_login,
party_site_id,
supplier_site_id,
beneficiary_party,
org_type,
anticipated_value_date,
po_number,
document_description,
document_currency_tax_amount,
document_curr_charge_amount,
amount_withheld,
payment_curr_discount_taken,
discount_date,
payment_due_date,
payment_profile_id,
payment_id,
formatting_payment_id,
internal_bank_account_id,
external_bank_account_id,
bank_charge_bearer,
interest_rate,
payment_grouping_number,
payment_reason_code,
payment_reason_comments,
settlement_priority,
remittance_message1,
remittance_message2,
remittance_message3,
unique_remittance_identifier,
uri_check_digit,
delivery_channel_code,
payment_format_code,
document_sequence_id,
document_sequence_value,
document_category_code,
bank_assigned_ref_code,
remit_to_location_id,
completed_pmts_group_id,
rejected_docs_group_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
address_source,
employee_address_code,
employee_payment_flag,
employee_person_id,
employee_address_id,
inv_payee_party_id,
inv_party_site_id,
inv_supplier_site_id,
inv_beneficiary_party,
ext_inv_payee_id,
relationship_id,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
affects_rejection_level --AWT enhancement 16296267
)
VALUES
(
iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_ref_number(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_payable_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_function(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_type(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_status(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_currency_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_amount(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_amount(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_service_request_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_method_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.exclusive_payment_flag(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.straight_through_flag(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payee_party_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.legal_entity_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.org_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.allow_removing_document_flag(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.created_by(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.creation_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.last_updated_by(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.last_update_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.object_version_number(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.last_update_login(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.party_site_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.supplier_site_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.beneficiary_party(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.org_type(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.anticipated_value_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.po_number(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_description(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_currency_tax_amount(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_curr_charge_amount(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.amount_withheld(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_curr_discount_taken(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.discount_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_due_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.formatting_payment_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.external_bank_account_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.bank_charge_bearer(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.interest_rate(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_grouping_number(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_reason_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_reason_comments(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.settlement_priority(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.remittance_message1(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.remittance_message2(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.remittance_message3(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.unique_remittance_identifier(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.uri_check_digit(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.delivery_channel_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_format_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_sequence_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_sequence_value(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_category_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.bank_assigned_ref_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.remit_to_location_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.completed_pmts_group_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.rejected_docs_group_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute_category(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute1(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute2(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute3(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute4(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute5(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute6(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute7(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute8(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute9(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute10(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute11(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute12(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute13(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute14(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.attribute15(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.address_source(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.employee_address_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.employee_payment_flag(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.employee_person_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.employee_address_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.inv_payee_party_id(l_trx_line_index) ,
iby_disburse_submit_pub_pkg.docspayTab.inv_party_site_id(l_trx_line_index) ,
iby_disburse_submit_pub_pkg.docspayTab.inv_supplier_site_id(l_trx_line_index) ,
iby_disburse_submit_pub_pkg.docspayTab.inv_beneficiary_party(l_trx_line_index) ,
iby_disburse_submit_pub_pkg.docspayTab.ext_inv_payee_id(l_trx_line_index) ,
iby_disburse_submit_pub_pkg.docspayTab.relationship_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute_category(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute1(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute2(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute3(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute4(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute5(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute6(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute7(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute8(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute9(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute10(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute11(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute12(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute13(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute14(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute15(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute16(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute17(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute18(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute19(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.global_attribute20(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.affects_rejection_level(l_trx_line_index) --AWT Enh 16296267
);
print_debuginfo(l_module_name, 'Finished inserting'
|| ' documents into IBY_DOCS_PAYABLE_ALL'
|| ' table');
delete_docspayTab;
* Insert the distinct payment functions and orgs that
* were found in the documents of this request. These
* will be used for limiting UI access to users.
*/
insertDistinctAccessTypsForReq(l_pmtFxAccessTypesTab,
l_orgAccessTypesTab);
* If the documents were inserted successfully, update the
* status of the payment request to 'submitted'.
*/
UPDATE
IBY_PAY_SERVICE_REQUESTS
SET
payment_service_request_status = REQ_STATUS_SUBMITTED
WHERE
payment_service_request_id = p_payreq_id;
print_debuginfo(l_module_name, 'Updated status of'
|| ' payment request '
|| p_payreq_id
|| ' to "submitted"'
);
* insertion was successful. Set the return status
* to success.
*/
l_return_status := 0;
print_log(l_module_name, l_module_name||':'|| ' While inserting document with Invoice Number : ' ||
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_ref_number(ERR_IND) || ' with Invoice ID : ' ||
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2(ERR_IND) );
|| 'attempting to insert documents for '
|| 'calling app id '
|| p_calling_app_id
|| ', calling app payment service request id '
|| p_calling_app_payreq_cd
);
END insert_payreq_documents;
SELECT IBY_DOCS_PAYABLE_ALL_S.nextval INTO l_docPayID
FROM DUAL;
SELECT IBY_DOCUMENT_LINES_S.nextval INTO l_docLineID
FROM DUAL;
| insertDistinctAccessTypsForReq
|
| PURPOSE:
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertDistinctAccessTypsForReq(
p_pmtFxAccessTypesTab IN distinctPmtFxAccessTab,
p_orgAccessTypesTab IN distinctOrgAccessTab
)
IS
TYPE t_object_id IS TABLE OF
NUMBER(15)
INDEX BY BINARY_INTEGER;
INSERT INTO IBY_PROCESS_FUNCTIONS
(
object_id,
object_type,
payment_function
)
VALUES
(
l_object_id(i),
l_object_type(i),
l_payment_function(i)
)
;
INSERT INTO IBY_PROCESS_ORGS
(
object_id,
object_type,
org_id,
org_type
)
VALUES
(
l_object_id(j),
l_object_type(j),
l_org_id(j),
l_org_type(j)
)
;
END insertDistinctAccessTypsForReq;
SELECT
cedoc.payment_document_id
INTO
x_profile_attribs.payment_doc_id
FROM
CE_PAYMENT_DOCUMENTS cedoc
WHERE
cedoc.payment_document_id = x_profile_attribs.payment_doc_id;
SELECT
automatic_ppr_report_submit,
ppr_report_format
INTO
l_report_flag,
l_report_format
FROM
IBY_INTERNAL_PAYERS_ALL sysoptions
WHERE
sysoptions.org_id IS NULL
;
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd,
IBY_PAY_SERVICE_REQUESTS req
WHERE
fnd.application_id = req.calling_app_id AND
req.payment_service_request_id = p_payreq_id
;
SELECT *
INTO l_ppp_rec
FROM IBY_PAYMENT_PROFILES
WHERE payment_profile_id = p_profile_id;
PROCEDURE delete_docspayTab IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.delete_docspayTab';
iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.calling_app_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_ref_number.delete;
iby_disburse_submit_pub_pkg.docspayTab.call_app_pay_service_req_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_payable_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_function.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_date.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_date.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_type.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_status.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_currency_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_amount.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_amount.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_service_request_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_method_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.exclusive_payment_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.straight_through_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.payee_party_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.legal_entity_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.org_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.allow_removing_document_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.created_by.delete;
iby_disburse_submit_pub_pkg.docspayTab.creation_date.delete;
iby_disburse_submit_pub_pkg.docspayTab.last_updated_by.delete;
iby_disburse_submit_pub_pkg.docspayTab.last_update_date.delete;
iby_disburse_submit_pub_pkg.docspayTab.object_version_number.delete;
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1.delete;
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2.delete;
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3.delete;
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4.delete;
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5.delete;
iby_disburse_submit_pub_pkg.docspayTab.last_update_login.delete;
iby_disburse_submit_pub_pkg.docspayTab.party_site_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.supplier_site_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.beneficiary_party.delete;
iby_disburse_submit_pub_pkg.docspayTab.org_type.delete;
iby_disburse_submit_pub_pkg.docspayTab.anticipated_value_date.delete;
iby_disburse_submit_pub_pkg.docspayTab.po_number.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_description.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_currency_tax_amount.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_curr_charge_amount.delete;
iby_disburse_submit_pub_pkg.docspayTab.amount_withheld.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_curr_discount_taken.delete;
iby_disburse_submit_pub_pkg.docspayTab.discount_date.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_due_date.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.formatting_payment_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.external_bank_account_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.bank_charge_bearer.delete;
iby_disburse_submit_pub_pkg.docspayTab.interest_rate.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_grouping_number.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_reason_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_reason_comments.delete;
iby_disburse_submit_pub_pkg.docspayTab.settlement_priority.delete;
iby_disburse_submit_pub_pkg.docspayTab.remittance_message1.delete;
iby_disburse_submit_pub_pkg.docspayTab.remittance_message2.delete;
iby_disburse_submit_pub_pkg.docspayTab.remittance_message3.delete;
iby_disburse_submit_pub_pkg.docspayTab.unique_remittance_identifier.delete;
iby_disburse_submit_pub_pkg.docspayTab.uri_check_digit.delete;
iby_disburse_submit_pub_pkg.docspayTab.delivery_channel_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_format_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_sequence_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_sequence_value.delete;
iby_disburse_submit_pub_pkg.docspayTab.document_category_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.bank_assigned_ref_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.remit_to_location_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.completed_pmts_group_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.rejected_docs_group_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute_category.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute1.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute2.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute3.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute4.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute5.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute6.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute7.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute8.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute9.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute10.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute11.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute12.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute13.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute14.delete;
iby_disburse_submit_pub_pkg.docspayTab.attribute15.delete;
iby_disburse_submit_pub_pkg.docspayTab.address_source.delete;
iby_disburse_submit_pub_pkg.docspayTab.employee_address_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.employee_payment_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.employee_person_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.employee_address_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.bank_instruction1_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.bank_instruction2_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_text_message1.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_text_message2.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_text_message3.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_remittance_message.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_bank_charge_bearer.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_delivery_channel.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_settle_priority_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_payment_details_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_details_length_limit.delete;
iby_disburse_submit_pub_pkg.docspayTab.payment_details_formula.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_max_documents_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.max_documents_per_payment.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_unique_remit_id_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_payment_reason.delete;
iby_disburse_submit_pub_pkg.docspayTab.group_by_due_date_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.processing_type.delete;
iby_disburse_submit_pub_pkg.docspayTab.declaration_option.delete;
iby_disburse_submit_pub_pkg.docspayTab.dcl_only_foreign_curr_pmt_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.declaration_curr_fx_rate_type.delete;
iby_disburse_submit_pub_pkg.docspayTab.declaration_currency_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.declaration_threshold_amount.delete;
iby_disburse_submit_pub_pkg.docspayTab.maximum_payment_amount.delete;
iby_disburse_submit_pub_pkg.docspayTab.minimum_payment_amount.delete;
iby_disburse_submit_pub_pkg.docspayTab.allow_zero_payments_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.support_bills_payable_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.iba_legal_entity_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.int_bank_country_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.ext_bank_country_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.foreign_pmts_allowed_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.inv_payee_party_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.inv_party_site_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.inv_supplier_site_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.inv_beneficiary_party.delete;
iby_disburse_submit_pub_pkg.docspayTab.ext_inv_payee_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.relationship_id.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute_category.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute1.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute2.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute3.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute4.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute5.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute6.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute7.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute8.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute9.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute10.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute11.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute12.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute13.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute14.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute15.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute16.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute17.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute18.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute19.delete;
iby_disburse_submit_pub_pkg.docspayTab.global_attribute20.delete;
iby_disburse_submit_pub_pkg.docspayTab.dont_pay_flag.delete;
iby_disburse_submit_pub_pkg.docspayTab.dont_pay_reason_code.delete;
iby_disburse_submit_pub_pkg.docspayTab.dont_pay_description.delete;
iby_disburse_submit_pub_pkg.docspayTab.affects_rejection_level.delete; --AWT Enh 16296267
END delete_docspayTab;
| update_payreq_status
|
|
| PURPOSE:
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE update_payreq_status (
l_payreq_id IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE,
l_payreq_status IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_status%TYPE,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.update_payreq_status';
update iby_pay_service_requests
set payment_service_request_status = l_payreq_status
where payment_service_request_id = l_payreq_id;
END update_payreq_status;