[Home] [Help]
155:
156: BEGIN
157: print_debuginfo(l_module_name, 'ENTER');
158:
159: UPDATE iby_payments_all
160: SET STOP_REQUEST_PLACED_FLAG = 'Y',
161: STOP_REQUEST_PLACED_BY = p_requested_by,
162: STOP_REQUEST_REASON = p_request_reason,
163: STOP_REQUEST_REFERENCE= p_request_reference,
233: || '.payment_stop_release';
234: BEGIN
235: print_debuginfo(l_module_name, 'ENTER');
236:
237: UPDATE iby_payments_all
238: SET STOP_RELEASED_FLAG = 'Y',
239: STOP_RELEASED_BY = p_released_by,
240: STOP_RELEASE_DATE = p_release_date,
241: STOP_RELEASE_REASON = p_release_reason,
808: INTO
809: l_app_short_name
810: FROM
811: FND_APPLICATION fnd,
812: IBY_PAYMENTS_ALL pmt,
813: IBY_PAY_SERVICE_REQUESTS req
814: WHERE
815: fnd.application_id = req.calling_app_id AND
816: req.payment_service_request_id = pmt.payment_service_request_id AND
1372: INTO
1373: l_instr_id
1374: FROM
1375: IBY_PAY_INSTRUCTIONS_ALL instr,
1376: IBY_PAYMENTS_ALL pmt
1377: WHERE
1378: instr.payment_instruction_id = pmt.payment_instruction_id AND
1379: pmt.payment_id = p_pmt_id;
1380: EXCEPTION
1395: count(*)
1396: INTO
1397: l_valid_pmts_count
1398: FROM
1399: IBY_PAYMENTS_ALL pmt
1400: WHERE
1401: pmt.payment_instruction_id = l_instr_id AND
1402: pmt.payment_reference_number is not null AND
1403: pmt.payment_status NOT IN
1437: * document's last issued check number must be updated
1438: */
1439: last_issued_document_number = nvl(
1440: (SELECT MAX(pmt.paper_document_number)
1441: FROM iby_payments_all pmt
1442: WHERE pmt.payment_instruction_id = l_instr_id)
1443: ,last_issued_document_number
1444: )
1445: WHERE
1461: INTO
1462: l_app_short_name
1463: FROM
1464: FND_APPLICATION fnd,
1465: IBY_PAYMENTS_ALL pmt,
1466: IBY_PAY_SERVICE_REQUESTS req
1467: WHERE
1468: fnd.application_id = req.calling_app_id AND
1469: req.payment_service_request_id = pmt.payment_service_request_id AND
1741: |
1742: | p_init_msg_list - Standard API parameter; default as FND_API.G_FALSE
1743: |
1744: | p_pmt_id - ID of the payment to be voided. This id will map
1745: | to IBY_PAYMENTS_ALL.PAYMENT_ID column.
1746: |
1747: | p_voided_by - User id of person who issued the void request.
1748: | This id will be stored as an attribute of the
1749: | payment.
1837: *
1838: * Void the specified payment.
1839: */
1840: UPDATE
1841: IBY_PAYMENTS_ALL
1842: SET
1843: payment_status = PAY_STATUS_VOID,
1844: voided_by = p_voided_by,
1845: void_date = p_void_date,
1905: INTO
1906: l_instr_id
1907: FROM
1908: IBY_PAY_INSTRUCTIONS_ALL instr,
1909: IBY_PAYMENTS_ALL pmt
1910: WHERE
1911: instr.payment_instruction_id = pmt.payment_instruction_id AND
1912: pmt.payment_id = p_pmt_id
1913: ;
1924: count(*)
1925: INTO
1926: l_valid_pmts_count
1927: FROM
1928: IBY_PAYMENTS_ALL pmt
1929: WHERE
1930: pmt.payment_instruction_id = l_instr_id AND
1931: pmt.payment_status NOT IN
1932: (
1978: * document's last issued check number must be updated
1979: */
1980: last_issued_document_number = nvl(
1981: (SELECT MAX(pmt.paper_document_number)
1982: FROM iby_payments_all pmt
1983: WHERE pmt.payment_instruction_id = l_instr_id)
1984: ,last_issued_document_number
1985: )
1986: WHERE
2065: |
2066: | p_init_msg_list - Standard API parameter; default as FND_API.G_FALSE
2067: |
2068: | p_pmt_id - ID of the payment to be voided. This id will map
2069: | to IBY_PAYMENTS_ALL.PAYMENT_ID column.
2070: |
2071: | p_voided_by - User id of person who issued the void request.
2072: | This id will be stored as an attribute of the
2073: | payment.
2264: pmt.payment_status
2265: INTO
2266: l_curr_pmt_status
2267: FROM
2268: IBY_PAYMENTS_ALL pmt
2269: WHERE
2270: payment_id = p_pmt_id
2271: ;
2272:
2337: *
2338: * Void the specified payment.
2339: */
2340: UPDATE
2341: IBY_PAYMENTS_ALL
2342: SET
2343: payment_status = PAY_STATUS_VOID,
2344: voided_by = p_voided_by,
2345: void_date = p_void_date,
2380: INTO
2381: l_instr_id
2382: FROM
2383: IBY_PAY_INSTRUCTIONS_ALL instr,
2384: IBY_PAYMENTS_ALL pmt
2385: WHERE
2386: instr.payment_instruction_id = pmt.payment_instruction_id AND
2387: pmt.payment_id = p_pmt_id
2388: ;
2398: count(*)
2399: INTO
2400: l_valid_pmts_count
2401: FROM
2402: IBY_PAYMENTS_ALL pmt
2403: WHERE
2404: pmt.payment_instruction_id = l_instr_id AND
2405: pmt.payment_status NOT IN
2406: (
2452: * document's last issued check number must be updated
2453: */
2454: last_issued_document_number = nvl(
2455: (SELECT MAX(pmt.paper_document_number)
2456: FROM iby_payments_all pmt
2457: WHERE pmt.payment_instruction_id = l_instr_id)
2458: ,last_issued_document_number
2459: )
2460: WHERE
2480: INTO
2481: l_app_short_name
2482: FROM
2483: FND_APPLICATION fnd,
2484: IBY_PAYMENTS_ALL pmt,
2485: IBY_PAY_SERVICE_REQUESTS req
2486: WHERE
2487: fnd.application_id = req.calling_app_id AND
2488: req.payment_service_request_id = pmt.payment_service_request_id AND
2737: INTO
2738: l_app_short_name
2739: FROM
2740: FND_APPLICATION fnd,
2741: IBY_PAYMENTS_ALL pmt,
2742: IBY_PAY_SERVICE_REQUESTS req
2743: WHERE
2744: fnd.application_id = req.calling_app_id AND
2745: req.payment_service_request_id = pmt.payment_service_request_id AND
2952: IS
2953: SELECT
2954: payment_id
2955: FROM
2956: IBY_PAYMENTS_ALL
2957: WHERE
2958: payment_instruction_id = p_instr_id AND
2959: payments_complete_flag = 'Y'
2960: ;
3674: SELECT DISTINCT
3675: fnd.application_short_name
3676: FROM
3677: FND_APPLICATION fnd,
3678: IBY_PAYMENTS_ALL pmt,
3679: IBY_PAY_SERVICE_REQUESTS req,
3680: IBY_PAY_INSTRUCTIONS_ALL ins
3681: WHERE
3682: pmt.payment_instruction_id = ins.payment_instruction_id AND
3694: SELECT
3695: fnd.application_id
3696: FROM
3697: FND_APPLICATION fnd,
3698: IBY_PAYMENTS_ALL pmt,
3699: IBY_PAY_SERVICE_REQUESTS req,
3700: IBY_PAY_INSTRUCTIONS_ALL ins
3701: WHERE
3702: pmt.payment_instruction_id = ins.payment_instruction_id AND
3756: count(*)
3757: INTO
3758: l_valid_pmts_count
3759: FROM
3760: IBY_PAYMENTS_ALL pmt
3761: WHERE
3762: pmt.payment_instruction_id = p_instr_id AND
3763: pmt.payment_status NOT IN
3764: (
3820:
3821: BEGIN
3822:
3823: UPDATE
3824: IBY_PAYMENTS_ALL
3825: SET
3826: payment_status = PAY_STATUS_INS_TERM
3827: WHERE
3828: payment_instruction_id = p_instr_id AND
3892: (
3893: SELECT
3894: payment_id
3895: FROM
3896: IBY_PAYMENTS_ALL
3897: WHERE
3898: payment_instruction_id = p_instr_id AND
3899: payment_status = PAY_STATUS_INS_TERM
3900: )
3968: END;
3969: IF(l_document_locked = TRUE) THEN
3970: SELECT Max(paper_document_number), Min(paper_document_number), Count(*)
3971: INTO l_max_paper_document_number, l_min_paper_document_number, l_number_of_payments
3972: FROM iby_payments_all WHERE payment_instruction_id = p_instr_id;
3973:
3974: l_last_issued_modified := l_last_issued - l_number_of_payments;
3975: IF ((l_last_issued <> l_max_paper_document_number)
3976: OR ((l_max_paper_document_number - l_min_paper_document_number) <> (l_number_of_payments - 1)))
4157: payment_id IN
4158: (SELECT
4159: payment_id
4160: FROM
4161: IBY_PAYMENTS_ALL
4162: WHERE
4163: payment_instruction_id = p_instr_id AND
4164: payment_status = PAY_STATUS_INS_TERM
4165: )
4488:
4489: BEGIN
4490:
4491: UPDATE
4492: IBY_PAYMENTS_ALL
4493: SET
4494: payment_status = PAY_STATUS_REQ_TERM
4495: WHERE
4496: payment_service_request_id = p_req_id AND
5226: | The list of used paper documents numbers will be used to insert
5227: | records into the IBY_USED_PAYMENT_DOCS table.
5228: |
5229: | The list of new paper document numbers will be used to update the
5230: | IBY_PAYMENTS_ALL table with the new paper document numbers for
5231: | the corresponding paments. The payment status will then be set to
5232: | READY_FOR_REPRINT.
5233: |
5234: | Finally, this API will invoke the paper printing flow.
5445:
5446: /*
5447: * STEP 2:
5448: *
5449: * Update the IBY_PAYMENTS_ALL table with the list of
5450: * new paper document numbers. The payment ids and new
5451: * paper document numbers have been provided in matching
5452: * order. The status of the payment also needs to be
5453: * updated to READY_FOR_REPRINT.
5463: pmt.payment_status
5464: INTO
5465: l_curr_pmt_status
5466: FROM
5467: IBY_PAYMENTS_ALL pmt
5468: WHERE
5469: payment_id = p_pmt_list(i)
5470: ;
5471:
5504: * payment is a setup / overflow payment and
5505: * it must be reprinted.
5506: */
5507: UPDATE
5508: IBY_PAYMENTS_ALL
5509: SET
5510: paper_document_number = p_new_ppr_docs_list(i),
5511: payment_status = DECODE(
5512: l_curr_pmt_status,
5806:
5807: /*
5808: * STEP 2:
5809: *
5810: * Update the IBY_PAYMENTS_ALL table with the new status.
5811: * The status of the payment needs to be READY_FOR_REPRINT.
5812: */
5813: FOR i IN p_pmt_list.FIRST .. p_pmt_list.LAST LOOP
5814:
5820: pmt.payment_status
5821: INTO
5822: l_curr_pmt_status
5823: FROM
5824: IBY_PAYMENTS_ALL pmt
5825: WHERE
5826: payment_id = p_pmt_list(i)
5827: ;
5828:
5837: * payment is a setup / overflow payment and
5838: * it must be reprinted.
5839: */
5840: UPDATE
5841: IBY_PAYMENTS_ALL
5842: SET
5843: payment_status = DECODE(
5844: l_curr_pmt_status,
5845: PAY_STATUS_VOID_SETUP,
5951: || ' to formatted ..'
5952: );
5953:
5954: UPDATE
5955: IBY_PAYMENTS_ALL
5956: SET
5957: payment_status = PAY_STATUS_FORMATTED
5958: WHERE
5959: payment_instruction_id = p_instr_id
6187:
6188: l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
6189: || '.finalize_print_status';
6190:
6191: l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
6192:
6193: /* used in invocation of callout procedure */
6194: l_api_version CONSTANT NUMBER := 1.0;
6195: l_msg_count NUMBER;
6205: l_doc_status_list docPayStatusTab;
6206:
6207: x_return_status_remove_doc VARCHAR2(200);
6208:
6209: CURSOR c_docs (p_pmt_id IBY_PAYMENTS_ALL.
6210: payment_id%TYPE)
6211: IS
6212: SELECT
6213: doc.document_payable_id,
6329: i||') :'
6330: || p_used_docs_list(i) );
6331:
6332: UPDATE
6333: IBY_PAYMENTS_ALL
6334: SET
6335: payment_status = PAY_STATUS_SPOILED
6336: WHERE
6337: payment_instruction_id = p_instr_id AND
6495: * paper payment; the payment must be in ISSUED status in order to
6496: * be marked complete.
6497: */
6498: UPDATE
6499: IBY_PAYMENTS_ALL
6500: SET
6501: payment_status = PAY_STATUS_ISSUED
6502: WHERE
6503: payment_instruction_id = p_instr_id AND
6666: l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
6667: || '.finalize_print_status';
6668:
6669:
6670: l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
6671: l_payment_status_list docPayStatusTab;
6672: l_skipped_document_number IBY_PAYMENTS_ALL.paper_document_number%TYPE;
6673:
6674: /* used in invocation of callout procedure */
6668:
6669:
6670: l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
6671: l_payment_status_list docPayStatusTab;
6672: l_skipped_document_number IBY_PAYMENTS_ALL.paper_document_number%TYPE;
6673:
6674: /* used in invocation of callout procedure */
6675: l_api_version CONSTANT NUMBER := 1.0;
6676: l_msg_count NUMBER;
6686: l_doc_status_list docPayStatusTab;
6687:
6688: x_return_status_remove_doc VARCHAR2(200);
6689:
6690: CURSOR c_docs (p_pmt_id IBY_PAYMENTS_ALL.
6691: payment_id%TYPE)
6692: IS
6693: SELECT
6694: doc.document_payable_id,
6768:
6769: -- fetching the payment status for the documents and storing them in an array
6770: SELECT payment_status
6771: INTO l_payment_status_list(i)
6772: FROM IBY_PAYMENTS_ALL
6773: WHERE paper_document_number = p_used_docs_list(i)
6774: AND payment_id = p_used_pmts_list(i)
6775: AND payment_instruction_id = p_instr_id;
6776:
6926: l_paper_doc_num := p_used_docs_list(i);
6927: print_debuginfo(l_module_name, 'In the loop, spoiled document number : ('||i||') :' || p_used_pmts_list(i) );
6928:
6929: UPDATE
6930: IBY_PAYMENTS_ALL
6931: SET
6932: payment_status = PAY_STATUS_SPOILED
6933: WHERE
6934: payment_instruction_id = p_instr_id AND
7094: * paper payment; the payment must be in ISSUED status in order to
7095: * be marked complete.
7096: */
7097: UPDATE
7098: IBY_PAYMENTS_ALL
7099: SET
7100: payment_status = PAY_STATUS_ISSUED
7101: WHERE
7102: payment_instruction_id = p_instr_id AND
7288: l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
7289: || '.finalize_instr_print_status';
7290:
7291: l_pmtDocsTab paperDocNumTab;
7292: l_skipped_document_number IBY_PAYMENTS_ALL.paper_document_number%TYPE;
7293: l_pmt_doc_id NUMBER(15);
7294:
7295:
7296: /*
7319: IS
7320: SELECT
7321: pmt.paper_document_number
7322: FROM
7323: IBY_PAYMENTS_ALL pmt
7324: WHERE
7325: pmt.payment_instruction_id = p_instr_id AND
7326: (
7327: (
7483: * paper payment; the payment must be in ISSUED status in order to
7484: * be marked complete.
7485: */
7486: UPDATE
7487: IBY_PAYMENTS_ALL
7488: SET
7489: payment_status = PAY_STATUS_ISSUED
7490: WHERE
7491: payment_instruction_id = p_instr_id AND
7773: INTO
7774: l_app_short_name
7775: FROM
7776: FND_APPLICATION fnd,
7777: IBY_PAYMENTS_ALL pmt,
7778: IBY_PAY_SERVICE_REQUESTS req,
7779: IBY_PAY_INSTRUCTIONS_ALL ins
7780: WHERE
7781: pmt.payment_instruction_id = ins.payment_instruction_id AND
7793: SELECT
7794: fnd.application_id
7795: FROM
7796: FND_APPLICATION fnd,
7797: IBY_PAYMENTS_ALL pmt,
7798: IBY_PAY_SERVICE_REQUESTS req,
7799: IBY_PAY_INSTRUCTIONS_ALL ins
7800: WHERE
7801: pmt.payment_instruction_id = ins.payment_instruction_id AND
7903: */
7904: IF (l_processing_type = P_TYPE_PRINTED) THEN
7905:
7906: UPDATE
7907: IBY_PAYMENTS_ALL
7908: SET
7909: payments_complete_flag = 'Y'
7910: WHERE
7911: payment_instruction_id = p_instr_id AND
7920:
7921: /* batch payment(s) */
7922:
7923: UPDATE
7924: IBY_PAYMENTS_ALL
7925: SET
7926: payments_complete_flag = 'Y'
7927: WHERE
7928: payment_instruction_id = p_instr_id AND
7971: * payment status is FORMATTED.
7972: */
7973:
7974: UPDATE
7975: IBY_PAYMENTS_ALL
7976: SET
7977: payments_complete_flag = 'Y'
7978: WHERE
7979: payment_instruction_id = p_instr_id AND
8106: */
8107: IF (l_processing_type = P_TYPE_PRINTED) THEN
8108:
8109: UPDATE
8110: IBY_PAYMENTS_ALL pmt
8111: SET
8112: pmt.completed_pmts_group_id = l_completion_id
8113: WHERE
8114: pmt.payment_instruction_id = p_instr_id AND
8127:
8128: ELSE
8129:
8130: UPDATE
8131: IBY_PAYMENTS_ALL pmt
8132: SET
8133: pmt.completed_pmts_group_id = l_completion_id
8134: WHERE
8135: pmt.payment_instruction_id = p_instr_id AND
8179: doc.payment_id IN
8180: (SELECT
8181: pmt.payment_id
8182: FROM
8183: IBY_PAYMENTS_ALL pmt
8184: WHERE
8185: pmt.completed_pmts_group_id = l_completion_id
8186: )
8187: ;
8216: count(*)
8217: INTO
8218: l_pmt_count
8219: FROM
8220: IBY_PAYMENTS_ALL pmt
8221: WHERE
8222: pmt.completed_pmts_group_id = l_completion_id
8223: ;
8224:
8386: * document's last issued check number must be updated
8387: */
8388: last_issued_document_number = nvl(
8389: (SELECT MAX(pmt.paper_document_number)
8390: FROM iby_payments_all pmt
8391: WHERE pmt.payment_instruction_id = p_instr_id)
8392: ,last_issued_document_number
8393: )
8394: WHERE
9783:
9784: ELSIF (p_object_type = 'PAYMENT') THEN
9785:
9786: UPDATE
9787: IBY_PAYMENTS_ALL pmt
9788: SET
9789: pmt.request_id = l_conc_request_id
9790: WHERE
9791: pmt.payment_id = p_object_id
9960:
9961: ELSIF (p_object_type = 'PAYMENT') THEN
9962:
9963: UPDATE
9964: IBY_PAYMENTS_ALL pmt
9965: SET
9966: pmt.request_id = NULL
9967: WHERE
9968: pmt.payment_id = p_object_id
10271: COUNT(*)
10272: INTO
10273: l_all_pmts_count
10274: FROM
10275: IBY_PAYMENTS_ALL pmt
10276: WHERE
10277: pmt.payment_instruction_id = p_instr_id
10278: ;
10279:
10288: COUNT(*)
10289: INTO
10290: l_term_pmts_count
10291: FROM
10292: IBY_PAYMENTS_ALL pmt
10293: WHERE
10294: pmt.payment_instruction_id = p_instr_id
10295: AND
10296: pmt.payment_status = PAY_STATUS_REQ_TERM
10378: | method for other general purposes.
10379: |
10380: |
10381: | -- IMPORTANT --
10382: | This method locks rows in the IBY_PAYMENTS_ALL table and
10383: | expects that the caller will commit/rollback to release lock.
10384: |
10385: *---------------------------------------------------------------------*/
10386: FUNCTION checkIfPmtInInstExists(
10435: /*
10436: * Instead of locking the entire table only lock the
10437: * rows that we are interested in (explicit locking).
10438: */
10439: -- LOCK TABLE IBY_PAYMENTS_ALL IN EXCLUSIVE MODE;
10440:
10441: SELECT
10442: pmt.payment_id
10443: INTO
10442: pmt.payment_id
10443: INTO
10444: l_test
10445: FROM
10446: IBY_PAYMENTS_ALL pmt
10447: WHERE
10448: pmt.payment_instruction_id IS NOT NULL
10449: AND
10450: pmt.payment_service_request_id = p_payreq_id
10466: (
10467: SELECT
10468: pmt.payment_id
10469: FROM
10470: IBY_PAYMENTS_ALL pmt
10471: WHERE
10472: pmt.payment_instruction_id IS NOT NULL
10473: AND
10474: pmt.payment_service_request_id = p_payreq_id
10592: inst.payment_instruction_id = p_instr_id
10593: ;
10594:
10595: UPDATE
10596: IBY_PAYMENTS_ALL pmt
10597: SET
10598: pmt.payment_status = PAY_STATUS_FORMATTED
10599: WHERE
10600: pmt.payment_instruction_id = p_instr_id
10610: inst.payment_instruction_id = p_instr_id
10611: ;
10612:
10613: UPDATE
10614: IBY_PAYMENTS_ALL pmt
10615: SET
10616: pmt.payment_status = PAY_STATUS_TRANSMITTED
10617: WHERE
10618: pmt.payment_instruction_id = p_instr_id
10863: (
10864: SELECT
10865: pmt.payment_id
10866: FROM
10867: IBY_PAYMENTS_ALL pmt
10868: WHERE
10869: pmt.payment_id = p_object_id
10870: AND
10871: pmt.request_id IS NOT NULL
11186: v_req_status_t VARCHAR2(80);
11187: v_req_phase_c VARCHAR2(30);
11188: v_req_status_c VARCHAR2(30);
11189: v_message VARCHAR2(240);
11190: v_request_id iby_payments_all.request_id%TYPE;
11191:
11192: x_return_status VARCHAR2(30) := 'PENDING';
11193:
11194: BEGIN
11259: IS
11260: SELECT
11261: pmt.payment_id
11262: FROM
11263: IBY_PAYMENTS_ALL pmt
11264: WHERE
11265: pmt.completed_pmts_group_id = p_complete_id
11266: ;
11267:
11552:
11553:
11554: select count(1)
11555: into l_pmt_org_count
11556: FROM iby_payments_all payments
11557: where payments.payment_status IN
11558: ('FAILED_BY_REJECTION_LEVEL',
11559: 'FAILED_BY_CALLING_APP',
11560: 'REMOVED',