[Home] [Help]
367: -- Submit the RXi Report
368: BEGIN
369: SELECT count(*)
370: INTO l_count
371: FROM FV_FACTS_TRX_TEMP;
372:
373: IF l_count >0 THEN
374:
375: --group the PO receiving lines, bug7253838
771: -- PROCEDURE JOURNAL_PROCESS
772: -- -------------------------------------------------------------------
773: -- Journal_Process procedure is called from the Main procedure.
774: -- Its primary purpose is to derive values to populate
775: -- 'FV_FACTS_TRX_TEMP' table from the rows derived from INVOICES,
776: -- PAYMENTS etc. It uses dynamimic SQL to dynamically set
777: -- the select statement for the cursor.
778: -- It uses the argument 'p_jrnl_type' to find whether the journal
779: -- type is Invoice or payment, etc. The valid journal type values
4412: -- --------------------------------------------------------------------
4413: -- PROCEDURE POPULATE_TABLE
4414: -- --------------------------------------------------------------------
4415: -- This procedure gets called from procedure Journal_Process.
4416: -- Its main purpose is to insert records in FV_FACTS_TRX_TEMP table.
4417: -- --------------------------------------------------------------------
4418: PROCEDURE populate_table
4419: ( p_treasury_symbol_id NUMBER ,
4420: p_set_of_books_id NUMBER ,
4470: l_module_name VARCHAR2(200);
4471: BEGIN
4472: l_module_name := g_module_name || 'populate_table';
4473: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4474: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' POPULATING FV_FACTS_TRX_TEMP TABLE ...');
4475: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TREASURY SYMBOL ID :'||P_TREASURY_SYMBOL_ID);
4476: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SET OF BOOKS ID :'||P_SET_OF_BOOKS_ID);
4477: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CCID :'||P_CODE_COMBINATION_ID);
4478: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FUND VALUE :'||P_FUND_VALUE);
4524: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SLA ENTITY ID :'||P_SLA_ENTITY_ID);
4525:
4526: END IF;
4527:
4528: INSERT INTO fv_facts_trx_temp
4529: (treasury_symbol_id ,
4530: set_of_books_id ,
4531: code_combination_id ,
4532: fund_value ,
4634: p_sla_hdr_creation_date,
4635: p_sla_entity_id );
4636:
4637: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4638: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' POPULATED FV_FACTS_TRX_TEMP TABLE ...');
4639: END IF;
4640: EXCEPTION
4641: WHEN OTHERS THEN
4642: g_error_code := SQLCODE ;
4995: END load_facts_attributes ;
4996: -- -------------------------------------------------------------------
4997: -- PROCEDURE PURGE_FACTS_TRANSACTIONS
4998: -- -------------------------------------------------------------------
4999: -- Purges all FACTS transactions from the FV_FACTS_TRX_TEMP table for
5000: -- the passed Treasaury Symbol.
5001: -- ------------------------------------------------------------------
5002: PROCEDURE purge_facts_transactionS
5003: IS
5003: IS
5004: l_module_name VARCHAR2(200);
5005: BEGIN
5006: l_module_name := g_module_name || 'purge_facts_transactionS';
5007: DELETE FROM fv_facts_trx_temp;
5008: COMMIT ;
5009: EXCEPTION
5010: -- Exception Processing
5011: WHEN NO_DATA_FOUND THEN
5501: min(gl_posted_date) gl_posted_date,
5502: min(sla_hdr_event_id) sla_hdr_event_id,
5503: min(sla_hdr_creation_date) sla_hdr_creation_date,
5504: min(sla_entity_id) sla_entity_id
5505: FROM fv_facts_trx_temp
5506: WHERE document_source = 'Purchasing'
5507: AND document_category = 'Receiving'
5508: GROUP BY document_number;
5509:
5513: --Added for bug 7253838
5514: --For PO receipts merge events having the
5515: --same receipt number
5516: FOR pur_recpt IN merge_po_recpt LOOP
5517: UPDATE fv_facts_trx_temp
5518: SET transaction_date = pur_recpt.transaction_date,
5519: creation_date_time = pur_recpt.creation_date_time,
5520: journal_creation_date = pur_recpt.journal_creation_date,
5521: journal_modified_date = pur_recpt.journal_modified_date,
5529: AND document_number = pur_recpt.document_number;
5530: END LOOP;
5531:
5532: /* --Solution modified as above
5533: INSERT INTO fv_facts_trx_temp (
5534: treasury_symbol_id ,
5535: set_of_books_id ,
5536: code_combination_id ,
5537: fund_value ,
5632: min(sla_hdr_event_id),
5633: min(sla_hdr_creation_date),
5634: min(sla_entity_id),
5635: -9999
5636: FROM fv_facts_trx_temp
5637: WHERE document_source = 'Purchasing'
5638: AND document_category = 'Receiving'
5639: GROUP BY treasury_symbol_id ,
5640: set_of_books_id ,
5676: period_name ,
5677: reversal_flag ,
5678: -9999 ;
5679:
5680: DELETE FROM fv_facts_trx_temp
5681: WHERE document_source = 'Purchasing'
5682: AND document_category = 'Receiving'
5683: AND nvl(period_activity,-3333) <> -9999;
5684:
5681: WHERE document_source = 'Purchasing'
5682: AND document_category = 'Receiving'
5683: AND nvl(period_activity,-3333) <> -9999;
5684:
5685: UPDATE fv_facts_trx_temp
5686: SET period_activity = null
5687: WHERE period_activity = -9999;
5688: */
5689: --To create separate records for debit and credit amounts
5699: l_var2 := ' credit ';
5700: END IF;
5701:
5702: l_select :=
5703: ' insert into fv_facts_trx_temp (
5704: treasury_symbol_id ,
5705: set_of_books_id ,
5706: code_combination_id ,
5707: fund_value ,
5804: sla_hdr_event_id,
5805: sla_hdr_creation_date,
5806: sla_entity_id,
5807: -1111
5808: FROM fv_facts_trx_temp
5809: WHERE '||l_var2 || ' <> 0
5810: and --(document_source <> ''Receivables'' and
5811: document_category <> ''Trade Receipts''
5812: GROUP BY treasury_symbol_id ,
5865: END LOOP;
5866:
5867: -- update with -1111 so that the rows
5868: -- are retained for reporting
5869: UPDATE fv_facts_trx_temp
5870: SET period_activity = -1111
5871: WHERE --document_source = 'Receivables'
5872: --AND
5873: document_category = 'Trade Receipts';
5873: document_category = 'Trade Receipts';
5874:
5875: -- Delete the grouped rows and retain
5876: -- rows required for the report
5877: DELETE FROM fv_facts_trx_temp
5878: WHERE NVL(period_activity, -0000) <> -1111;
5879: EXCEPTION
5880: WHEN OTHERS THEN
5881: g_error_buf := SQLERRM;
5979: BEGIN
5980: --Group payables lines so that any duplicate lines
5981: --with different credit and debit amounts are
5982: --reported on one line with net amount
5983: INSERT INTO fv_facts_trx_temp (
5984: treasury_symbol_id ,
5985: set_of_books_id ,
5986: code_combination_id ,
5987: fund_value ,
6137: reversal_flag ,
6138: (sla_hdr_event_id),
6139: (sla_hdr_creation_date),
6140: (sla_entity_id)
6141: FROM fv_facts_trx_temp
6142: WHERE document_source = 'Payables'
6143: AND document_category = 'Purchase Invoices'
6144: GROUP BY treasury_symbol_id ,
6145: set_of_books_id ,
6191: sla_hdr_creation_date,
6192: sla_entity_id
6193: );
6194:
6195: DELETE from fv_facts_trx_temp
6196: WHERE document_source = 'Payables'
6197: AND document_category = 'Purchase Invoices'
6198: and period_activity <> -8888;
6199: