[Home] [Help]
5354: Is
5355: Cursor billing_process (p_line_from IN NUMBER,
5356: p_line_to IN NUMBER) IS
5357: SELECT chr_id,cle_id,line_no
5358: FROM oks_process_billing
5359: WHERE line_no between p_line_from and p_line_to;
5360:
5361: -------------------------------------------------------------------------
5362: -- Begin partial period computation logic
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.
6195: /*Following line is required to avoid GSCC errors at ARU creation.
6196: Concatination of string is required to avoid run time error.
6197: Ampersand 1 is taking value from un_oks param in dbdrv command line.*/
6198: --v_truncstring := 'Truncate Table oks';
6199: --v_truncstring := v_truncstring||'.oks_process_billing';
6200: v_truncstring := 'Truncate Table OKS'||'.oks_process_billing';
6201: DBMS_SQL.PARSE(v_cursor,v_truncstring,DBMS_SQL.V7);
6202: DBMS_SQL.CLOSE_CURSOR(v_cursor);
6203: END IF;
6196: Concatination of string is required to avoid run time error.
6197: Ampersand 1 is taking value from un_oks param in dbdrv command line.*/
6198: --v_truncstring := 'Truncate Table oks';
6199: --v_truncstring := v_truncstring||'.oks_process_billing';
6200: v_truncstring := 'Truncate Table OKS'||'.oks_process_billing';
6201: DBMS_SQL.PARSE(v_cursor,v_truncstring,DBMS_SQL.V7);
6202: DBMS_SQL.CLOSE_CURSOR(v_cursor);
6203: END IF;
6204:
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)
6284: SELECT /*+ leading(lvl,line,as1,hdr,sts)
6285: use_nl(line,hdr) use_hash(as1,sts) swap_join_inputs(as1)
6286: swap_join_inputs(sts) */
6287: hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
6330:
6331: IF Nvl(p_access_mode,'X') = 'M' THEN
6332: /*removed the parallel hint and added NO_PUSH_PRED(lvl) as per appsperf team suggestion*/
6333: /*added nl_aj hint as per appsperf team suggestion*/
6334: INSERT /*+ append */ into oks_process_billing
6335: (chr_id,cle_id,line_no,currency_code)
6336: SELECT /*+ leading(lvl,line,as1,hdr,as1)
6337: use_nl(line,hdr) use_hash(as1,sts,oa) swap_join_inputs(as1) swap_join_inputs(oa)
6338: swap_join_inputs(sts)
6372: From okc_k_rel_objs rel
6373: Where rel.cle_id is not null
6374: );*/ ---Commented for ER 16039680
6375: ELSE
6376: INSERT /*+ append */ into oks_process_billing
6377: (chr_id,cle_id,line_no,currency_code)
6378: SELECT /*+ leading(lvl,line,as1,hdr,as1)
6379: use_nl(line,hdr) use_hash(as1,sts) swap_join_inputs(as1)
6380: swap_join_inputs(sts)
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) */
6425: hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
6426: from (Select /*+ parallel(lvl) no_merge */ distinct lvl.parent_cle_id
6427: from oks_level_elements lvl
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
6473: from (
6474: Select distinct lvl.parent_cle_id
6475: from oks_level_elements lvl
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
6514: from /*(
6515: Select /*+ parallel(lvl) distinct lvl.parent_cle_id
6516: from oks_level_elements lvl
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:
6565: SELECT hdr.id hdr_id,line.id line_id,rownum,hdr.currency_code
6566: from okc_k_headers_b hdr, okc_k_lines_b line
6567: WHERE Hdr.id = line.dnz_chr_id
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)
6603: swap_join_inputs(hdr) swap_join_inputs(sts) swap_join_inputs(cgp) */
6604: hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
6605: from (
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) */
6652: hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
6653: from (
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
6708: from okc_k_headers_b hdr, okc_k_lines_b line
6709: WHERE Hdr.id = line.dnz_chr_id
6710: AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
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
6740: from okc_k_headers_b hdr, okc_k_lines_b line
6741: WHERE Hdr.id = line.dnz_chr_id
6742: AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
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:
6770: SELECT hdr.id hdr_id,line.id line_id,rownum,hdr.currency_code
6771: from okc_k_headers_b hdr, okc_k_lines_b line
6772: WHERE Hdr.id = line.dnz_chr_id
6801: Where rel.cle_id is not null ); */ ---Commented for ER 16039680
6802:
6803: End if; -- End if clause for null parameters check
6804:
6805: ---this commit is required for the oks_process_billing table. should not be commented:
6806: commit;
6807: select count(*) into l_line_no from oks_process_billing;
6808:
6809: If l_write_log then
6803: End if; -- End if clause for null parameters check
6804:
6805: ---this commit is required for the oks_process_billing table. should not be commented:
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;
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;
6835:
6836: /* l_subrequests := 30; Bug 13105205*/ -- hard coded.
6837: l_slot_size := ceil(l_line_no / l_subrequests);
6838:
6839: --SELECT ceil(count(*)/10) into l_slot_size from oks_process_billing;
6840: l_min_boundary := 1;
6841: l_max_boundary := l_slot_size;
6842: FOR idx in 1..l_subrequests
6843: LOOP
6892: v_cursor := DBMS_SQL.OPEN_CURSOR;
6893: /* Following line is required to avoid GSCC errors at ARU creation.
6894: Concatination of string is required to avoid run time error */
6895: -- v_truncstring := 'Truncate Table oks';
6896: -- v_truncstring := v_truncstring||'.oks_process_billing';
6897: v_truncstring := 'Truncate Table OKS'||'.oks_process_billing';
6898: DBMS_SQL.PARSE(v_cursor,v_truncstring,DBMS_SQL.V7);
6899: DBMS_SQL.CLOSE_CURSOR(v_cursor);
6900:
6893: /* Following line is required to avoid GSCC errors at ARU creation.
6894: Concatination of string is required to avoid run time error */
6895: -- v_truncstring := 'Truncate Table oks';
6896: -- v_truncstring := v_truncstring||'.oks_process_billing';
6897: v_truncstring := 'Truncate Table OKS'||'.oks_process_billing';
6898: DBMS_SQL.PARSE(v_cursor,v_truncstring,DBMS_SQL.V7);
6899: DBMS_SQL.CLOSE_CURSOR(v_cursor);
6900:
6901:
6937: v_cursor := DBMS_SQL.OPEN_CURSOR;
6938: /* Following line is required to avoid GSCC errors at ARU creation.
6939: Concatination of string is required to avoid run time error */
6940: --v_truncstring := 'Truncate Table oks';
6941: --v_truncstring := v_truncstring||'.oks_process_billing';
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:
6938: /* Following line is required to avoid GSCC errors at ARU creation.
6939: Concatination of string is required to avoid run time error */
6940: --v_truncstring := 'Truncate Table oks';
6941: --v_truncstring := v_truncstring||'.oks_process_billing';
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');