[Home] [Help]
1012:
1013: ---------------------------------------------------------------------
1014: -- procedure ins_job_run_dtl
1015: --
1016: -- Procedure to insert into the oki_job_run_dtl the list of
1017: -- contracts to be deleted from and inserted into the oki base
1018: -- tables.
1019: ---------------------------------------------------------------------
1020:
1037: BEGIN
1038: l_retcode := '0' ;
1039: l_sysdate := sysdate;
1040:
1041: l_loc := 'Inserting into oki_job_run_dtl.' ;
1042: -- Insert into the job_run_dtl table
1043: -- record to be deleted and records to be inserted
1044: INSERT INTO oki_job_run_dtl (
1045: job_run_id
1040:
1041: l_loc := 'Inserting into oki_job_run_dtl.' ;
1042: -- Insert into the job_run_dtl table
1043: -- record to be deleted and records to be inserted
1044: INSERT INTO oki_job_run_dtl (
1045: job_run_id
1046: , chr_id
1047: , action_flag
1048: , sob_id
1168: COMMIT ;
1169:
1170:
1171: -- analyze table
1172: fnd_stats.gather_table_stats(ownname=>'OKI' ,tabname=>'OKI_JOB_RUN_DTL',percent=> 10);
1173:
1174: EXCEPTION
1175: WHEN OTHERS THEN
1176: l_sqlcode := sqlcode ;
1200:
1201: /****************11510 changes for currency conversion new Procedure***********/
1202: ---------------------------------------------------------------------
1203: -- procedure initial_load_job_run_dtl
1204: -- Insert into the oki_job_run_dtl the list of
1205: -- contracts to be inserted into OKI_SALES_K_HDRS table during inital load.
1206: -- This table stores both the functional as well as base conversion rates for
1207: -- all the contracts inserted
1208: ---------------------------------------------------------------------
1397: END LOOP; -- FOR loop on tab(i)
1398:
1399:
1400: FORALL j in chr_id_tab.first..chr_id_tab.last
1401: INSERT INTO oki_job_run_dtl (
1402: job_run_id
1403: ,chr_id
1404: ,action_flag
1405: ,sob_id
1501: -- procedure job_start
1502: --
1503: -- Starts the refresh process
1504: -- 1. Creates an oki_job_runs record
1505: -- 2. Loads the oki_job_run_dtl table with the records to delete
1506: -- and insert into the oki base tables.
1507: ---------------------------------------------------------------------
1508: PROCEDURE job_start
1509: ( p_job_start_date IN DATE
1540: l_sysdate := sysdate;
1541: l_table_owner :='OKI';
1542:
1543: /* No need to truncate this table for incremental load
1544: l_table_name := 'OKI_JOB_RUN_DTL' ;
1545: l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
1546: truncate_table( p_table_owner => l_table_owner
1547: , p_table_name => l_table_name
1548: , x_errbuf => x_errbuf
1699: -- procedure initial_load_job_start
1700: --
1701: -- Starts the refresh process
1702: -- 1. Creates an oki_job_runs record
1703: -- 2. Loads the oki_job_run_dtl table with the records
1704: -- to insert into the oki base tables.
1705: ---------------------------------------------------------------------
1706:
1707: PROCEDURE initial_load_job_start
1733: BEGIN
1734: l_retcode := '0' ;
1735: l_sysdate := sysdate ;
1736: l_job_start_date := fnd_conc_date.string_to_date('1900/01/01');
1737: l_table_name :='OKI_JOB_RUN_DTL' ;
1738: l_table_owner := 'OKI';
1739:
1740: fnd_file.put_line( which => fnd_file.log
1741: , buff => 'Service Contracts Intelligence - Initial load Started on: ' ||
1755: -- Truncate failed, exit immediately.
1756: RAISE l_excp_exit_immediate ;
1757: END IF ;
1758:
1759: l_table_name := 'OKI_JOB_RUN_DTL' ;
1760: l_loc := 'Calling truncate_table with ' || l_table_owner || '.' || l_table_name ;
1761: truncate_table( p_table_owner => l_table_owner
1762: , p_table_name => l_table_name
1763: , x_errbuf => x_errbuf
2142:
2143: l_loc := p_object_name || ' has already been refreshed.' ;
2144: -- Refresh for this table has already been completed successfully
2145: -- since the job_run_id from the oki_job_run table equals the
2146: -- job_run_id from the oki_job_run_dtl table.
2147: IF l_job_run_id = l_rfh_job_run_id THEN
2148: raise l_excp_no_processing ;
2149: END IF ;
2150:
2644: /* 11510 Changes End */
2645: FROM
2646: okc_k_headers_b khr
2647: , oks_k_headers_b hoks -- 11510 Changes
2648: , oki_job_run_dtl jrd -- 11510 Changes
2649: , okc_k_party_roles_b cpl
2650: , okc_statuses_b sts
2651: , gl_periods spd
2652: , gl_periods spd1 -- 11510 Changes
2900: END IF ;
2901:
2902: l_loc := 'Deleting from ' || l_table_name ;
2903: DELETE FROM oki_sales_k_hdrs shd
2904: WHERE chr_id in ( SELECT /*+ index_ffs(jrd oki_job_run_dtl_u1)*/
2905: jrd.chr_id
2906: FROM oki_job_run_dtl jrd
2907: WHERE jrd.job_run_id = l_job_run_id ) ;
2908:
2902: l_loc := 'Deleting from ' || l_table_name ;
2903: DELETE FROM oki_sales_k_hdrs shd
2904: WHERE chr_id in ( SELECT /*+ index_ffs(jrd oki_job_run_dtl_u1)*/
2905: jrd.chr_id
2906: FROM oki_job_run_dtl jrd
2907: WHERE jrd.job_run_id = l_job_run_id ) ;
2908:
2909:
2910: l_loc := 'Populating Table oki_sales_k_hdrs ' ;
3100: , terminated.terminated_amount * jrd.trx_func_rate
3101: , terminated.terminated_amount * jrd.trx_base_rate
3102: /* 11510 Changes End */
3103: FROM
3104: oki_job_run_dtl jrd
3105: , okc_k_headers_b khr
3106: , oks_k_headers_b hoks -- 11510 Changes
3107: , okc_k_party_roles_b cpl
3108: , okc_statuses_b sts
3112: , okx_order_headers_v oh
3113: , okc_governances og -- From oki_pricing_rule
3114: , ( SELECT /*+ leading(jrd) full(jrd) cardinality(jrd,1)*/
3115: ol.subject_chr_id, decode(count(1),0,'Y','N') is_new_yn
3116: FROM okc_operation_lines ol, oki_job_run_dtl jrd
3117: WHERE 1 = 1
3118: AND jrd.job_run_id = l_job_run_id
3119: AND jrd.action_flag = 'I'
3120: AND ol.subject_chr_id = jrd.chr_id
3124: /* 11510 Changes Start */
3125: ,(SELECT okscle.dnz_chr_id /*+ leading(jrd) full(jrd) cardinality(jrd,1)*/
3126: , SUM (NVL(ubt_amount,0) + NVL(credit_amount,0) +
3127: NVL(suppressed_credit,0)) terminated_amount
3128: FROM oks_k_lines_b okscle,okc_k_lines_b okccle,oki_job_run_dtl jrd
3129: WHERE okccle.id = okscle.cle_id
3130: AND okscle.dnz_chr_id = jrd.chr_id
3131: AND jrd.job_run_id = l_job_run_id
3132: AND jrd.action_flag = 'I'
3187: and cpd.adjustment_period_flag = 'N'
3188: and sob.set_of_books_id = shd.sob_id)
3189: WHERE shd.chr_id IN (
3190: SELECT chr_id
3191: FROM oki_job_run_dtl jrd
3192: WHERE 1 = 1
3193: AND jrd.job_run_id = l_job_run_id
3194: AND jrd.action_flag = 'I'
3195: )
3213: AND cle.chr_id = shd.chr_id )
3214: WHERE shd.date_terminated IS NOT NULL
3215: AND shd.chr_id IN (
3216: SELECT chr_id
3217: FROM oki_job_run_dtl jrd
3218: WHERE 1 = 1
3219: AND jrd.job_run_id = l_job_run_id
3220: AND jrd.action_flag = 'I'
3221: );
3523: okc_k_lines_b cle
3524: ,oki_sales_k_hdrs shd
3525: ,okc_k_items cim
3526: ,okc_statuses_b sts
3527: ,oki_job_run_dtl jrd -- 11510 Changes
3528: WHERE 1=1
3529: and cim.cle_id = cle.id
3530: and sts.code = cle.sts_code
3531: and cle.chr_id is not null
3689:
3690: l_loc := 'Deleting from ' || l_table_name ;
3691: DELETE FROM oki_sold_itm_lines sil
3692: WHERE chr_id in ( SELECT jrd.chr_id
3693: FROM oki_job_run_dtl jrd
3694: WHERE jrd.job_run_id = l_job_run_id ) ;
3695:
3696: l_loc := 'Inserting into ' || l_table_name ;
3697: -- insert data
3815: okc_k_lines_b cle
3816: , oki_sales_k_hdrs shd
3817: , okc_k_items cim
3818: , okc_statuses_b sts
3819: , oki_job_run_dtl jrd
3820: WHERE 1=1
3821: AND cim.cle_id = cle.id
3822: and sts.code = cle.sts_code
3823: AND cle.chr_id IS NOT NULL
4148: --Bug Fix 3469671 code changes-------------------------------------------------
4149: from
4150: oki_sold_itm_lines sil
4151: /*11510 changes added*/
4152: ,oki_job_run_dtl jrd
4153: ,okc_k_lines_b cle
4154: ,okc_k_items cim
4155: ,csi_item_instances cii
4156: ,okc_statuses_b sts
4327: END IF ;
4328:
4329: l_loc := 'Deleting from ' || l_table_name ;
4330: DELETE FROM oki_cov_prd_lines cpl
4331: WHERE chr_id in ( SELECT /*+ index_ffs(jrd oki_job_run_dtl_u1)*/
4332: jrd.chr_id
4333: FROM oki_job_run_dtl jrd
4334: WHERE jrd.job_run_id = l_job_run_id ) ;
4335:
4329: l_loc := 'Deleting from ' || l_table_name ;
4330: DELETE FROM oki_cov_prd_lines cpl
4331: WHERE chr_id in ( SELECT /*+ index_ffs(jrd oki_job_run_dtl_u1)*/
4332: jrd.chr_id
4333: FROM oki_job_run_dtl jrd
4334: WHERE jrd.job_run_id = l_job_run_id ) ;
4335:
4336: l_loc := 'Inserting into ' || l_table_name ;
4337: -- insert data
4539: , oki_sold_itm_lines sil
4540: , okc_k_items cim
4541: , csi_item_instances cii
4542: , okc_statuses_b sts
4543: , oki_job_run_dtl jrd
4544: /*11510 changes added*/
4545: , gl_periods epd
4546: ,(select distinct object_cle_id cle_id from okc_operation_lines
4547: where active_yn = 'Y' ) exp --Bug Fix 3469671 code changes
4961:
4962: l_loc := 'Deleting from ' || l_table_name ;
4963: DELETE FROM oki_k_salesreps ksr
4964: WHERE contract_id in ( SELECT jrd.chr_id
4965: FROM oki_job_run_dtl jrd
4966: WHERE jrd.job_run_id = l_job_run_id ) ;
4967:
4968: l_loc := 'Inserting into ' || l_table_name ;
4969: -- insert data
5019: , ctt.attribute15
5020: FROM
5021: oki_sales_k_hdrs shd
5022: , okc_contacts ctt
5023: , oki_job_run_dtl jrd
5024: WHERE 1=1
5025: AND ctt.cro_code = l_salesperson_code -- 11510 Changes
5026: AND ctt.dnz_chr_id = shd.chr_id
5027: and jrd.chr_id = shd.chr_id
5309: FROM oki_cov_prd_lines cpl
5310: where cpl.parent_cle_id = sil.cle_id
5311: )
5312: where sil.chr_id in (select shd.chr_id
5313: from oki_job_run_dtl jrd, oki_sales_k_hdrs shd
5314: where 1 =1
5315: and jrd.action_flag = 'I'
5316: and jrd.job_run_id = l_job_run_id
5317: and jrd.chr_id = shd.chr_id