[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
613: pa_debug.g_err_stage := 'Log: Start of Update_Act_Enc_Balance';
614: pa_debug.write_file('LOG',pa_debug.g_err_stage);
615: END IF;
616:
617: pa_funds_control_utils.print_message('Entering Sweeper');
618: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
619: pa_fck_util.debug_msg('PB:Entering Sweeper');
620: END IF;
621:
622: --deleting rows older than 'X' days which is set in the profile
623: --Pkts with status C should not exist ideally
624: --Pkts with status B will be taken care of during rebaselining
625: --Pkts with status P are yet to be funds checked, do not delete here.
626: pa_funds_control_utils.print_message('Open c_delete_pkts');
627: open c_delete_pkts;
628: loop
629: l_RowIdTab.Delete;
630: fetch c_delete_pkts bulk collect into
635: pa_debug.g_err_stage := 'Log: No. of records to delete ' || l_RowIdTab.count;
636: pa_debug.write_file('LOG',pa_debug.g_err_stage);
637: END IF;
638:
639: pa_funds_control_utils.print_message('No. of records to delete = ' || l_RowIdTab.count);
640:
641: if l_RowIdTab.count = 0 then
642: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
643: pa_debug.g_err_stage := 'Log: No records in c_delete_pkts, exit';
642: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
643: pa_debug.g_err_stage := 'Log: No records in c_delete_pkts, exit';
644: pa_debug.write_file('LOG',pa_debug.g_err_stage);
645: END IF;
646: pa_funds_control_utils.print_message('No records from c_delete_pkts, exit');
647: exit;
648: end if;
649:
650: FORALL i in l_RowIdTab.first..l_RowIdTab.last
657: commit;
658: exit when c_delete_pkts%notfound;
659: end loop;
660: close c_delete_pkts;
661: pa_funds_control_utils.print_message('Close c_delete_pkts');
662:
663: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
664: /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
665: pa_debug.g_err_stage := 'Log: Last Deleted ' || to_char(l_rowcount) || ' records from PA_BC_PACKETS older than ' || l_profile_value || ' days';
665: pa_debug.g_err_stage := 'Log: Last Deleted ' || to_char(l_rowcount) || ' records from PA_BC_PACKETS older than ' || l_profile_value || ' days';
666: pa_debug.write_file('LOG',pa_debug.g_err_stage);
667: END IF;
668: /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
669: pa_funds_control_utils.print_message('No of Deleted old packets in pa_bc_packets = ' || to_char(l_rowcount));
670:
671: --deleting rows older than 'X' days which is set in the profile
672: --(batch_id of -999 are those belonging to actuals)
673: /*
685: pa_debug.g_err_stage := 'Log: Deleted ' || to_char(l_rowcount) || ' records from GL_BC_PACKETS older than ' || l_profile_value || ' days';
686: pa_debug.write_file('LOG',pa_debug.g_err_stage);
687: END IF;
688: /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
689: pa_funds_control_utils.print_message('No of Deleted old packets in gl_bc_packets = '|| to_char(l_rowcount));
690:
691: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
692: pa_debug.g_err_stage := 'Log: Entering Loop';
693: pa_debug.write_file('LOG',pa_debug.g_err_stage);
696: --Loop for distinct project in pa_bc_packets
697: FOR eRec in c_pkt_proj LOOP
698:
699: l_project_id := eRec.project_id;
700: pa_funds_control_utils.print_message('*******************************************************');
701: pa_funds_control_utils.print_message('Inside Project loop ' || to_char(l_project_id));
702:
703: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
704: pa_debug.g_err_stage := '*******************************************************';
697: FOR eRec in c_pkt_proj LOOP
698:
699: l_project_id := eRec.project_id;
700: pa_funds_control_utils.print_message('*******************************************************');
701: pa_funds_control_utils.print_message('Inside Project loop ' || to_char(l_project_id));
702:
703: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
704: pa_debug.g_err_stage := '*******************************************************';
705: pa_debug.write_file('LOG',pa_debug.g_err_stage);
710:
711: --Acquire lock on project
712: IF (pa_debug.acquire_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
713:
714: pa_funds_control_utils.print_message('Acquired Lock on project '||to_char(l_project_id));
715:
716: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
717: pa_debug.g_err_stage := 'Log: Lock Acquired for Project = ' || to_char(l_project_id) ;
718: pa_debug.write_file('LOG',pa_debug.g_err_stage);
717: pa_debug.g_err_stage := 'Log: Lock Acquired for Project = ' || to_char(l_project_id) ;
718: pa_debug.write_file('LOG',pa_debug.g_err_stage);
719: END IF;
720:
721: pa_funds_control_utils.print_message('Open cursor c_bc_packets');
722: --Open c_bc_packets cursor
723: open c_bc_packets(l_project_id);
724:
725: l_count := 0;
738: pa_debug.g_err_stage := 'Log: Fetched ' || l_count || ' batch';
739: pa_debug.write_file('LOG',pa_debug.g_err_stage);
740: END IF;
741:
742: pa_funds_control_utils.print_message('No of cursor batch fetched = ' || l_count);
743:
744: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
745: pa_debug.g_err_stage := 'Log: Fetch cursor c_bc_packets';
746: pa_debug.write_file('LOG',pa_debug.g_err_stage);
746: pa_debug.write_file('LOG',pa_debug.g_err_stage);
747: END IF;
748:
749:
750: pa_funds_control_utils.print_message('Fetch cursor c_bc_packets');
751:
752: --Fetch cursor c_bc_packets limiting rows
753: FETCH c_bc_packets BULK COLLECT INTO
754: l_PktBdgtVerTab,
819: pa_debug.g_err_stage := 'Log: No. of records fetched ' || l_PktIdTab.count;
820: pa_debug.write_file('LOG',pa_debug.g_err_stage);
821: END IF;
822:
823: pa_funds_control_utils.print_message('No. of records fetched = ' || l_PktIdTab.count);
824:
825: --If no rows fetched, exit
826: IF l_PktIdTab.count = 0 THEN
827: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
827: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
828: pa_debug.g_err_stage := 'Log: No records in c_bc_packets, exit';
829: pa_debug.write_file('LOG',pa_debug.g_err_stage);
830: END IF;
831: pa_funds_control_utils.print_message('No records from c_bc_packets, exit');
832: EXIT;
833: END IF;
834:
835: pa_funds_control_utils.print_message('In c_bc_packets loop');
831: pa_funds_control_utils.print_message('No records from c_bc_packets, exit');
832: EXIT;
833: END IF;
834:
835: pa_funds_control_utils.print_message('In c_bc_packets loop');
836:
837: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
838: pa_debug.g_err_stage := 'Log: Before update of balances';
839: pa_debug.write_file('LOG',pa_debug.g_err_stage);
838: pa_debug.g_err_stage := 'Log: Before update of balances';
839: pa_debug.write_file('LOG',pa_debug.g_err_stage);
840: END IF;
841:
842: pa_funds_control_utils.print_message('Before update of balances');
843:
844: --Update the balances if the record exists in pa_bc_balances
845: FORALL i in l_PktIdTab.FIRST..l_PktIdTab.LAST
846: UPDATE pa_bc_balances pb
875: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
876: pa_debug.g_err_stage := 'Log: Updating Balances , Index = '|| i || ' RowCount = ' || SQL%BULK_ROWCOUNT(i) ||' Pkt Id ' || l_PktBcPktTab(i);
877: pa_debug.write_file('LOG',pa_debug.g_err_stage);
878: END IF;
879: pa_funds_control_utils.print_message('Updating Balances ' || SQL%BULK_ROWCOUNT(i) ||' Pkt Id ' || l_PktBcPktTab(i));
880:
881: --If no. of rec updated not = 0 then place packet id in another table.
882: --This is to ensure that we update the status of these packets only
883: if (SQL%BULK_ROWCOUNT(i) <> 0) then
887: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
888: pa_debug.g_err_stage := 'Log: Num = ' || num || ' Status Pkt Id = ' || l_StsUpdPktIdTab(num) || ' Bc Pkt = ' || l_StsUpdBcPktIdTab(num);
889: pa_debug.write_file('LOG',pa_debug.g_err_stage);
890: END IF;
891: pa_funds_control_utils.print_message('Status Packet Id = ' || l_StsUpdPktIdTab(num)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(num));
892: end if;
893: end loop;
894:
895: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
903: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
904: pa_debug.g_err_stage := 'Log: No of Status updates = ' || l_StsUpdPktIdTab(i)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(i);
905: pa_debug.write_file('LOG',pa_debug.g_err_stage);
906: END IF;
907: pa_funds_control_utils.print_message('No of Status updates = ' || l_StsUpdPktIdTab(i)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(i));
908: end loop;
909: end if;
910:
911: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
915: pa_debug.g_err_stage := 'Log: Before Insert into commitments';
916: pa_debug.write_file('LOG',pa_debug.g_err_stage);
917: END IF;
918:
919: pa_funds_control_utils.print_message('Inserting Commitments');
920:
921: --Insert into pa_bc_commitments_all if bc_commitment_id is null in pa_bc_packets
922: FORALL k in l_PktIdTab.FIRST..l_PktIdTab.LAST
923: insert into pa_bc_commitments_all(
1080: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1081: pa_debug.g_err_stage := 'Log: No of commitment inserts = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1082: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1083: END IF;
1084: pa_funds_control_utils.print_message('No. of commitment insert = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1085: end loop;
1086:
1087: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1088: pa_debug.g_err_stage := 'Log: After Insert into commitments';
1091: pa_debug.g_err_stage := 'Log: Before Update of commitments';
1092: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1093: END IF;
1094:
1095: pa_funds_control_utils.print_message('Before update of pa_bc_commitments_all');
1096:
1097: --Update pa_bc_commitments_all if bc_commitment_id is not null
1098: FORALL j in l_PktIdTab.FIRST..l_PktIdTab.LAST
1099: UPDATE pa_bc_commitments_all pbc
1161: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1162: pa_debug.g_err_stage := 'Log: No of commitment updates = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1163: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1164: END IF;
1165: pa_funds_control_utils.print_message('No. of commitment updates = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1166: end loop;
1167:
1168: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1169: pa_debug.g_err_stage := 'Log: After Update of commitments';
1172: pa_debug.g_err_stage := 'Log: Before update status_code, balance_posted_flag in c_bc_packets loop '||l_StsUpdPktIdTab.count;
1173: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1174: END IF;
1175:
1176: pa_funds_control_utils.print_message('Before update of status_code');
1177: pa_funds_control_utils.print_message('No. of status updates in c_bc_packets= '||l_StsUpdPktIdTab.count);
1178:
1179: --Update status_code and balance_posted_flag
1180: IF (l_StsUpdPktIdTab.count <> 0) THEN
1173: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1174: END IF;
1175:
1176: pa_funds_control_utils.print_message('Before update of status_code');
1177: pa_funds_control_utils.print_message('No. of status updates in c_bc_packets= '||l_StsUpdPktIdTab.count);
1178:
1179: --Update status_code and balance_posted_flag
1180: IF (l_StsUpdPktIdTab.count <> 0) THEN
1181: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1203: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1204: pa_debug.g_err_stage := 'Log: No of status update ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1205: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1206: END IF;
1207: pa_funds_control_utils.print_message('No of status update ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1208: end loop;
1209: end if;
1210:
1211: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1214: END IF;
1215:
1216: --Commit in a batch i.e. 200 rows
1217: commit;
1218: pa_funds_control_utils.print_message('End loop');
1219:
1220: EXIT WHEN c_bc_packets%NOTFOUND;
1221: END LOOP;
1222:
1219:
1220: EXIT WHEN c_bc_packets%NOTFOUND;
1221: END LOOP;
1222:
1223: pa_funds_control_utils.print_message('Close c_bc_packets');
1224:
1225: CLOSE c_bc_packets;
1226: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1227: pa_debug.g_err_stage := 'Log: Open cursor c_ins_packets';
1227: pa_debug.g_err_stage := 'Log: Open cursor c_ins_packets';
1228: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1229: END IF;
1230:
1231: pa_funds_control_utils.print_message('Open cursor c_ins_packets');
1232:
1233: l_count := 0;
1234:
1235: --Open cursor c_ins_packets
1245: pa_debug.g_err_stage := 'Log: Fetched ' || l_count || ' batch';
1246: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1247: END IF;
1248:
1249: pa_funds_control_utils.print_message('No of cursor batch fetched = ' || l_count);
1250:
1251: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1252: pa_debug.g_err_stage := 'Log: Fetch cursor c_ins_packets';
1253: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1252: pa_debug.g_err_stage := 'Log: Fetch cursor c_ins_packets';
1253: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1254: END IF;
1255:
1256: pa_funds_control_utils.print_message('Fetch cursor c_ins_packets');
1257:
1258: --Fetch records from cursor in batch
1259: FETCH c_ins_packets bulk collect into
1260: l_InsBdgtVerTab,
1281: pa_debug.g_err_stage := 'Log: No. of records fetched = '||l_InsProjectTab.count;
1282: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1283: END IF;
1284:
1285: pa_funds_control_utils.print_message('No. of records fetched = ' || l_InsProjectTab.count);
1286:
1287: --If no. of records fetched = 0 then exit
1288: IF l_InsProjectTab.count = 0 THEN
1289: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1289: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1290: pa_debug.g_err_stage := 'Log: No records from cursor c_ins_packets, exit';
1291: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1292: END IF;
1293: pa_funds_control_utils.print_message('No records from cursor c_ins_packets, exit');
1294: EXIT;
1295: END IF;
1296:
1297: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1298: pa_debug.g_err_stage := 'Log: Before inserting balances';
1299: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1300: END IF;
1301:
1302: pa_funds_control_utils.print_message('Before inserting into pa_bc_balances');
1303:
1304: --Insert into pa_bc_balances
1305: FORALL p in l_InsProjectTab.FIRST..l_InsProjectTab.LAST
1306: insert into pa_bc_balances(project_id
1361: pa_debug.g_err_stage := 'Log: Before update status_code and balance_posted_flag in c_ins_packets';
1362: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1363: END IF;
1364:
1365: pa_funds_control_utils.print_message('Update status_code and balance_posted_flag');
1366:
1367: --Update status_code and balance_posted_flag in pa_bc_packets
1368: FORALL m in l_InsProjectTab.FIRST..l_InsProjectTab.LAST
1369: UPDATE pa_bc_packets pbc
1384: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1385: pa_debug.g_err_stage := 'Log: No. of status update in c_ins_packets = ' || SQL%BULK_ROWCOUNT(i);
1386: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1387: END IF;
1388: pa_funds_control_utils.print_message('No. of status update in c_ins_packets = ' || SQL%BULK_ROWCOUNT(i));
1389: end loop;
1390:
1391: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1392: pa_debug.g_err_stage := 'Log: After Update of status_code and balance_posted_flag in c_ins_packets loop';
1403: CLOSE c_ins_packets;
1404:
1405: --Release lock on project
1406: IF (pa_debug.release_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
1407: pa_funds_control_utils.print_message('Releasing lock for '|| l_project_id);
1408: IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1409: pa_debug.g_err_stage := 'Log: Finished processing, release lock on Project = ' || to_char(l_project_id);
1410: pa_debug.write_file('LOG',pa_debug.g_err_stage);
1411: END IF;
1448:
1449: x_error_message_code := (SQLCODE||' '||SQLERRM);
1450:
1451: IF (pa_debug.release_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
1452: pa_funds_control_utils.print_message('In others Releasing lock for proj '|| l_project_id);
1453: null;
1454: END IF;
1455:
1456: IF c_bc_packets%isopen THEN