[Home] [Help]
1: PACKAGE BODY FII_AR_REVENUE_B_C AS
2: /* $Header: FIIAR18B.pls 120.96 2007/03/21 19:45:38 juding ship $ */
3:
4: g_errbuf VARCHAR2(2000) := NULL;
5: g_retcode VARCHAR2(20) := NULL;
39: PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
40: G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
41:
42: g_usage_code CONSTANT VARCHAR2(10) := 'DBI';
43: g_table_name VARCHAR2(50) := 'FII_AR_REVENUE_B';
44:
45: g_program_code_R CONSTANT VARCHAR2(30) := 'RECEIVABLES REVENUE';
46: g_program_code_DR CONSTANT VARCHAR2(30) := 'RECEIVABLES DEF REVENUE';
47:
282: g_section := 'Section 20';
283:
284: -- --------------------------------------------------------
285: -- Find the schema owner and tablespace
286: -- FII_AR_REVENUE_B is using
287: -- --------------------------------------------------------
288: g_section := 'Section 30';
289: IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_fii_schema))
290: THEN NULL;
338: -----------------------------------------------------------
339: -- PROCEDURE UNIQUE_CONST_RECORDS
340: -- this procedure creates temp table fii_ar_uni_con_rec to stores
341: -- the records which violate the
342: -- unique constraint condition on FII_AR_REVENUE_B.
343: -- The records in the table fii_ar_uni_con_rec may be investigated
344: -- to identify the reason for unique const violation.
345: -- The temp table fii_ar_uni_con_rec is dropped at the begining
346: -- of the subsequent Load / Update programs.
364: HAVING count(*)>1) ';
365:
366: fii_util.put_line('');
367: fii_util.put_line('There are duplicate records in the staging table.
368: This needs to be fixed before inserting records in FII_AR_REVENUE_B.
369: Created temp table fii_ar_uni_con_rec which stores
370: records that violate unique constraint on FII_AR_REVENUE_B. Please investigate
371: this table for duplicate records.');
372: fii_util.put_line('');
366: fii_util.put_line('');
367: fii_util.put_line('There are duplicate records in the staging table.
368: This needs to be fixed before inserting records in FII_AR_REVENUE_B.
369: Created temp table fii_ar_uni_con_rec which stores
370: records that violate unique constraint on FII_AR_REVENUE_B. Please investigate
371: this table for duplicate records.');
372: fii_util.put_line('');
373:
374: if g_debug_flag = 'Y' then
1278: seq_id NUMBER :=0;
1279: l_row_count NUMBER;
1280: BEGIN
1281:
1282: SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
1283:
1284: if g_debug_flag = 'Y' then
1285: fii_util.put_line(' ');
1286: fii_util.put_line('Merging data into base summary table');
1287: fii_util.start_timer;
1288: fii_util.put_line('');
1289: end if;
1290:
1291: MERGE INTO FII_AR_REVENUE_B f
1292: USING (SELECT /*+ cardinality(stg,1) */ * FROM FII_AR_REVENUE_STG stg
1293: WHERE prim_conversion_rate > 0
1294: OR sec_conversion_rate > 0) stg
1295: ON ( stg.revenue_pk = f.revenue_pk)
1402: commit;
1403:
1404: RETURN(l_row_count);
1405:
1406: /* DELETE FROM FII_AR_REVENUE_B f
1407: WHERE f.UPDATE_SEQUENCE <> seq_id
1408: AND f.TRANSACTION_CLASS <> 'ADJ'
1409: AND f.INVOICE_LINE_ID IN (SELECT fpk.primary_key1 FROM FII_AR_REVENUE_ID fpk
1410: WHERE fpk.view_type_id = 4);
2218: -- Bug 4942753: Change to return 1 if any row exists
2219: BEGIN
2220: SELECT 1
2221: INTO l_count1
2222: FROM fii_ar_revenue_b
2223: WHERE ROWNUM = 1;
2224: EXCEPTION
2225: WHEN NO_DATA_FOUND THEN
2226: l_count1 := 0;
2292: -- Bug 4942753: Change to return 1 if any row exists
2293: BEGIN
2294: SELECT 1
2295: INTO l_count1
2296: FROM fii_ar_revenue_b
2297: WHERE ROWNUM = 1;
2298: EXCEPTION
2299: WHEN NO_DATA_FOUND THEN
2300: l_count1 := 0;
2787: BEGIN
2788: -- DEBUG
2789: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
2790: 'FII',
2791: 'FII_AR_REVENUE_B_C_SUBWORKER',
2792: NULL,
2793: NULL,
2794: FALSE, -- sub request,may need to set true
2795: p_worker_no);
2846: TABLESPACE '||g_tablespace||'
2847: NOLOGGING storage (initial 4K next 16K MAXEXTENTS UNLIMITED) as
2848: select distinct
2849: invoice_id
2850: from FII_AR_REVENUE_B
2851: where transaction_class <> ''ADJ'' ';
2852:
2853: if g_debug_flag = 'Y' then
2854: fii_util.put_line('');
2895:
2896:
2897: g_section := 'Section 40';
2898: l_stmt:='
2899: delete from FII_AR_REVENUE_B
2900: where transaction_class <> ''ADJ''
2901: and invoice_id in (select invoice_id
2902: FROM '||g_fii_schema||'.fii_ar_revenue_sum_del2) ';
2903:
2965: -- fii_util.initialize will get profile options FII_DEBUG_MODE
2966: -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
2967: -- the log files and output files are written to
2968: ----------------------------------------------------------------
2969: fii_util.initialize(p_object_name||'.log',p_object_name||'.out',l_dir, 'FII_AR_REVENUE_B_C_SUBWORKER');
2970:
2971: g_fii_user_id := FND_GLOBAL.User_Id;
2972: g_fii_login_id := FND_GLOBAL.Login_Id;
2973:
3508: fii_util.start_timer;
3509: fii_util.put_line('');
3510: end if;
3511:
3512: SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
3513:
3514: INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_B F (
3515: REVENUE_PK,
3516: GL_DATE_ID,
3510: end if;
3511:
3512: SELECT FII_AR_REVENUE_B_S.nextval INTO seq_id FROM dual;
3513:
3514: INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AR_REVENUE_B F (
3515: REVENUE_PK,
3516: GL_DATE_ID,
3517: GL_DATE,
3518: INVENTORY_ITEM_ID,
3820: -- the log directory
3821: ------------------------------------------------
3822:
3823: IF g_program_type = 'I' THEN
3824: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_I');
3825: ELSIF g_program_type = 'L' THEN
3826: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_L');
3827: END IF;
3828:
3822:
3823: IF g_program_type = 'I' THEN
3824: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_I');
3825: ELSIF g_program_type = 'L' THEN
3826: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_L');
3827: END IF;
3828:
3829: IF g_program_type = 'I' THEN
3830: IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_REVENUE_B_I')) THEN
3826: fii_util.initialize('FII_AR_REV_SUM.log','FII_AR_REV_SUM.out',l_dir, 'FII_AR_REVENUE_B_L');
3827: END IF;
3828:
3829: IF g_program_type = 'I' THEN
3830: IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_REVENUE_B_I')) THEN
3831: raise_application_error(-20000,errbuf);
3832: return;
3833: END IF;
3834: ELSIF g_program_type = 'L' THEN
3831: raise_application_error(-20000,errbuf);
3832: return;
3833: END IF;
3834: ELSIF g_program_type = 'L' THEN
3835: IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_AR_REVENUE_B_L')) THEN
3836: raise_application_error(-20000,errbuf);
3837: return;
3838: END IF;
3839: END IF;
3854:
3855: ----------------------------------------------------
3856: -- drop table fii_ar_uni_con_rec. This table stores
3857: -- temporarily records that violated unique constraint
3858: -- condition when inserting records into FII_AR_REVENUE_B
3859: -- during previous Load /Update program. If the fii_ar_uni_con_rec doesnot
3860: -- exist, then nothing is done. The fii_ar_uni_con_rec will
3861: -- be created if there is any unique constraint violation
3862: -- during Load /Update program
3903: FII_UTIL.put_line('Running Initial Load, truncate staging and base summary tables.');
3904: END IF;
3905: -- DEBUG
3906: TRUNCATE_TABLE('FII_AR_REVENUE_STG');
3907: TRUNCATE_TABLE('FII_AR_REVENUE_B');
3908: TRUNCATE_TABLE('FII_AR_REV_ACCTS');
3909: BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_I');
3910: BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_L');
3911: COMMIT;
3905: -- DEBUG
3906: TRUNCATE_TABLE('FII_AR_REVENUE_STG');
3907: TRUNCATE_TABLE('FII_AR_REVENUE_B');
3908: TRUNCATE_TABLE('FII_AR_REV_ACCTS');
3909: BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_I');
3910: BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_L');
3911: COMMIT;
3912: END IF;
3913:
3906: TRUNCATE_TABLE('FII_AR_REVENUE_STG');
3907: TRUNCATE_TABLE('FII_AR_REVENUE_B');
3908: TRUNCATE_TABLE('FII_AR_REV_ACCTS');
3909: BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_I');
3910: BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_REVENUE_B_L');
3911: COMMIT;
3912: END IF;
3913:
3914: -------------------------------------------------------------
3945: -----------------------------------------------------------------
3946: -- When running in Incremental mode, the values of the parameters
3947: -- are derived
3948: -----------------------------------------------------------------
3949: BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AR_REVENUE_B_I',
3950: l_last_start_date1,
3951: l_last_end_date,
3952: l_last_period_from,
3953: l_last_period_to1);
4000: -- The LOAD may be run on 7-Mar-2003 but the the records may be collected
4001: -- upto 31-DEC-2002. To get the records from 31-DEC-2002 we use the least
4002: -- of l_last_start_date2, l_last_period_to2
4003: -- --------------------------------------------------------------------------
4004: BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_AR_REVENUE_B_L',
4005: l_last_start_date2,
4006: l_last_end_date,
4007: l_last_period_from,
4008: l_last_period_to2);
4826: l_section := 'W-Section 10';
4827: -- -----------------------------------------------
4828: -- Set up directory structure for child process
4829: -- -----------------------------------------------
4830: CHILD_SETUP('FII_AR_REVENUE_B_C_SUBWORKER'||p_worker_no);
4831:
4832: if g_debug_flag = 'Y' then
4833: fii_util.put_line(' ');
4834: fii_util.put_timestamp;
5093:
5094:
5095: END WORKER;
5096:
5097: END FII_AR_REVENUE_B_C;