843: l_count3 NUMBER;
844: l_count4 NUMBER;
845: BEGIN
846: IF(g_psr_snapshot_table.EXISTS(p_snapshot_code) AND g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count IS NOT NULL) THEN
847: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Getting from the cache',
849: debug_level => FND_LOG.LEVEL_STATEMENT,
850: module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
851: END IF;
845: BEGIN
846: IF(g_psr_snapshot_table.EXISTS(p_snapshot_code) AND g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count IS NOT NULL) THEN
847: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Getting from the cache',
849: debug_level => FND_LOG.LEVEL_STATEMENT,
850: module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
851: END IF;
852: l_ret_val:= g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count;
853: ELSE
1057: IF ( p_psr_id IS NOT NULL ) THEN
1058:
1059: IF(g_psr_table.EXISTS(p_psr_id) AND g_psr_table(p_psr_id).payment_status_flag IS NOT NULL
1060: AND p_from_cache = 'TRUE') THEN
1061: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_payment_status_flag :Getting from the cache',
1063: debug_level => FND_LOG.LEVEL_STATEMENT,
1064: module => 'IBY_UTILITY_PVT.get_payment_status_flag');
1065: END IF;
1059: IF(g_psr_table.EXISTS(p_psr_id) AND g_psr_table(p_psr_id).payment_status_flag IS NOT NULL
1060: AND p_from_cache = 'TRUE') THEN
1061: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_payment_status_flag :Getting from the cache',
1063: debug_level => FND_LOG.LEVEL_STATEMENT,
1064: module => 'IBY_UTILITY_PVT.get_payment_status_flag');
1065: END IF;
1066: l_payment_status_flag:= g_psr_table(p_psr_id).payment_status_flag;
1067: ELSE
1141:
1142: BEGIN
1143: IF(g_psr_table.EXISTS(p_psr_id) AND g_psr_table(p_psr_id).psr_status IS NOT NULL
1144: AND p_from_cache = 'TRUE') THEN
1145: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1146: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_status :Getting from the cache',
1147: debug_level => FND_LOG.LEVEL_STATEMENT,
1148: module => 'IBY_UTILITY_PVT.get_psr_status');
1149: END IF;
1143: IF(g_psr_table.EXISTS(p_psr_id) AND g_psr_table(p_psr_id).psr_status IS NOT NULL
1144: AND p_from_cache = 'TRUE') THEN
1145: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1146: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_status :Getting from the cache',
1147: debug_level => FND_LOG.LEVEL_STATEMENT,
1148: module => 'IBY_UTILITY_PVT.get_psr_status');
1149: END IF;
1150: l_psr_status:= g_psr_table(p_psr_id).psr_status;
1151: ELSE
1262: */
1263: PROCEDURE initialize
1264: IS
1265: BEGIN
1266: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1267: iby_debug_pub.add(debug_msg => 'ENTER: ' || 'IBY_UTILITY_PVT.initialize',
1268: debug_level => FND_LOG.LEVEL_STATEMENT,
1269: module => 'IBY_UTILITY_PVT.initialize');
1270: END IF;
1264: IS
1265: BEGIN
1266: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1267: iby_debug_pub.add(debug_msg => 'ENTER: ' || 'IBY_UTILITY_PVT.initialize',
1268: debug_level => FND_LOG.LEVEL_STATEMENT,
1269: module => 'IBY_UTILITY_PVT.initialize');
1270: END IF;
1271: g_psr_table.DELETE;
1272: g_psr_snapshot_table.DELETE;
1270: END IF;
1271: g_psr_table.DELETE;
1272: g_psr_snapshot_table.DELETE;
1273: g_instr_access_table.DELETE;
1274: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1275: iby_debug_pub.add(debug_msg => 'Exit: ' || 'IBY_UTILITY_PVT.initialize',
1276: debug_level => FND_LOG.LEVEL_STATEMENT,
1277: module => 'IBY_UTILITY_PVT.initialize');
1278: END IF;
1272: g_psr_snapshot_table.DELETE;
1273: g_instr_access_table.DELETE;
1274: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1275: iby_debug_pub.add(debug_msg => 'Exit: ' || 'IBY_UTILITY_PVT.initialize',
1276: debug_level => FND_LOG.LEVEL_STATEMENT,
1277: module => 'IBY_UTILITY_PVT.initialize');
1278: END IF;
1279: END initialize;
1280:
1422: --AND process_type = 'STANDARD' ;
1423: BEGIN
1424: IF(g_psr_snapshot_table.EXISTS(p_snapshot_code) AND g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count IS NOT NULL) THEN
1425:
1426: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Getting from the cache', debug_level => FND_LOG.LEVEL_STATEMENT, module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
1428: END IF ;
1429: ELSE
1430: -- Initialize the variables.
1423: BEGIN
1424: IF(g_psr_snapshot_table.EXISTS(p_snapshot_code) AND g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count IS NOT NULL) THEN
1425:
1426: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Getting from the cache', debug_level => FND_LOG.LEVEL_STATEMENT, module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
1428: END IF ;
1429: ELSE
1430: -- Initialize the variables.
1431: l_need_action :=0;
1488: EXCEPTION
1489: WHEN NO_DATA_FOUND THEN
1490: NULL; -- Do Nothing.
1491: WHEN OTHERS THEN
1492: iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Error in processing count', debug_level => FND_LOG.LEVEL_STATEMENT, module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
1493: END;
1494: END IF;
1495: /* PAYMENTS_CREATED and it can be in either processing or need action group */
1496: /* IBY Terminated */
1647: BEGIN
1648:
1649: -- Deleting all records from GT table
1650: DELETE FROM IBY_PAY_DASHBOARD_GT;
1651: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1652: iby_debug_pub.add(debug_msg => 'Deleted all records from GT table',
1653: debug_level => FND_LOG.LEVEL_STATEMENT,
1654: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1655: END IF;
1649: -- Deleting all records from GT table
1650: DELETE FROM IBY_PAY_DASHBOARD_GT;
1651: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1652: iby_debug_pub.add(debug_msg => 'Deleted all records from GT table',
1653: debug_level => FND_LOG.LEVEL_STATEMENT,
1654: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1655: END IF;
1656:
1657:
1709: WHERE iby.calling_app_id = 200
1710: AND iby.call_app_pay_service_req_code =
1711: ap.checkrun_name);
1712:
1713: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1714: iby_debug_pub.add(debug_msg => 'Populated Need Action PPRs ',
1715: debug_level => FND_LOG.LEVEL_STATEMENT,
1716: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1717: END IF;
1711: ap.checkrun_name);
1712:
1713: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1714: iby_debug_pub.add(debug_msg => 'Populated Need Action PPRs ',
1715: debug_level => FND_LOG.LEVEL_STATEMENT,
1716: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1717: END IF;
1718:
1719: -- Inserting PPRs which are in Processing Status - 'PROCESSING'
1785: WHERE iby.calling_app_id = 200
1786: AND iby.call_app_pay_service_req_code =
1787: ap.checkrun_name);
1788:
1789: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1790: iby_debug_pub.add(debug_msg => 'Populated Processing PPRs ',
1791: debug_level => FND_LOG.LEVEL_STATEMENT,
1792: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1793: END IF;
1787: ap.checkrun_name);
1788:
1789: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1790: iby_debug_pub.add(debug_msg => 'Populated Processing PPRs ',
1791: debug_level => FND_LOG.LEVEL_STATEMENT,
1792: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1793: END IF;
1794:
1795: -- Inserting PPRs which are terminated by User --'USER_TERMINATED'
1828: AND iby.call_app_pay_service_req_code =
1829: ap.checkrun_name)
1830: AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1831:
1832: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1833: iby_debug_pub.add(debug_msg => 'Populated User Terminated PPRs ',
1834: debug_level => FND_LOG.LEVEL_STATEMENT,
1835: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1836: END IF;
1830: AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1831:
1832: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1833: iby_debug_pub.add(debug_msg => 'Populated User Terminated PPRs ',
1834: debug_level => FND_LOG.LEVEL_STATEMENT,
1835: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1836: END IF;
1837:
1838:
1856: ('PENDING_REVIEW_DOC_VAL_ERRORS',
1857: 'PENDING_REVIEW_PMT_VAL_ERRORS'))
1858: AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1859:
1860: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1861: iby_debug_pub.add(debug_msg => 'Populated Program Errors PPRs ',
1862: debug_level => FND_LOG.LEVEL_STATEMENT,
1863: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1864: END IF;
1858: AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1859:
1860: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1861: iby_debug_pub.add(debug_msg => 'Populated Program Errors PPRs ',
1862: debug_level => FND_LOG.LEVEL_STATEMENT,
1863: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1864: END IF;
1865:
1866: -- Insert PPRs which are completed - 'COMPLETED'
1883: ('PAYMENTS_CREATED','COMPLETED')
1884: AND AP_PAYMENT_UTIL_PKG.get_payment_status_flag(iby.payment_service_request_id) = 'Y')
1885: AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1886:
1887: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1888: iby_debug_pub.add(debug_msg => 'Populated Completed PPRs ',
1889: debug_level => FND_LOG.LEVEL_STATEMENT,
1890: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1891: iby_debug_pub.add(debug_msg => 'Exit: ' || 'IBY_UTILITY_PVT.populate_psr_snapshot_count',
1885: AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1886:
1887: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1888: iby_debug_pub.add(debug_msg => 'Populated Completed PPRs ',
1889: debug_level => FND_LOG.LEVEL_STATEMENT,
1890: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1891: iby_debug_pub.add(debug_msg => 'Exit: ' || 'IBY_UTILITY_PVT.populate_psr_snapshot_count',
1892: debug_level => FND_LOG.LEVEL_STATEMENT,
1893: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1888: iby_debug_pub.add(debug_msg => 'Populated Completed PPRs ',
1889: debug_level => FND_LOG.LEVEL_STATEMENT,
1890: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1891: iby_debug_pub.add(debug_msg => 'Exit: ' || 'IBY_UTILITY_PVT.populate_psr_snapshot_count',
1892: debug_level => FND_LOG.LEVEL_STATEMENT,
1893: module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1894: END IF;
1895:
1896: END populate_psr_snapshot_count;