[Home] [Help]
134: p_billing_uom => p_uom_code,
135: p_period_type => p_period_type
136: );
137: IF Nvl(l_minimum,0) = 0 THEN
138: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error OKS_BILL_REC_PUB.Get_Prorated_Usage_Qty returns l_minimum as '||l_minimum);
139: Raise G_EXCEPTION_HALT_VALIDATION;
140: END IF;
141: l_minimum := Round(l_minimum,0);
142: ELSE
150: p_round_dec => 0
151: );
152:
153: IF Nvl(l_temp,0) = 0 THEN
154: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Get_target_qty returns Zero');
155: Raise G_EXCEPTION_HALT_VALIDATION;
156: END IF;
157: l_minimum := Round((trunc(l_to_date) - trunc(l_from_date) + 1)
158: * (l_minimum /l_temp) ,0) ;
225: END LOOP;
226:
227: EXCEPTION
228: WHEN G_EXCEPTION_HALT_VALIDATION THEN
229: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Level Procedure --G_exception_halt_validation raised' || sqlerrm);
230: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
231: WHEN OTHERS THEN
232: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Level Procedure --OTHERS Exception raised' || sqlerrm);
233: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
228: WHEN G_EXCEPTION_HALT_VALIDATION THEN
229: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Level Procedure --G_exception_halt_validation raised' || sqlerrm);
230: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
231: WHEN OTHERS THEN
232: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Level Procedure --OTHERS Exception raised' || sqlerrm);
233: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
234: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
235:
236: END level;
292:
293:
294: EXCEPTION
295: WHEN G_EXCEPTION_HALT_VALIDATION THEN
296: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in OKS_REG_GET_AMCV -- G_Exception_halt_validation raised' );
297: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
298: WHEN OTHERS THEN
299: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in OKS_REG_GET_AMCV -- Others Exception raised' );
300: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
295: WHEN G_EXCEPTION_HALT_VALIDATION THEN
296: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in OKS_REG_GET_AMCV -- G_Exception_halt_validation raised' );
297: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
298: WHEN OTHERS THEN
299: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in OKS_REG_GET_AMCV -- Others Exception raised' );
300: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
301: G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
302: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
303:
757: usage_exception EXCEPTION;
758:
759: BEGIN
760: If l_write_log then
761: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
762: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
763: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
764: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
765: End If;
758:
759: BEGIN
760: If l_write_log then
761: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
762: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
763: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
764: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
765: End If;
766:
759: BEGIN
760: If l_write_log then
761: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
762: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
763: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
764: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
765: End If;
766:
767:
760: If l_write_log then
761: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
762: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
763: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
764: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
765: End If;
766:
767:
768: p_return_status := 'S';
807: l_msg_data => l_msg_data);
808: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
809: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
810:
811: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in getting next level ');
812:
813: /*Needs to determine or revisited */
814: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
815: Raise USAGE_EXCEPTION;
856: l_bill_end_date := p_top_line_term_date - 1 ;
857: END IF;
858:
859: If l_write_log then
860: FND_FILE.PUT_LINE( FND_FILE.LOG,'Line Interface Date :' ||l_inv_date );
861: FND_FILE.PUT_LINE( FND_FILE.LOG,'Billing Period Start_date: ' || l_bill_start_date||' To '||l_bill_end_date);
862: End If;
863:
864: l_ptr := 0;
857: END IF;
858:
859: If l_write_log then
860: FND_FILE.PUT_LINE( FND_FILE.LOG,'Line Interface Date :' ||l_inv_date );
861: FND_FILE.PUT_LINE( FND_FILE.LOG,'Billing Period Start_date: ' || l_bill_start_date||' To '||l_bill_end_date);
862: End If;
863:
864: l_ptr := 0;
865: l_total := 0;
888: l_msg_data => l_msg_data);
889: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N';
890: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
891:
892: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in creating account');
893:
894:
895:
896: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION') ;
933: (l_msg_cnt => l_msg_count,
934: l_msg_data => l_msg_data);
935: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
936: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
937: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bcl');
938:
939: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
940: Raise USAGE_EXCEPTION;
941: END IF;
968: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
969: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
970: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
971: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
972: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in get next level of coverage ');
973: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
974: Raise USAGE_EXCEPTION;
975: END IF;
976:
985: l_calc_rec.l_calc_sdate := level_coverage(1).bill_from_date;
986: l_calc_rec.l_calc_edate := level_coverage(1).bill_to_date;
987:
988: IF (l_write_log) THEN
989: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage amount : '||l_amount);
990: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage start_date: '||l_calc_rec.l_calc_sdate);
991: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage end_date : '||l_calc_rec.l_calc_edate);
992: END IF;
993:
986: l_calc_rec.l_calc_edate := level_coverage(1).bill_to_date;
987:
988: IF (l_write_log) THEN
989: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage amount : '||l_amount);
990: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage start_date: '||l_calc_rec.l_calc_sdate);
991: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage end_date : '||l_calc_rec.l_calc_edate);
992: END IF;
993:
994: IF ( l_covlvl_rec.date_terminated is not null) AND
987:
988: IF (l_write_log) THEN
989: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage amount : '||l_amount);
990: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage start_date: '||l_calc_rec.l_calc_sdate);
991: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage end_date : '||l_calc_rec.l_calc_edate);
992: END IF;
993:
994: IF ( l_covlvl_rec.date_terminated is not null) AND
995: ( trunc(l_calc_rec.l_calc_edate) >=
1040: l_cov_tbl(l_ptr).date_to_interface := sysdate;
1041:
1042: IF (l_usage_type = 'NPR') THEN
1043: IF (l_write_log) THEN
1044: FND_FILE.PUT_LINE( FND_FILE.LOG, 'USAGE_TYPE of subline = NPR' );
1045: END IF;
1046:
1047: OPEN qty_uom_csr_sub(l_covlvl_rec.id);
1048: FETCH qty_uom_csr_sub into l_qty_uom_sub_rec;
1057:
1058:
1059: ELSIF (l_usage_type = 'FRT') THEN
1060: IF (l_write_log) THEN
1061: FND_FILE.PUT_LINE( FND_FILE.LOG, 'USAGE_TYPE of subline = FRT');
1062: END IF;
1063:
1064: l_qty := l_covlvl_rec.fixed_qty;
1065:
1087: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1088: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero ';
1089: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1090: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero';
1091: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Get_Prorated_Usage_Qty returns l_qty as '||l_qty);
1092:
1093: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1094: Raise USAGE_EXCEPTION;
1095: END IF;
1108: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1109: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero ';
1110: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1111: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero';
1112: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Get_target_qty returns Zero');
1113:
1114: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1115: Raise USAGE_EXCEPTION;
1116: END IF;
1182: END IF;
1183:
1184:
1185: IF (l_write_log) THEN
1186: FND_FILE.PUT_LINE( FND_FILE.LOG, 'After counter values X_value'||' '||l_qty);
1187: END IF;
1188:
1189: l_cov_tbl(l_ptr).result :=nvl(l_qty, 0);
1190: l_cov_tbl(l_ptr).actual :=l_actual_qty ;
1208: fetch get_counter_qty into l_lock_date, l_counter_id;
1209: close get_counter_qty;
1210:
1211: IF (l_write_log) THEN
1212: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Cll IB Lock '||l_lock_date||', Counter id '||l_counter_id);
1213: END IF;
1214:
1215: l_counter_reading_lock_rec.reading_lock_date := l_lock_date;
1216: l_counter_reading_lock_rec.counter_id := l_counter_id;
1273: X_RETURN_STATUS => l_return_status
1274: );
1275:
1276: IF (l_write_log) THEN
1277: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Level '||' '||l_return_status);
1278: END IF;
1279:
1280: IF (l_return_status <> 'S') THEN
1281: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1280: IF (l_return_status <> 'S') THEN
1281: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1282: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message:= 'Error: '|| sqlerrm||'. Error Message:' ;
1283:
1284: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in LEVEL ') ;
1285: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1286: Raise USAGE_EXCEPTION;
1287: END IF;
1288: END IF; -- l_usage_type in 'VRT','QTY'
1403: oks_bill_rec_pub.get_message
1404: (l_msg_cnt => l_msg_count,
1405: l_msg_data => l_msg_data);
1406:
1407: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error update bsl'||' '||l_return_status);
1408: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1409: Raise USAGE_EXCEPTION;
1410: END IF;
1411:
1513: end if;
1514:
1515: Exception
1516: When others then
1517: FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure delete_billing_tables '||sqlerrm || 'for line_id '||p_line_id ||' for header id '||p_dnz_chr_id);
1518: End Rollback_usg_bill_dtls_prc;
1519:
1520:
1521: ---------------------------------------------------------------------------------------------------------
1671: P_CVMV_REC => l_cvmv_rec,
1672: X_CVMV_REC => l_cvmv_out_rec);
1673:
1674: IF l_write_log THEN
1675: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After calling OKC_CVM_PVT.update_contract_version l_return_status '||l_return_status);
1676: END IF;
1677:
1678: l_chrv_rec.id := p_dnz_chr_id;
1679: l_chrv_rec.last_update_date := sysdate;
1688: P_CHRV_REC => l_chrv_rec,
1689: X_CHRV_REC => l_chrv_out_rec);
1690:
1691: IF l_write_log THEN
1692: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After calling OKC_CONTRACT_PUB.update_contract_header l_return_status '||l_return_status);
1693: END IF;
1694:
1695: COMMIT;
1696:
1697: END IF;
1698:
1699: EXCEPTION
1700: WHEN OTHERS THEN
1701: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- updating version'||sqlerrm );
1702:
1703: End update_version;
1704:
1705:
3030:
3031:
3032: IF NVL (lx_return_status, 'S') <> 'S'
3033: THEN
3034: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => QP API error '||lx_return_status_text);
3035: RAISE bulk_contract_exception;
3036: END IF;
3037:
3038: for usage_line_rec IN usage_line_csr(bulk_qp_hdr_id_rec.dnz_chr_id)
3040: BEGIN
3041: DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
3042:
3043: If l_write_log then
3044: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 initializing Billing Report ');
3045: End If;
3046:
3047: l_billrep_found := FALSE;
3048:
3073: p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value := 0;
3074: END IF;
3075:
3076: If l_write_log then
3077: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After initializing Billing Report ');
3078: End If;
3079:
3080:
3081: SELECT count(usage_line_rec.line_id) INTO l_subline_count
3433: IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3434: oks_bill_rec_pub.get_message (
3435: l_msg_cnt => l_msg_count,
3436: l_msg_data => l_msg_data);
3437: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => Failed in AR FEEDER :'||' '||usage_line_rec.line_id);
3438:
3439: RAISE Bulk_USAGE_line_EXCEPTION;
3440:
3441:
3519:
3520:
3521: EXCEPTION
3522: WHEN OTHERS THEN
3523: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Usg_billing_bulk_qp_call_prc=> when others raised'||sqlerrm);
3524: END Usg_billing_bulk_qp_call_prc;
3525:
3526:
3527: -------------------------------------------------------------------------
3778: usage_exception EXCEPTION;
3779:
3780: BEGIN
3781: If l_write_log then
3782: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
3783: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
3784: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
3785: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
3786: End If;
3779:
3780: BEGIN
3781: If l_write_log then
3782: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
3783: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
3784: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
3785: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
3786: End If;
3787:
3780: BEGIN
3781: If l_write_log then
3782: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
3783: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
3784: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
3785: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
3786: End If;
3787:
3788:
3781: If l_write_log then
3782: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
3783: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
3784: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
3785: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
3786: End If;
3787:
3788:
3789: p_return_status := 'S';
3828: l_msg_data => l_msg_data);
3829: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
3830: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
3831:
3832: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in getting next level ');
3833:
3834: /*Needs to determine or revisited */
3835: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
3836: Raise USAGE_EXCEPTION;
3877: l_bill_end_date := p_top_line_term_date - 1 ;
3878: END IF;
3879:
3880: If l_write_log then
3881: FND_FILE.PUT_LINE( FND_FILE.LOG,'Line Interface Date :' ||l_inv_date );
3882: FND_FILE.PUT_LINE( FND_FILE.LOG,'Billing Period Start_date: ' || l_bill_start_date||' To '||l_bill_end_date);
3883: End If;
3884:
3885: l_ptr := 0;
3878: END IF;
3879:
3880: If l_write_log then
3881: FND_FILE.PUT_LINE( FND_FILE.LOG,'Line Interface Date :' ||l_inv_date );
3882: FND_FILE.PUT_LINE( FND_FILE.LOG,'Billing Period Start_date: ' || l_bill_start_date||' To '||l_bill_end_date);
3883: End If;
3884:
3885: l_ptr := 0;
3886: l_total := 0;
3909: l_msg_data => l_msg_data);
3910: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N';
3911: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
3912:
3913: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in creating account');
3914:
3915:
3916:
3917: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION') ;
3954: (l_msg_cnt => l_msg_count,
3955: l_msg_data => l_msg_data);
3956: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
3957: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
3958: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bcl');
3959:
3960: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
3961: Raise USAGE_EXCEPTION;
3962: END IF;
3989: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
3990: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
3991: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
3992: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
3993: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in get next level of coverage ');
3994: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
3995: Raise USAGE_EXCEPTION;
3996: END IF;
3997:
4006: l_calc_rec.l_calc_sdate := level_coverage(1).bill_from_date;
4007: l_calc_rec.l_calc_edate := level_coverage(1).bill_to_date;
4008:
4009: IF (l_write_log) THEN
4010: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage amount : '||l_amount);
4011: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage start_date: '||l_calc_rec.l_calc_sdate);
4012: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage end_date : '||l_calc_rec.l_calc_edate);
4013: END IF;
4014:
4007: l_calc_rec.l_calc_edate := level_coverage(1).bill_to_date;
4008:
4009: IF (l_write_log) THEN
4010: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage amount : '||l_amount);
4011: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage start_date: '||l_calc_rec.l_calc_sdate);
4012: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage end_date : '||l_calc_rec.l_calc_edate);
4013: END IF;
4014:
4015: IF ( l_covlvl_rec.date_terminated is not null) AND
4008:
4009: IF (l_write_log) THEN
4010: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage amount : '||l_amount);
4011: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage start_date: '||l_calc_rec.l_calc_sdate);
4012: FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage end_date : '||l_calc_rec.l_calc_edate);
4013: END IF;
4014:
4015: IF ( l_covlvl_rec.date_terminated is not null) AND
4016: ( trunc(l_calc_rec.l_calc_edate) >=
4061: l_cov_tbl(l_ptr).date_to_interface := sysdate;
4062:
4063: IF (l_usage_type = 'NPR') THEN
4064: IF (l_write_log) THEN
4065: FND_FILE.PUT_LINE( FND_FILE.LOG, 'USAGE_TYPE of subline = NPR' );
4066: END IF;
4067:
4068: OPEN qty_uom_csr_sub(l_covlvl_rec.id);
4069: FETCH qty_uom_csr_sub into l_qty_uom_sub_rec;
4078:
4079:
4080: ELSIF (l_usage_type = 'FRT') THEN
4081: IF (l_write_log) THEN
4082: FND_FILE.PUT_LINE( FND_FILE.LOG, 'USAGE_TYPE of subline = FRT');
4083: END IF;
4084:
4085: l_qty := l_covlvl_rec.fixed_qty;
4086:
4108: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
4109: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero ';
4110: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4111: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero';
4112: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Get_Prorated_Usage_Qty returns l_qty as '||l_qty);
4113:
4114: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4115: Raise USAGE_EXCEPTION;
4116: END IF;
4130: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
4131: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero ';
4132: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4133: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero';
4134: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Get_target_qty returns Zero');
4135:
4136: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4137: Raise USAGE_EXCEPTION;
4138: END IF;
4204: END IF;
4205:
4206:
4207: IF (l_write_log) THEN
4208: FND_FILE.PUT_LINE( FND_FILE.LOG, 'After counter values X_value'||' '||l_qty);
4209: END IF;
4210:
4211: l_cov_tbl(l_ptr).result :=nvl(l_qty, 0);
4212: l_cov_tbl(l_ptr).actual :=l_actual_qty ;
4230: fetch get_counter_qty into l_lock_date, l_counter_id;
4231: close get_counter_qty;
4232:
4233: IF (l_write_log) THEN
4234: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Cll IB Lock '||l_lock_date||', Counter id '||l_counter_id);
4235: END IF;
4236:
4237: l_counter_reading_lock_rec.reading_lock_date := l_lock_date;
4238: l_counter_reading_lock_rec.counter_id := l_counter_id;
4295: X_RETURN_STATUS => l_return_status
4296: );
4297:
4298: IF (l_write_log) THEN
4299: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Level '||' '||l_return_status);
4300: END IF;
4301:
4302: IF (l_return_status <> 'S') THEN
4303: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
4302: IF (l_return_status <> 'S') THEN
4303: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
4304: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message:= 'Error: '|| sqlerrm||'. Error Message:' ;
4305:
4306: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in LEVEL ') ;
4307: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4308: Raise USAGE_EXCEPTION;
4309: END IF;
4310: END IF; -- l_usage_type in 'VRT','QTY'
4381: );
4382:
4383:
4384: IF (l_write_log) THEN
4385: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Price Calculated: '||l_price_rec.PROD_EXT_AMOUNT);
4386: END IF;
4387:
4388: /*FOR BILLING REPORT*/
4389: l_exception_amount := l_price_rec.PROD_EXT_AMOUNT;
4392: /*Added for bug 9927490*/
4393: IF (l_return_status = 'S') THEN
4394: IF l_price_rec.status_code = 'OKS_DUPLICATE_PRICE_LIST_LINES' THEN
4395: l_status_text := l_price_rec.status_text;
4396: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Price Breaks are not available. Following message is returned by Pricing Engine'||' '||l_status_text);
4397: Raise USAGE_EXCEPTION;
4398:
4399: END IF;
4400: ELSE
4401: oks_bill_rec_pub.get_message
4402: (l_msg_cnt => l_msg_count,
4403: l_msg_data => l_msg_data);
4404:
4405: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Calculate Price Error'||' '||l_return_status);
4406:
4407: Raise USAGE_EXCEPTION;
4408:
4409: --OKC_FORMS_UTIL.Display_Errors(P_Stat => l_return_status);
4416:
4417: else
4418:
4419: If l_write_log then --added by ansraj for bug 14016663
4420: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Skipping the zero quantity line for bsl_id '||bsl_price_rec.bsl_id);
4421: End If;
4422:
4423: l_exception_amount := 0;
4424: l_price_rec.PROD_EXT_AMOUNT:=0;
4442: oks_bill_rec_pub.get_message
4443: (l_msg_cnt => l_msg_count,
4444: l_msg_data => l_msg_data);
4445:
4446: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error update bsl'||' '||l_return_status);
4447: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4448: Raise USAGE_EXCEPTION;
4449: END IF;
4450:
4671: service_exception EXCEPTION;
4672: sub_service_exception EXCEPTION;
4673: BEGIN
4674: IF (l_write_log) THEN
4675: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Service/Ext Warranty Item Starts***');
4676: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Id: ' || p_top_line_id);
4677: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Start Date: ' || p_top_line_start_date);
4678: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
4679: END IF;
4672: sub_service_exception EXCEPTION;
4673: BEGIN
4674: IF (l_write_log) THEN
4675: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Service/Ext Warranty Item Starts***');
4676: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Id: ' || p_top_line_id);
4677: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Start Date: ' || p_top_line_start_date);
4678: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
4679: END IF;
4680:
4673: BEGIN
4674: IF (l_write_log) THEN
4675: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Service/Ext Warranty Item Starts***');
4676: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Id: ' || p_top_line_id);
4677: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Start Date: ' || p_top_line_start_date);
4678: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
4679: END IF;
4680:
4681: p_return_status := 'S';
4674: IF (l_write_log) THEN
4675: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Service/Ext Warranty Item Starts***');
4676: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Id: ' || p_top_line_id);
4677: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Start Date: ' || p_top_line_start_date);
4678: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
4679: END IF;
4680:
4681: p_return_status := 'S';
4682: l_level_elements_count := 0;
4699: END IF;
4700:
4701: --End mchoudha Bug#3537100
4702: IF (l_write_log) THEN
4703: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Going to process all sublines for top line '||p_top_line_id);
4704: END IF;
4705:
4706: FOR l_covlvl_rec in l_subline_csr(p_top_line_id )
4707: LOOP
4709:
4710: /*FOR BILLING REPORT*/
4711: l_subline_id := l_covlvl_rec.id;
4712: IF (l_write_log) THEN
4713: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => inside FOR l_covlvl_rec Processing Coverage Id: '||l_covlvl_rec.id);
4714: END IF;
4715:
4716: level_elements_tab.delete;
4717:
4726: X_MSG_DATA => l_msg_data,
4727: X_NEXT_LEVEL_ELEMENT => level_elements_tab );
4728:
4729: IF (l_write_log) THEN
4730: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_UTIL_PUB.get_next_level_element l_return_status '||l_return_status);
4731: END IF;
4732:
4733: IF (l_return_status <> 'S') THEN
4734: oks_bill_rec_pub.get_message
4739: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4740: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4741: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4742:
4743: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Creation of get_next_level_element For coverage Id: '||l_covlvl_rec.id );
4744: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
4745:
4746: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4747:
4740: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4741: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4742:
4743: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Creation of get_next_level_element For coverage Id: '||l_covlvl_rec.id );
4744: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
4745:
4746: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4747:
4748: RAISE sub_service_exception;
4759:
4760: l_level_elements_count := l_level_elements_count + level_elements_tab.count;
4761:
4762: IF (l_write_log) THEN
4763: FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Period for coverage: ' || no_of_cycles);
4764: END IF;
4765:
4766: OPEN qty_uom_csr(l_covlvl_rec.id);
4767: FETCH qty_uom_csr into qty_uom_rec;
4767: FETCH qty_uom_csr into qty_uom_rec;
4768: CLOSE qty_uom_csr;
4769:
4770: IF (l_write_log) THEN
4771: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Going inside WHILE (e_ptr <= no_of_cycles) ');
4772: END IF;
4773:
4774: WHILE (e_ptr <= no_of_cycles)
4775: LOOP
4791: l_calc_rec.l_calc_edate := l_covlvl_rec.date_terminated - 1;
4792: END IF;
4793:
4794: IF (l_write_log) THEN
4795: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Interface Date: '||l_inv_date);
4796: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Amount : ' || l_amount);
4797: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Start Date : '||l_calc_rec.l_calc_sdate);
4798: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage End Date : '||l_calc_rec.l_calc_edate);
4799: END IF;
4792: END IF;
4793:
4794: IF (l_write_log) THEN
4795: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Interface Date: '||l_inv_date);
4796: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Amount : ' || l_amount);
4797: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Start Date : '||l_calc_rec.l_calc_sdate);
4798: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage End Date : '||l_calc_rec.l_calc_edate);
4799: END IF;
4800:
4793:
4794: IF (l_write_log) THEN
4795: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Interface Date: '||l_inv_date);
4796: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Amount : ' || l_amount);
4797: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Start Date : '||l_calc_rec.l_calc_sdate);
4798: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage End Date : '||l_calc_rec.l_calc_edate);
4799: END IF;
4800:
4801: IF (trunc(l_inv_date) <= trunc(p_date)) THEN
4794: IF (l_write_log) THEN
4795: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Interface Date: '||l_inv_date);
4796: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Amount : ' || l_amount);
4797: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Start Date : '||l_calc_rec.l_calc_sdate);
4798: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage End Date : '||l_calc_rec.l_calc_edate);
4799: END IF;
4800:
4801: IF (trunc(l_inv_date) <= trunc(p_date)) THEN
4802:
4805: l_ar_inv_date := sysdate;
4806: END IF;
4807:
4808: IF (l_write_log) THEN
4809: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => l_ar_inv_date '||l_ar_inv_date);
4810: END IF;
4811:
4812: l_ptr := 1;
4813:
4824: L_MSG_DATA => l_msg_data
4825: );
4826:
4827: IF (l_write_log) THEN
4828: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_REC_PUB.create_bank_Account l_return_status '||l_return_status);
4829: END IF;
4830:
4831: IF (l_return_status <> 'S') THEN
4832: oks_bill_rec_pub.get_message
4837: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4838: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4839: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4840:
4841: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Creation of bank account For coverage Id: '||l_covlvl_rec.id );
4842: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
4843:
4844: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4845:
4838: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4839: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4840:
4841: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Creation of bank account For coverage Id: '||l_covlvl_rec.id );
4842: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
4843:
4844: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4845:
4846: RAISE sub_service_exception;
4863: X_BCL_ID => l_bcl_id
4864: );
4865:
4866: IF (l_write_log) THEN
4867: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_REC_PUB.Insert_bcl l_return_status '||l_return_status);
4868: END IF;
4869:
4870: IF (l_return_status <> 'S') THEN
4871: oks_bill_rec_pub.get_message(
4875: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4876: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4877: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4878:
4879: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Insert BCL For coverage Id: ' ||l_covlvl_rec.id );
4880: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
4881:
4882: RAISE sub_service_exception;
4883: END IF;
4876: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4877: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4878:
4879: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Insert BCL For coverage Id: ' ||l_covlvl_rec.id );
4880: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
4881:
4882: RAISE sub_service_exception;
4883: END IF;
4884:
4909:
4910: END IF; -- (trunc(l_inv_date) <= trunc(p_date))
4911:
4912: IF ( l_write_log) THEN
4913: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => l_cov_tbl.count '||l_cov_tbl.count);
4914: END IF;
4915:
4916: IF (l_cov_tbl.count > 0) THEN
4917: p_ar_feeder_ctr := 1;
4929:
4930: END IF;
4931:
4932: IF (l_write_log) THEN
4933: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_REC_PUB.Insert_all_subline l_return_status '||l_return_status);
4934: END IF;
4935:
4936: IF (l_return_status <> 'S') THEN
4937: oks_bill_rec_pub.get_message(
4941: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4942: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4943: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4944:
4945: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Bill_Service_Item => Insert into sublines table failed Contract line id :'||p_top_line_id);
4946: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
4947: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4948: RAISE sub_service_exception;
4949: END IF;
4942: l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
4943: l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
4944:
4945: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Bill_Service_Item => Insert into sublines table failed Contract line id :'||p_top_line_id);
4946: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
4947: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
4948: RAISE sub_service_exception;
4949: END IF;
4950:
4958: AND date_start <= l_calc_rec.l_calc_sdate;
4959: END IF;
4960:
4961: IF (l_write_log) THEN
4962: FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After updating oks_level_elements ');
4963: END IF;
4964:
4965: e_ptr := e_ptr + 1;
4966:
5005: p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
5006:
5007: END IF;
5008:
5009: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed- sub_service_exception exception raised for coverage: '||l_covlvl_rec.id||' With Error: '||sqlerrm );
5010: EXIT;
5011: WHEN OTHERS THEN
5012: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
5013: /* FOR BILLING REPORT */
5035: p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
5036:
5037: END IF;
5038: p_return_status := 'E';
5039: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed- when others exception raised for coverage '||l_covlvl_rec.id||' With Error: '||sqlerrm );
5040: EXIT;
5041: END;
5042: END LOOP; -- FOR loop for covered level
5043:
5062: l_processed_lines_tbl.DELETE(l_pr_tbl_idx) ;
5063: END IF;
5064:
5065: IF (l_write_log) THEN
5066: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Service/Ext Warranty Item Ends ***');
5067: END IF;
5068:
5069:
5070: EXCEPTION
5074: IF (l_summary_yn = 'Y') THEN
5075: p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines + 1;
5076: p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + nvl(l_line_total,0);
5077: END IF;
5078: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed- when SERVICE_EXCEPTION exception raised '||sqlerrm );
5079: WHEN OTHERS THEN
5080: p_return_status := 'E';
5081: /* FOR BILLING REPORT */
5082: IF (l_summary_yn = 'Y') THEN
5082: IF (l_summary_yn = 'Y') THEN
5083: p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines + 1;
5084: p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + nvl(l_line_total,0);
5085: END IF;
5086: FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed-outside when OTHERS exception raised '||sqlerrm );
5087: END Bill_Service_Item;
5088:
5089:
5090: Procedure Bill_Subscription_item(
5133: p_return_status := 'S';
5134: level_elements_tab.delete;
5135:
5136: IF (l_write_log) THEN
5137: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Subscription Item ***');
5138: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
5139: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
5140: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
5141: END IF;
5134: level_elements_tab.delete;
5135:
5136: IF (l_write_log) THEN
5137: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Subscription Item ***');
5138: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
5139: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
5140: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
5141: END IF;
5142:
5135:
5136: IF (l_write_log) THEN
5137: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Subscription Item ***');
5138: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
5139: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
5140: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
5141: END IF;
5142:
5143: oks_bill_util_pub.get_next_level_element(
5136: IF (l_write_log) THEN
5137: FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Subscription Item ***');
5138: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
5139: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
5140: FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
5141: END IF;
5142:
5143: oks_bill_util_pub.get_next_level_element(
5144: p_api_version => l_api_version,
5158: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
5159: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '||
5160: sqlerrm||'. Error Message: '||l_msg_data ;
5161:
5162: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in getting next level ');
5163:
5164: Raise SUBSRIPTION_EXCEPTION;
5165: END IF;
5166:
5207: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
5208: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
5209:
5210: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
5211: FND_FILE.PUT_LINE( FND_FILE.LOG,' Failed in Creation of bank account');
5212: FND_FILE.PUT_LINE( FND_FILE.LOG,' Rolling Back the Whole Service '||p_top_line_id );
5213:
5214: RAISE SUBSRIPTION_EXCEPTION;
5215: END IF;
5208: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
5209:
5210: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
5211: FND_FILE.PUT_LINE( FND_FILE.LOG,' Failed in Creation of bank account');
5212: FND_FILE.PUT_LINE( FND_FILE.LOG,' Rolling Back the Whole Service '||p_top_line_id );
5213:
5214: RAISE SUBSRIPTION_EXCEPTION;
5215: END IF;
5216: ***/
5241: l_msg_data => l_msg_data);
5242: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
5243: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
5244:
5245: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bcl ');
5246: Raise SUBSRIPTION_EXCEPTION;
5247: END IF;
5248:
5249: IF (p_prv <> 2) THEN
5288: P_MSG_DATA => l_msg_data
5289: );
5290:
5291: IF (l_write_log) THEN
5292: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Status after insert into sublines '||l_return_status );
5293: END IF;
5294:
5295: IF (l_return_status <> 'S') THEN
5296: oks_bill_rec_pub.get_message(
5297: l_msg_cnt => l_msg_count,
5298: l_msg_data => l_msg_data);
5299: l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N ' ;
5300: l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
5301: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Insert into sublines table failed Contract line id : '||p_top_line_id);
5302: --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION ');
5303: Raise SUBSRIPTION_EXCEPTION;
5304: END IF;
5305:
5530:
5531: --mchoudha Fix for bug#4198616
5532: --initializing the variables in case of parallel workers
5533: l_yes_no := Fnd_profile.value('OKS_BILLING_REPORT_AND_LOG');
5534: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => OKS: Billing Report And Log is set to '||l_yes_no);
5535:
5536: --Ceredian ER
5537: l_bulk_usage := FND_PROFILE.VALUE('OKS_USAGE_BILL_QP_BULK_CALL');
5538:
5536: --Ceredian ER
5537: l_bulk_usage := FND_PROFILE.VALUE('OKS_USAGE_BILL_QP_BULK_CALL');
5538:
5539: If l_write_log then
5540: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill processing starts ');
5541: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => OKS_USAGE_BILL_QP_BULK_CALL is set to '||l_bulk_usage);
5542: End If;
5543:
5544: If l_yes_no = 'YES' then
5537: l_bulk_usage := FND_PROFILE.VALUE('OKS_USAGE_BILL_QP_BULK_CALL');
5538:
5539: If l_write_log then
5540: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill processing starts ');
5541: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => OKS_USAGE_BILL_QP_BULK_CALL is set to '||l_bulk_usage);
5542: End If;
5543:
5544: If l_yes_no = 'YES' then
5545: l_write_log := TRUE;
5554: FETCH l_uom_csr into l_uom_code;
5555: CLOSE l_uom_csr;
5556:
5557: IF l_uom_code Is Null Then
5558: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Time Units Of measure not set for DAY');
5559: Raise G_EXCEPTION_HALT_VALIDATION;
5560: END IF;
5561:
5562: --/*Set The p_date which is used to control the program*/
5573: l_billrep_errtbl_idx := -1 ;
5574: --End mchoudha Bug#3537100
5575:
5576: If l_write_log then
5577: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => Opening billing_process cursor from '||p_process_from||' to '||p_process_to);
5578: End If;
5579:
5580:
5581:
5585: FETCH billing_process bulk collect
5586: INTO chr_id, cle_id,l_line_no LIMIT 10000;
5587:
5588: If l_write_log then
5589: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After Fetch billing_process cursor count of line ids fetched '||cle_id.count);
5590: End If;
5591:
5592: IF cle_id.COUNT > 0 THEN --chk for count
5593:
5599:
5600: DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
5601:
5602: IF l_write_log THEN
5603: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 opening cursor l_bill_line_csr line id '||cle_id(i)||' header ID '||chr_id(i)||' line number '||l_line_no(i));
5604: END IF;
5605:
5606: /*****
5607: The policy context is set to multiple for Bug# 6158988
5613:
5614: OPEN l_bill_line_csr (chr_id(i),cle_id(i));
5615: FETCH l_bill_line_csr into l_bill_rec ;
5616: IF (l_bill_line_csr%notfound) THEN
5617: FND_FILE.PUT_LINE(FND_FILE.LOG,'not found l_bill_line_csr for line id '||cle_id(i)||' for contract id '||chr_id(i));
5618: CLOSE l_bill_line_csr;
5619: RAISE MAIN_LINE_EXCEPTION;
5620: END IF;
5621: CLOSE l_bill_line_csr;
5624: l_select_counter := l_select_counter + 1;
5625: l_ar_Feeder_ctr := 0;
5626:
5627: IF l_write_log THEN
5628: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Contract# : '||l_bill_rec.Contract_number);
5629: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Modifier: '||l_bill_rec.Contract_number_modifier);
5630: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Parameter Default Date ' || P_date);
5631: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Start# : '||l_bill_rec.period_start);
5632: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Type: '||l_bill_rec.period_type);
5625: l_ar_Feeder_ctr := 0;
5626:
5627: IF l_write_log THEN
5628: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Contract# : '||l_bill_rec.Contract_number);
5629: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Modifier: '||l_bill_rec.Contract_number_modifier);
5630: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Parameter Default Date ' || P_date);
5631: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Start# : '||l_bill_rec.period_start);
5632: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Type: '||l_bill_rec.period_type);
5633: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Price_uom ' ||l_bill_rec.price_uom);
5626:
5627: IF l_write_log THEN
5628: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Contract# : '||l_bill_rec.Contract_number);
5629: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Modifier: '||l_bill_rec.Contract_number_modifier);
5630: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Parameter Default Date ' || P_date);
5631: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Start# : '||l_bill_rec.period_start);
5632: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Type: '||l_bill_rec.period_type);
5633: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Price_uom ' ||l_bill_rec.price_uom);
5634: END IF;
5627: IF l_write_log THEN
5628: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Contract# : '||l_bill_rec.Contract_number);
5629: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Modifier: '||l_bill_rec.Contract_number_modifier);
5630: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Parameter Default Date ' || P_date);
5631: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Start# : '||l_bill_rec.period_start);
5632: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Type: '||l_bill_rec.period_type);
5633: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Price_uom ' ||l_bill_rec.price_uom);
5634: END IF;
5635:
5628: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Contract# : '||l_bill_rec.Contract_number);
5629: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Modifier: '||l_bill_rec.Contract_number_modifier);
5630: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Parameter Default Date ' || P_date);
5631: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Start# : '||l_bill_rec.period_start);
5632: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Type: '||l_bill_rec.period_type);
5633: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Price_uom ' ||l_bill_rec.price_uom);
5634: END IF;
5635:
5636: -- Commented as part of R12. Authoring org id changed to org_id (MOAC)
5629: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Modifier: '||l_bill_rec.Contract_number_modifier);
5630: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Parameter Default Date ' || P_date);
5631: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Start# : '||l_bill_rec.period_start);
5632: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Type: '||l_bill_rec.period_type);
5633: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Price_uom ' ||l_bill_rec.price_uom);
5634: END IF;
5635:
5636: -- Commented as part of R12. Authoring org id changed to org_id (MOAC)
5637: -- okc_context.set_okc_org_context(l_bill_Rec.authoring_org_id, Null);
5637: -- okc_context.set_okc_org_context(l_bill_Rec.authoring_org_id, Null);
5638: okc_context.set_okc_org_context(l_bill_Rec.org_id, Null);
5639:
5640: IF l_write_log THEN
5641: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After setting okc_context.set_okc_org_context for org '||l_bill_Rec.org_id);
5642: END IF;
5643:
5644:
5645: l_okl_flag := 0; --- check for OKL contract
5648: FETCH l_okl_contract_csr into l_okl_flag;
5649: CLOSE l_okl_contract_csr;
5650:
5651: If l_write_log then
5652: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 opening cursor l_bill_line_csr l_okl_flag '||l_okl_flag);
5653: End If;
5654:
5655: /*Invoices are to be generated only for contracts whose
5656: AR_interface_flag = 'Y'
5655: /*Invoices are to be generated only for contracts whose
5656: AR_interface_flag = 'Y'
5657: */
5658: If l_write_log then
5659: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => l_bill_rec.ar_interface_yn '||Nvl(l_bill_rec.ar_interface_yn,'Y'));
5660: End If;
5661:
5662: IF Nvl(l_bill_rec.ar_interface_yn,'Y') = 'Y' THEN -- {
5663:
5669:
5670:
5671:
5672: If l_write_log then
5673: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 initializing Billing Report ');
5674: End If;
5675:
5676: l_billrep_found := FALSE;
5677:
5702: l_billrep_tbl(l_billrep_tbl_idx).Rejected_SubLines_Value := 0;
5703: END IF;
5704:
5705: If l_write_log then
5706: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After initializing Billing Report ');
5707: End If;
5708:
5709: --End mchoudha Bug#3537100
5710:
5712: FETCH qty_uom_csr into qty_uom_rec;
5713: CLOSE qty_uom_csr;
5714:
5715: If l_write_log then
5716: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After opening qty_uom_csr uom_code '||qty_uom_rec.uom_code);
5717: End If;
5718:
5719:
5720: IF (l_bill_rec.lse_id = 46) THEN
5719:
5720: IF (l_bill_rec.lse_id = 46) THEN
5721: -----------------------------------------------------------------
5722: If l_write_log then
5723: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling Bill_Subscription_item for '||l_bill_rec.id);
5724: End If;
5725:
5726: Bill_Subscription_item(
5727: l_bill_rec.dnz_chr_id,
5751: ELSIF (l_bill_rec.lse_id in (1,19)) Then
5752: --Process_service_items
5753: -----------------------------------------------------------------
5754: If l_write_log then
5755: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling Bill_Service_item for '||l_bill_rec.id);
5756: End If;
5757:
5758: Bill_Service_item(
5759: l_bill_rec.dnz_chr_id,
5794: If Nvl(l_bulk_usage,'N') = 'Y' THEN
5795:
5796: -- Process_usage_items
5797: If l_write_log then
5798: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling Bill_Usage_Item_bulk for '||l_bill_rec.id);
5799: End If;
5800: -------------------------------------------------------------------------
5801: -- Begin partial period computation logic
5802: -- Developer Mani Choudhary
5837: Else
5838:
5839: -- Process_usage_items
5840: If l_write_log then
5841: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling Bill_Usage_Item for '||l_bill_rec.id);
5842: End If;
5843: -------------------------------------------------------------------------
5844: -- Begin partial period computation logic
5845: -- Developer Mani Choudhary
5891:
5892: /* skuchima Ceredian ER Added condition to stop calling AR for usage lines*/
5893: IF ( (l_ar_feeder_ctr = 1 AND Nvl(l_bulk_usage,'N') = 'N') OR (l_ar_feeder_ctr =1 and l_bill_rec.lse_id <> 12 ) ) THEN
5894: If l_write_log then
5895: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling OKS_ARFEEDER_PUB.Get_REC_FEEDER for '||l_bill_rec.id);
5896: End If;
5897:
5898: OKS_ARFEEDER_PUB.Get_REC_FEEDER
5899: (
5923: P_PROCESSED_SUB_LINES_TBL => l_processed_sub_lines_tbl,
5924: P_ERROR_MESSAGE => l_processed_lines_tbl(l_pr_tbl_idx).Error_Message,
5925: P_TOP_LINE => l_bill_rec.id) ;
5926:
5927: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => Failed in AR FEEDER :'||' '||l_bill_rec.id);
5928:
5929: ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
5930: Raise Main_Line_Exception;
5931: END IF;
5953: --Added by pmallara begin exception block to fix bug#3961046
5954: BEGIN
5955: l_reject_counter := l_reject_counter + 1;
5956: DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
5957: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => MAIN LINE EXCEPTION RAISE'||' '||sqlerrm);
5958: RETCODE := 1;
5959: EXCEPTION
5960: WHEN OTHERS THEN
5961: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- when trying to rollback inside MAIN_LINE_EXCEPTION '||sqlerrm );
5957: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => MAIN LINE EXCEPTION RAISE'||' '||sqlerrm);
5958: RETCODE := 1;
5959: EXCEPTION
5960: WHEN OTHERS THEN
5961: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- when trying to rollback inside MAIN_LINE_EXCEPTION '||sqlerrm );
5962: exit;
5963: END;
5964:
5965: WHEN OTHERS THEN
5976: l_billrep_err_tbl(l_billrep_errtbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
5977:
5978: l_reject_counter := l_reject_counter + 1;
5979: DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
5980: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- when others exception raised in Mainline loop '||sqlerrm );
5981: RETCODE := 1;
5982:
5983: --Bug#5378184
5984: l_err_code := substr(sqlerrm,instr(sqlerrm,'-')+1,5);
5988: --End Bug#5378184
5989:
5990: EXCEPTION
5991: WHEN OTHERS THEN
5992: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- when trying to rollback inside WHEN OTHERS exception'||sqlerrm );
5993: exit;
5994: END;
5995: END;
5996: END LOOP; --end of loop for cle_id tbl
6034: ,p_line_to => P_process_to
6035: ,x_return_status => l_return_status);
6036:
6037: IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
6038: FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => *** Failed to create Billing Report ***' );
6039: END IF;
6040: End If;
6041:
6042: If l_write_log then
6039: END IF;
6040: End If;
6041:
6042: If l_write_log then
6043: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill processing Ends ');
6044: End If;
6045:
6046: ---- Added if clause to issue warning for any data related issue
6047: IF (RETCODE <> 1) THEN
6052:
6053: WHEN G_EXCEPTION_HALT_VALIDATION THEN
6054: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6055: RETCODE := 2;
6056: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => when g_exception_rollback raised'||sqlerrm);
6057: DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
6058: WHEN G_EXCEPTION_ROLLBACK THEN
6059: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => when g_exception_rollback raised'||sqlerrm);
6060: DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
6055: RETCODE := 2;
6056: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => when g_exception_rollback raised'||sqlerrm);
6057: DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
6058: WHEN G_EXCEPTION_ROLLBACK THEN
6059: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => when g_exception_rollback raised'||sqlerrm);
6060: DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
6061:
6062: WHEN OTHERS THEN
6063: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6061:
6062: WHEN OTHERS THEN
6063: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6064: RETCODE := 2;
6065: FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => when others raised'||sqlerrm);
6066: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
6067: G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
6068: DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
6069:
6126: l_original_org_id NUMBER; /*skuchima*/
6127:
6128: BEGIN
6129:
6130: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main Starts');
6131:
6132: l_yes_no := Fnd_profile.value('OKS_BILLING_REPORT_AND_LOG');
6133:
6134: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main => OKS: Billing Report And Log is set to '||l_yes_no);
6130: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main Starts');
6131:
6132: l_yes_no := Fnd_profile.value('OKS_BILLING_REPORT_AND_LOG');
6133:
6134: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main => OKS: Billing Report And Log is set to '||l_yes_no);
6135:
6136: If l_yes_no = 'YES' then
6137: l_write_log := TRUE;
6138: l_write_report := TRUE;
6146: --mchoudha fixed bug#4729936
6147: --IF (nvl(fnd_profile.value('OKC_VIEW_K_BY_ORG'),'N') = 'Y' ) THEN
6148: -- l_org_id := fnd_profile.value('ORG_ID');
6149: If l_write_log then
6150: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => parameter Org ID is '||l_org_id);
6151: End If;
6152: -- END IF;
6153:
6154: If l_write_log then
6151: End If;
6152: -- END IF;
6153:
6154: If l_write_log then
6155: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Preview Y/N ='||P_process);
6156: End If;
6157: /*Added for bug:10191601*/
6158: p_access_mode := mo_global.get_access_mode;
6159: /*p_access_mode - specifies the operating unit access. 'S' for Single, 'M' for Multiple */
6171: END IF;
6172:
6173: user_id := FND_GLOBAL.USER_ID;
6174: If l_write_log then
6175: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => User_Id ='||to_char(user_id));
6176: End If;
6177:
6178: --nechatur 29-Nov-2005 for bug#4459229
6179: --p_date := nvl(trunc(p_default_date),trunc(sysdate));
6187: --truncated during the restart of the parent
6188: If(FND_CONC_GLOBAL.request_data is null) THEN
6189:
6190: If l_write_log then
6191: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Truncating oks_process_billing');
6192: End If;
6193:
6194: v_cursor := DBMS_SQL.OPEN_CURSOR;
6195: /*Following line is required to avoid GSCC errors at ARU creation.
6211: */
6212: IF (FND_CONC_GLOBAL.request_data is null) THEN -- not a restart of parent
6213:
6214: If l_write_log then
6215: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Call to OKS_ARFEEDER_PUB.Get_REC_FEEDER to interface termination records');
6216: End If;
6217:
6218: OKS_ARFEEDER_PUB.Get_REC_FEEDER
6219: (
6231: p_billrep_err_tbl_idx => l_billrep_errtbl_idx
6232: ) ;
6233:
6234: If l_write_log then
6235: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Call to OKS_ARFEEDER_PUB.Get_REC_FEEDER to interface termination records l_return_status '||l_return_status);
6236: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Access_Mode after AR call - '||mo_global.get_access_mode);
6237: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org Context after AR call - '||okc_context.get_okc_org_id);
6238: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Access Mode - '||p_Access_Mode);
6239: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Org Context - '||l_original_org_id);
6232: ) ;
6233:
6234: If l_write_log then
6235: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Call to OKS_ARFEEDER_PUB.Get_REC_FEEDER to interface termination records l_return_status '||l_return_status);
6236: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Access_Mode after AR call - '||mo_global.get_access_mode);
6237: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org Context after AR call - '||okc_context.get_okc_org_id);
6238: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Access Mode - '||p_Access_Mode);
6239: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Org Context - '||l_original_org_id);
6240: End If;
6233:
6234: If l_write_log then
6235: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Call to OKS_ARFEEDER_PUB.Get_REC_FEEDER to interface termination records l_return_status '||l_return_status);
6236: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Access_Mode after AR call - '||mo_global.get_access_mode);
6237: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org Context after AR call - '||okc_context.get_okc_org_id);
6238: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Access Mode - '||p_Access_Mode);
6239: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Org Context - '||l_original_org_id);
6240: End If;
6241:
6234: If l_write_log then
6235: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Call to OKS_ARFEEDER_PUB.Get_REC_FEEDER to interface termination records l_return_status '||l_return_status);
6236: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Access_Mode after AR call - '||mo_global.get_access_mode);
6237: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org Context after AR call - '||okc_context.get_okc_org_id);
6238: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Access Mode - '||p_Access_Mode);
6239: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Org Context - '||l_original_org_id);
6240: End If;
6241:
6242: /*Set the org context again after ar call skuchima bug16452991 */
6235: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Call to OKS_ARFEEDER_PUB.Get_REC_FEEDER to interface termination records l_return_status '||l_return_status);
6236: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Access_Mode after AR call - '||mo_global.get_access_mode);
6237: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org Context after AR call - '||okc_context.get_okc_org_id);
6238: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Access Mode - '||p_Access_Mode);
6239: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original Org Context - '||l_original_org_id);
6240: End If;
6241:
6242: /*Set the org context again after ar call skuchima bug16452991 */
6243: mo_global.set_policy_context(p_access_mode,l_original_org_id);
6255: IF (p_grp_id is not null) or (p_customer_id is not null) or
6256: (p_category is not null) or (l_org_id is not null) or
6257: (p_contract_hdr_id is not null) Then
6258: If l_write_log then
6259: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameters Passed => Contract Id '||p_contract_hdr_id);
6260: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer '||P_customer_id|| ' Category '||p_category);
6261: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org '||l_org_id|| ' Group '||p_grp_id);
6262: End If;
6263:
6256: (p_category is not null) or (l_org_id is not null) or
6257: (p_contract_hdr_id is not null) Then
6258: If l_write_log then
6259: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameters Passed => Contract Id '||p_contract_hdr_id);
6260: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer '||P_customer_id|| ' Category '||p_category);
6261: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org '||l_org_id|| ' Group '||p_grp_id);
6262: End If;
6263:
6264: End If;
6257: (p_contract_hdr_id is not null) Then
6258: If l_write_log then
6259: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameters Passed => Contract Id '||p_contract_hdr_id);
6260: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer '||P_customer_id|| ' Category '||p_category);
6261: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org '||l_org_id|| ' Group '||p_grp_id);
6262: End If;
6263:
6264: End If;
6265:
6268: (p_category is null) and (l_org_id is null) and
6269: (p_contract_hdr_id is null) Then
6270:
6271: If l_write_log then --added by ansraj for 14016663
6272: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Blind Query ' || ': with date '||p_date);
6273: End If;
6274:
6275: /*code hook is used as part of bug7668259*/
6276: IF l_hook = 1 AND l_date_interface_start IS NOT NULL THEN
6275: /*code hook is used as part of bug7668259*/
6276: IF l_hook = 1 AND l_date_interface_start IS NOT NULL THEN
6277:
6278: If l_write_log then --added by ansraj for 14016663
6279: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using Serial plan as l_hook value is 1 with
6280: date_interface_start as: '|| l_date_interface_start );
6281: End If;
6282:
6283: INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6319: /*added for bug7668259*/
6320: ELSIF l_hint = 'FULL' THEN /*full parallel processing of data*/
6321:
6322: If l_write_log then -- by ansraj for bug 14016663
6323: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using FULL Parallel plan as l_hint value is FULL');
6324: End if;
6325:
6326: /*Added for bug:10191601*/
6327: /*p_access_mode = 'M' for multi-org, 'S' for Single org.
6416: END IF;
6417: /*modified for bug7668259*/
6418: ELSE /*Semi parallel processing of data */
6419: If l_write_log then -- by ansraj for bug 14016663
6420: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using Semi Parallel plan as l_hook value is 0');
6421: End if;
6422:
6423: INSERT /*+ append */ into oks_process_billing(chr_id,cle_id,line_no,currency_code)
6424: SELECT /*+ leading(lvl,line,as1,hdr,sts) use_nl(line,hdr) use_hash(as1,sts) swap_join_inputs(as1) swap_join_inputs(sts) */
6458: (p_grp_id is null) and (p_customer_id is null) and
6459: (p_category is null) and (l_org_id is not null) and
6460: (p_contract_hdr_id is null) Then
6461: If l_write_log then -- by ansraj for bug 14016663
6462: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for Org id');
6463: End if;
6464: /*modified for bug7668259*/
6465: IF l_hook = 1 AND l_date_interface_start IS NOT NULL THEN
6466:
6464: /*modified for bug7668259*/
6465: IF l_hook = 1 AND l_date_interface_start IS NOT NULL THEN
6466:
6467: If l_write_log then -- by ansraj for bug 14016663
6468: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using Serial plan as l_hook value is 1 with date_interface_start as: '||l_date_interface_start);
6469: End if;
6470:
6471: INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6472: SELECT hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
6505: /*modified for bug7668259*/
6506: ELSE /*l_hook = 0, so parallel processing of data*/
6507:
6508: If l_write_log then -- by ansraj for bug 14016663
6509: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using Parallel plan as l_hook value is 0') ;
6510: End if;
6511:
6512: INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6513: SELECT hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
6556: Elsif
6557: (p_contract_hdr_id is not null) Then
6558:
6559: If l_write_log then -- by ansraj for bug 14016663
6560: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for Contract id');
6561: End if;
6562:
6563: INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6564:
6589: (p_grp_id is not null) and (p_customer_id is null) and
6590: (p_category is null) and (l_org_id is not null) and
6591: (p_contract_hdr_id is null) Then
6592: if l_write_log then -- by ansraj for bug 14016663
6593: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for contract group and org id');
6594: End if;
6595: /*modified for bug7668259*/
6596: IF l_hook = 1 AND l_date_interface_start IS NOT NULL THEN
6597: if l_write_log then -- by ansraj for bug 14016663
6594: End if;
6595: /*modified for bug7668259*/
6596: IF l_hook = 1 AND l_date_interface_start IS NOT NULL THEN
6597: if l_write_log then -- by ansraj for bug 14016663
6598: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using Serial plan as l_hook value is 1 with date_interface_start as: '||l_date_interface_start);
6599: End if;
6600:
6601: INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6602: SELECT /*+ leading(lvl) ORDERED USE_HASH(line hdr sts as1 cgp)
6643: /*modified for bug7668259*/
6644: ELSE /*l_hook = 0, so normal processing of data*/
6645:
6646: If l_write_log then -- by ansraj for bug 14016663
6647: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using Normal Plan as l_hook value is 0');
6648: End if;
6649: INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6650: SELECT /*+ leading(lvl) ORDERED USE_HASH(line hdr sts as1 cgp)
6651: swap_join_inputs(hdr) swap_join_inputs(sts) swap_join_inputs(cgp) */
6694: (p_category is not null) and (l_org_id is not null) and
6695: (p_contract_hdr_id is null) Then
6696:
6697: If l_write_log then -- by ansraj for bug 14016663
6698: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for category and org id');
6699: End if;
6700: /*modified for bug7668259*/
6701: IF l_hook = 1 AND l_date_interface_start IS NOT NULL THEN
6702: If l_write_log then -- by ansraj for bug 14016663
6699: End if;
6700: /*modified for bug7668259*/
6701: IF l_hook = 1 AND l_date_interface_start IS NOT NULL THEN
6702: If l_write_log then -- by ansraj for bug 14016663
6703: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using Serial plan as l_hook value is 1 with date_interface_start as: '||l_date_interface_start);
6704: End if;
6705:
6706: INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6707: SELECT hdr.id hdr_id,line.id line_id,rownum,hdr.currency_code
6731:
6732: /*modified for bug7668259*/
6733: ELSE /*l_hook = 0, so normal processing of data*/
6734: If l_write_log then -- by ansraj for bug 14016663
6735: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Using Normal plan as l_hook value is 0');
6736: End if;
6737:
6738: INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6739: SELECT hdr.id hdr_id,line.id line_id,rownum,hdr.currency_code
6761: Where rel.cle_id is not null ); */ ---Commented for ER 16039680
6762: END IF;
6763: Else
6764: If l_write_log then -- by ansraj for bug 14016663
6765: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for rest of the parameters');
6766: End if;
6767:
6768: INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
6769:
6806: commit;
6807: select count(*) into l_line_no from oks_process_billing;
6808:
6809: If l_write_log then
6810: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Number of records in oks_process_billing is '||l_line_no);
6811: End If;
6812:
6813:
6814: END IF;
6816: /* As part of bug13105205 removed the hardcoded value to l_subrequests and assigning the value from profile skuchima*/
6817: l_subrequests := nvl(FND_PROFILE.VALUE('OKS_NO_CHILD_REQUESTS_MAIN_BILLING'),30);
6818:
6819: If l_write_log then
6820: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Profile option OKS: Number of Child Requests for Service Contracts Main Billing '||l_subrequests);
6821: End If;
6822:
6823:
6824: IF (p_contract_hdr_id is null) AND -- not a specific contract
6826: (nvl(FND_PROFILE.VALUE('OKS_PARALLEL_WORKER'),'NO') = 'YES') THEN
6827:
6828: -- l_line_no represent total records to be processed here
6829: If l_write_log then
6830: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Profile option OKS: Parallel Worker is set to YES');
6831: End If;
6832:
6833: IF (l_line_no > MAX_SINGLE_REQUEST ) THEN
6834: -- populate lo,hi varrays
6855:
6856: IF (l_ret = 0) THEN
6857: errbuf := fnd_message.get;
6858: retcode := 2;
6859: FND_FILE.PUT_LINE (FND_FILE.LOG,'Sub-request failed to submit: '
6860: || errbuf);
6861: return;
6862: ELSE
6863: If l_write_log then -- by ansraj for bug 14016663
6860: || errbuf);
6861: return;
6862: ELSE
6863: If l_write_log then -- by ansraj for bug 14016663
6864: FND_FILE.PUT_LINE (FND_FILE.LOG,'Sub-request '||to_char(l_ret)||
6865: ' submitted for line numbers '||l_min_boundary||' to '||l_max_boundary);
6866: End if;
6867: END IF;
6868:
6885:
6886: ELSIF ((FND_CONC_GLOBAL.request_data is not null ) AND
6887: (nvl(FND_PROFILE.VALUE('OKS_PARALLEL_WORKER'),'NO') = 'YES')) THEN
6888: -- restart detected (sub-requests finished)...cleanup and exit.
6889: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Commiting here');
6890: COMMIT;
6891:
6892: v_cursor := DBMS_SQL.OPEN_CURSOR;
6893: /* Following line is required to avoid GSCC errors at ARU creation.
6903: return;
6904: END IF; -- parent test
6905:
6906: If l_write_log then
6907: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => B4 Calling OKS_BILLING_PUB.CALCULATE_BILL for sequential run');
6908: End If;
6909:
6910: OKS_BILLING_PUB.CALCULATE_BILL
6911: (l_errbuf, l_retcode, 1, l_flag,
6912: p_date,1 ,l_line_no,
6913: p_prv);
6914:
6915: If l_write_log then
6916: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Calling OKS_BILLING_PUB.CALCULATE_BILL for sequential run');
6917: End If;
6918:
6919: IF (l_retcode = 0) THEN
6920: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main is successfully completed');
6916: FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Calling OKS_BILLING_PUB.CALCULATE_BILL for sequential run');
6917: End If;
6918:
6919: IF (l_retcode = 0) THEN
6920: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main is successfully completed');
6921: conc_ret_code := SUCCESS;
6922: RETCODE := 0;
6923: ELSIF (l_retcode = 1) THEN
6924: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main is successfully completed with warnings');
6920: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main is successfully completed');
6921: conc_ret_code := SUCCESS;
6922: RETCODE := 0;
6923: ELSIF (l_retcode = 1) THEN
6924: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main is successfully completed with warnings');
6925: conc_ret_code := WARNING;
6926: RETCODE := 1;
6927: ELSE
6928: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main completed with errors');
6924: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main is successfully completed with warnings');
6925: conc_ret_code := WARNING;
6926: RETCODE := 1;
6927: ELSE
6928: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main completed with errors');
6929: conc_ret_code := ERROR;
6930: RETCODE := 2;
6931: DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_MAIN_BILLING');
6932: END IF;
6942: v_truncstring := 'Truncate Table OKS'||'.oks_process_billing';
6943: DBMS_SQL.PARSE(v_cursor,v_truncstring,DBMS_SQL.V7);
6944: DBMS_SQL.CLOSE_CURSOR(v_cursor);
6945:
6946: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main Ends');
6947:
6948: EXCEPTION
6949: WHEN UTL_FILE.INVALID_PATH THEN
6950: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE LOCATION OR NAME WAS INVALID');
6946: FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main Ends');
6947:
6948: EXCEPTION
6949: WHEN UTL_FILE.INVALID_PATH THEN
6950: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE LOCATION OR NAME WAS INVALID');
6951: WHEN UTL_FILE.INVALID_MODE THEN
6952: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE OPEN MODE STRING WAS INVALID');
6953: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
6954: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE HANDLE WAS INVALID');
6948: EXCEPTION
6949: WHEN UTL_FILE.INVALID_PATH THEN
6950: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE LOCATION OR NAME WAS INVALID');
6951: WHEN UTL_FILE.INVALID_MODE THEN
6952: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE OPEN MODE STRING WAS INVALID');
6953: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
6954: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE HANDLE WAS INVALID');
6955: WHEN UTL_FILE.INVALID_OPERATION THEN
6956: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE IS NOT OPEN FOR WRITTING');
6950: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE LOCATION OR NAME WAS INVALID');
6951: WHEN UTL_FILE.INVALID_MODE THEN
6952: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE OPEN MODE STRING WAS INVALID');
6953: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
6954: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE HANDLE WAS INVALID');
6955: WHEN UTL_FILE.INVALID_OPERATION THEN
6956: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE IS NOT OPEN FOR WRITTING');
6957: WHEN UTL_FILE.WRITE_ERROR THEN
6958: FND_FILE.PUT_LINE (FND_FILE.LOG,'OS ERROR OCCURRED DURING WRITE OPERATION');
6952: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE OPEN MODE STRING WAS INVALID');
6953: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
6954: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE HANDLE WAS INVALID');
6955: WHEN UTL_FILE.INVALID_OPERATION THEN
6956: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE IS NOT OPEN FOR WRITTING');
6957: WHEN UTL_FILE.WRITE_ERROR THEN
6958: FND_FILE.PUT_LINE (FND_FILE.LOG,'OS ERROR OCCURRED DURING WRITE OPERATION');
6959:
6960: END Billing_Main;
6954: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE HANDLE WAS INVALID');
6955: WHEN UTL_FILE.INVALID_OPERATION THEN
6956: FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE IS NOT OPEN FOR WRITTING');
6957: WHEN UTL_FILE.WRITE_ERROR THEN
6958: FND_FILE.PUT_LINE (FND_FILE.LOG,'OS ERROR OCCURRED DURING WRITE OPERATION');
6959:
6960: END Billing_Main;
6961:
6962:
7221: IF (l_return_status <> 'S') THEN
7222: OKS_BILL_REC_PUB.GET_MESSAGE(
7223: l_msg_cnt => l_msg_cnt,
7224: l_msg_data => l_msg_data);
7225: FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: Failed in AR FEEDER');
7226: ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
7227: l_retcode := 1;
7228: END IF;
7229:
7227: l_retcode := 1;
7228: END IF;
7229:
7230: IF (l_retcode = 0) THEN
7231: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Supress credit is successfully completed');
7232: conc_ret_code := SUCCESS;
7233: RETCODE := 0;
7234: END IF;
7235:
7236: COMMIT;
7237:
7238: EXCEPTION
7239: WHEN OTHERS THEN
7240: FND_FILE.PUT_LINE( FND_FILE.LOG, 'Supress credit completed with Errors');
7241: conc_ret_code := ERROR;
7242: RETCODE := 2;
7243: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
7244: G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);