[Home] [Help]
22: p_pa_date in date) return date
23: IS
24: l_st_date date;
25: BEGIN
26: --pa_funds_control_utils.print_message('Entering GetBCBalStartDate, Time Phase = ' || p_time_phase_code || ' Sob = '|| p_sob_id || ' Org Id = ' || p_org_id);
27: --pa_fck_util.debug_msg('Entering GetBCBalStartDate' );
28:
29: IF p_time_phase_code = 'G' then
30: select gps.start_date
83: END;
84:
85: END IF;
86:
87: --pa_funds_control_utils.print_message('Leaving Start date');
88: --pa_fck_util.debug_msg('Exiting GetBCBalStartDate' );
89: return l_st_date;
90: EXCEPTION
91: WHEN OTHERS THEN
114: p_pa_date in date) return date
115: IS
116: l_ed_date date;
117: BEGIN
118: --pa_funds_control_utils.print_message('Entering GetBCBalEndDate');
119: --pa_fck_util.debug_msg('Entering GetBCBalEndDate' );
120:
121: IF p_time_phase_code = 'G' then
122: select gps.end_date
173: END;
174:
175: END IF;
176:
177: --pa_funds_control_utils.print_message('Leaving End date');
178: --pa_fck_util.debug_msg('Exiting GetBCBalEndDate' );
179: return l_ed_date;
180: EXCEPTION
181: WHEN OTHERS THEN
677: pa_debug.g_err_stage := 'Log: Start of Update_Act_Enc_Balance';
678: pa_debug.write_file('LOG',pa_debug.g_err_stage);
679: END IF;
680:
681: pa_funds_control_utils.print_message('Entering Sweeper');
682: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
683: pa_fck_util.debug_msg('PB:Entering Sweeper');
684: END IF;
685:
686: --deleting rows older than 'X' days which is set in the profile
687: --Pkts with status C should not exist ideally
688: --Pkts with status B will be taken care of during rebaselining
689: --Pkts with status P are yet to be funds checked, do not delete here.
690: pa_funds_control_utils.print_message('Open c_delete_pkts');
691: open c_delete_pkts;
692: loop
693: l_RowIdTab.Delete;
694: fetch c_delete_pkts bulk collect into
699: pa_debug.g_err_stage := 'Log: No. of records to delete ' || l_RowIdTab.count;
700: pa_debug.write_file('LOG',pa_debug.g_err_stage);
701: END IF;
702:
703: pa_funds_control_utils.print_message('No. of records to delete = ' || l_RowIdTab.count);
704:
705: if l_RowIdTab.count = 0 then
706: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
707: pa_debug.g_err_stage := 'Log: No records in c_delete_pkts, exit';
706: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
707: pa_debug.g_err_stage := 'Log: No records in c_delete_pkts, exit';
708: pa_debug.write_file('LOG',pa_debug.g_err_stage);
709: END IF;
710: pa_funds_control_utils.print_message('No records from c_delete_pkts, exit');
711: exit;
712: end if;
713:
714: FORALL i in l_RowIdTab.first..l_RowIdTab.last
721: commit;
722: exit when c_delete_pkts%notfound;
723: end loop;
724: close c_delete_pkts;
725: pa_funds_control_utils.print_message('Close c_delete_pkts');
726:
727: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
728: /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
729: pa_debug.g_err_stage := 'Log: Last Deleted ' || to_char(l_rowcount) || ' records from PA_BC_PACKETS older than ' || l_profile_value || ' days';
729: pa_debug.g_err_stage := 'Log: Last Deleted ' || to_char(l_rowcount) || ' records from PA_BC_PACKETS older than ' || l_profile_value || ' days';
730: pa_debug.write_file('LOG',pa_debug.g_err_stage);
731: END IF;
732: /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
733: pa_funds_control_utils.print_message('No of Deleted old packets in pa_bc_packets = ' || to_char(l_rowcount));
734:
735: --deleting rows older than 'X' days which is set in the profile
736: --(batch_id of -999 are those belonging to actuals)
737: /*
749: pa_debug.g_err_stage := 'Log: Deleted ' || to_char(l_rowcount) || ' records from GL_BC_PACKETS older than ' || l_profile_value || ' days';
750: pa_debug.write_file('LOG',pa_debug.g_err_stage);
751: END IF;
752: /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
753: pa_funds_control_utils.print_message('No of Deleted old packets in gl_bc_packets = '|| to_char(l_rowcount));
754:
755: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
756: pa_debug.g_err_stage := 'Log: Entering Loop';
757: pa_debug.write_file('LOG',pa_debug.g_err_stage);
760: --Loop for distinct project in pa_bc_packets
761: FOR eRec in c_pkt_proj LOOP
762:
763: l_project_id := eRec.project_id;
764: pa_funds_control_utils.print_message('*******************************************************');
765: pa_funds_control_utils.print_message('Inside Project loop ' || to_char(l_project_id));
766:
767: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
768: pa_debug.g_err_stage := '*******************************************************';
761: FOR eRec in c_pkt_proj LOOP
762:
763: l_project_id := eRec.project_id;
764: pa_funds_control_utils.print_message('*******************************************************');
765: pa_funds_control_utils.print_message('Inside Project loop ' || to_char(l_project_id));
766:
767: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
768: pa_debug.g_err_stage := '*******************************************************';
769: pa_debug.write_file('LOG',pa_debug.g_err_stage);
774:
775: --Acquire lock on project
776: IF (pa_debug.acquire_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
777:
778: pa_funds_control_utils.print_message('Acquired Lock on project '||to_char(l_project_id));
779:
780: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
781: pa_debug.g_err_stage := 'Log: Lock Acquired for Project = ' || to_char(l_project_id) ;
782: pa_debug.write_file('LOG',pa_debug.g_err_stage);
781: pa_debug.g_err_stage := 'Log: Lock Acquired for Project = ' || to_char(l_project_id) ;
782: pa_debug.write_file('LOG',pa_debug.g_err_stage);
783: END IF;
784:
785: pa_funds_control_utils.print_message('Open cursor c_bc_packets');
786: --Open c_bc_packets cursor
787: open c_bc_packets(l_project_id);
788:
789: l_count := 0;
802: pa_debug.g_err_stage := 'Log: Fetched ' || l_count || ' batch';
803: pa_debug.write_file('LOG',pa_debug.g_err_stage);
804: END IF;
805:
806: pa_funds_control_utils.print_message('No of cursor batch fetched = ' || l_count);
807:
808: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
809: pa_debug.g_err_stage := 'Log: Fetch cursor c_bc_packets';
810: pa_debug.write_file('LOG',pa_debug.g_err_stage);
810: pa_debug.write_file('LOG',pa_debug.g_err_stage);
811: END IF;
812:
813:
814: pa_funds_control_utils.print_message('Fetch cursor c_bc_packets');
815:
816: --Fetch cursor c_bc_packets limiting rows
817: FETCH c_bc_packets BULK COLLECT INTO
818: l_PktBdgtVerTab,
883: pa_debug.g_err_stage := 'Log: No. of records fetched ' || l_PktIdTab.count;
884: pa_debug.write_file('LOG',pa_debug.g_err_stage);
885: END IF;
886:
887: pa_funds_control_utils.print_message('No. of records fetched = ' || l_PktIdTab.count);
888:
889: --If no rows fetched, exit
890: IF l_PktIdTab.count = 0 THEN
891: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
891: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
892: pa_debug.g_err_stage := 'Log: No records in c_bc_packets, exit';
893: pa_debug.write_file('LOG',pa_debug.g_err_stage);
894: END IF;
895: pa_funds_control_utils.print_message('No records from c_bc_packets, exit');
896: EXIT;
897: END IF;
898:
899: pa_funds_control_utils.print_message('In c_bc_packets loop');
895: pa_funds_control_utils.print_message('No records from c_bc_packets, exit');
896: EXIT;
897: END IF;
898:
899: pa_funds_control_utils.print_message('In c_bc_packets loop');
900:
901: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
902: pa_debug.g_err_stage := 'Log: Before update of balances';
903: pa_debug.write_file('LOG',pa_debug.g_err_stage);
902: pa_debug.g_err_stage := 'Log: Before update of balances';
903: pa_debug.write_file('LOG',pa_debug.g_err_stage);
904: END IF;
905:
906: pa_funds_control_utils.print_message('Before update of balances');
907:
908: --Update the balances if the record exists in pa_bc_balances
909: FORALL i in l_PktIdTab.FIRST..l_PktIdTab.LAST
910: UPDATE pa_bc_balances pb
939: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
940: pa_debug.g_err_stage := 'Log: Updating Balances , Index = '|| i || ' RowCount = ' || SQL%BULK_ROWCOUNT(i) ||' Pkt Id ' || l_PktBcPktTab(i);
941: pa_debug.write_file('LOG',pa_debug.g_err_stage);
942: END IF;
943: pa_funds_control_utils.print_message('Updating Balances ' || SQL%BULK_ROWCOUNT(i) ||' Pkt Id ' || l_PktBcPktTab(i));
944:
945: --If no. of rec updated not = 0 then place packet id in another table.
946: --This is to ensure that we update the status of these packets only
947: if (SQL%BULK_ROWCOUNT(i) <> 0) then
951: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
952: pa_debug.g_err_stage := 'Log: Num = ' || num || ' Status Pkt Id = ' || l_StsUpdPktIdTab(num) || ' Bc Pkt = ' || l_StsUpdBcPktIdTab(num);
953: pa_debug.write_file('LOG',pa_debug.g_err_stage);
954: END IF;
955: pa_funds_control_utils.print_message('Status Packet Id = ' || l_StsUpdPktIdTab(num)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(num));
956: end if;
957: end loop;
958:
959: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
967: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
968: pa_debug.g_err_stage := 'Log: No of Status updates = ' || l_StsUpdPktIdTab(i)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(i);
969: pa_debug.write_file('LOG',pa_debug.g_err_stage);
970: END IF;
971: pa_funds_control_utils.print_message('No of Status updates = ' || l_StsUpdPktIdTab(i)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(i));
972: end loop;
973: end if;
974:
975: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
979: pa_debug.g_err_stage := 'Log: Before Insert into commitments';
980: pa_debug.write_file('LOG',pa_debug.g_err_stage);
981: END IF;
982:
983: pa_funds_control_utils.print_message('Inserting Commitments');
984:
985: --Insert into pa_bc_commitments_all if bc_commitment_id is null in pa_bc_packets
986: FORALL k in l_PktIdTab.FIRST..l_PktIdTab.LAST
987: insert into pa_bc_commitments_all(
1144: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1145: pa_debug.g_err_stage := 'Log: No of commitment inserts = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1146: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1147: END IF;
1148: pa_funds_control_utils.print_message('No. of commitment insert = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1149: end loop;
1150:
1151: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1152: pa_debug.g_err_stage := 'Log: After Insert into commitments';
1155: pa_debug.g_err_stage := 'Log: Before Update of commitments';
1156: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1157: END IF;
1158:
1159: pa_funds_control_utils.print_message('Before update of pa_bc_commitments_all');
1160:
1161: --Update pa_bc_commitments_all if bc_commitment_id is not null
1162: FORALL j in l_PktIdTab.FIRST..l_PktIdTab.LAST
1163: UPDATE pa_bc_commitments_all pbc
1225: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1226: pa_debug.g_err_stage := 'Log: No of commitment updates = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1227: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1228: END IF;
1229: pa_funds_control_utils.print_message('No. of commitment updates = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1230: end loop;
1231:
1232: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1233: pa_debug.g_err_stage := 'Log: After Update of commitments';
1236: pa_debug.g_err_stage := 'Log: Before update status_code, balance_posted_flag in c_bc_packets loop '||l_StsUpdPktIdTab.count;
1237: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1238: END IF;
1239:
1240: pa_funds_control_utils.print_message('Before update of status_code');
1241: pa_funds_control_utils.print_message('No. of status updates in c_bc_packets= '||l_StsUpdPktIdTab.count);
1242:
1243: --Update status_code and balance_posted_flag
1244: IF (l_StsUpdPktIdTab.count <> 0) THEN
1237: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1238: END IF;
1239:
1240: pa_funds_control_utils.print_message('Before update of status_code');
1241: pa_funds_control_utils.print_message('No. of status updates in c_bc_packets= '||l_StsUpdPktIdTab.count);
1242:
1243: --Update status_code and balance_posted_flag
1244: IF (l_StsUpdPktIdTab.count <> 0) THEN
1245: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1267: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1268: pa_debug.g_err_stage := 'Log: No of status update ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1269: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1270: END IF;
1271: pa_funds_control_utils.print_message('No of status update ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1272: end loop;
1273: end if;
1274:
1275: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1278: END IF;
1279:
1280: --Commit in a batch i.e. 200 rows
1281: commit;
1282: pa_funds_control_utils.print_message('End loop');
1283:
1284: EXIT WHEN c_bc_packets%NOTFOUND;
1285: END LOOP;
1286:
1283:
1284: EXIT WHEN c_bc_packets%NOTFOUND;
1285: END LOOP;
1286:
1287: pa_funds_control_utils.print_message('Close c_bc_packets');
1288:
1289: CLOSE c_bc_packets;
1290: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1291: pa_debug.g_err_stage := 'Log: Open cursor c_ins_packets';
1291: pa_debug.g_err_stage := 'Log: Open cursor c_ins_packets';
1292: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1293: END IF;
1294:
1295: pa_funds_control_utils.print_message('Open cursor c_ins_packets');
1296:
1297: l_count := 0;
1298:
1299: --Open cursor c_ins_packets
1309: pa_debug.g_err_stage := 'Log: Fetched ' || l_count || ' batch';
1310: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1311: END IF;
1312:
1313: pa_funds_control_utils.print_message('No of cursor batch fetched = ' || l_count);
1314:
1315: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1316: pa_debug.g_err_stage := 'Log: Fetch cursor c_ins_packets';
1317: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1316: pa_debug.g_err_stage := 'Log: Fetch cursor c_ins_packets';
1317: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1318: END IF;
1319:
1320: pa_funds_control_utils.print_message('Fetch cursor c_ins_packets');
1321:
1322: --Fetch records from cursor in batch
1323: FETCH c_ins_packets bulk collect into
1324: l_InsBdgtVerTab,
1345: pa_debug.g_err_stage := 'Log: No. of records fetched = '||l_InsProjectTab.count;
1346: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1347: END IF;
1348:
1349: pa_funds_control_utils.print_message('No. of records fetched = ' || l_InsProjectTab.count);
1350:
1351: --If no. of records fetched = 0 then exit
1352: IF l_InsProjectTab.count = 0 THEN
1353: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1353: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1354: pa_debug.g_err_stage := 'Log: No records from cursor c_ins_packets, exit';
1355: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1356: END IF;
1357: pa_funds_control_utils.print_message('No records from cursor c_ins_packets, exit');
1358: EXIT;
1359: END IF;
1360:
1361: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1362: pa_debug.g_err_stage := 'Log: Before inserting balances';
1363: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1364: END IF;
1365:
1366: pa_funds_control_utils.print_message('Before inserting into pa_bc_balances');
1367:
1368: --Insert into pa_bc_balances
1369: FORALL p in l_InsProjectTab.FIRST..l_InsProjectTab.LAST
1370: insert into pa_bc_balances(project_id
1425: pa_debug.g_err_stage := 'Log: Before update status_code and balance_posted_flag in c_ins_packets';
1426: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1427: END IF;
1428:
1429: pa_funds_control_utils.print_message('Update status_code and balance_posted_flag');
1430:
1431: --Update status_code and balance_posted_flag in pa_bc_packets
1432: FORALL m in l_InsProjectTab.FIRST..l_InsProjectTab.LAST
1433: UPDATE pa_bc_packets pbc
1448: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1449: pa_debug.g_err_stage := 'Log: No. of status update in c_ins_packets = ' || SQL%BULK_ROWCOUNT(i);
1450: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1451: END IF;
1452: pa_funds_control_utils.print_message('No. of status update in c_ins_packets = ' || SQL%BULK_ROWCOUNT(i));
1453: end loop;
1454:
1455: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1456: pa_debug.g_err_stage := 'Log: After Update of status_code and balance_posted_flag in c_ins_packets loop';
1467: CLOSE c_ins_packets;
1468:
1469: --Release lock on project
1470: IF (pa_debug.release_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
1471: pa_funds_control_utils.print_message('Releasing lock for '|| l_project_id);
1472: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1473: pa_debug.g_err_stage := 'Log: Finished processing, release lock on Project = ' || to_char(l_project_id);
1474: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1475: END IF;
1512:
1513: x_error_message_code := (SQLCODE||' '||SQLERRM);
1514:
1515: IF (pa_debug.release_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
1516: pa_funds_control_utils.print_message('In others Releasing lock for proj '|| l_project_id);
1517: null;
1518: END IF;
1519:
1520: IF c_bc_packets%isopen THEN