[Home] [Help]
752:
753: BEGIN
754: SELECT count(*)
755: INTO l_count
756: FROM FV_GTAS_TRX_REG_TEMP;
757:
758: log(l_module, 'l_count '||l_count);
759:
760: IF l_count >0 THEN
1463: -- PROCEDURE JOURNAL_PROCESS
1464: -- -------------------------------------------------------------------
1465: -- Journal_Process procedure is called from the Main procedure.
1466: -- Its primary purpose is to derive values to populate
1467: -- 'FV_GTAS_TRX_REG_TEMP' table from the rows derived from INVOICES,
1468: -- PAYMENTS etc. It uses dynamimic SQL to dynamically set
1469: -- the select statement for the cursor.
1470: -- It uses the argument 'p_jrnl_type' to find whether the journal
1471: -- type is Invoice or payment, etc. The valid journal type values
4511: -- --------------------------------------------------------------------
4512: -- PROCEDURE POPULATE_TABLE
4513: -- --------------------------------------------------------------------
4514: -- This procedure gets called from procedure Journal_Process.
4515: -- Its main purpose is to insert records in FV_GTAS_TRX_REG_TEMP table.
4516: -- --------------------------------------------------------------------
4517: PROCEDURE POPULATE_TABLE
4518: ( p_account_number VARCHAR2,
4519: p_treasury_symbol_id NUMBER ,
4574:
4575: log(l_module, ' p_set_of_books_id '||p_set_of_books_id);
4576: log(l_module,'p_authority_type_val: '||p_authority_type_val);
4577:
4578: INSERT INTO fv_gtas_trx_reg_temp
4579: (ACCOUNT_NUMBER ,
4580: TREASURY_SYMBOL_ID ,
4581: SET_OF_BOOKS_ID ,
4582: CODE_COMBINATION_ID,
4682: p_budget_impact_ind,
4683: p_ussgl_account
4684: );
4685:
4686: log(l_module, ' Populated fv_gtas_trx_reg_temp table ');
4687:
4688: EXCEPTION
4689: WHEN OTHERS THEN
4690: g_error_code := SQLCODE ;
4694: END populate_table;
4695: -- -------------------------------------------------------------------
4696: -- PROCEDURE PURGE_GTAS_TRANSACTIONS
4697: -- -------------------------------------------------------------------
4698: -- Purges all GTAS transactions from the FV_GTAS_TRX_REG_TEMP table for
4699: -- the passed Treasaury Symbol.
4700: -- ------------------------------------------------------------------
4701: PROCEDURE PURGE_GTAS_TRANSACTIONS
4702: IS
4702: IS
4703: l_module VARCHAR2(200):= g_module || 'purge_gtas_transactions';
4704: BEGIN
4705: log(l_module, 'Begin');
4706: DELETE FROM fv_gtas_trx_reg_temp;
4707: log(l_module, 'End');
4708: EXCEPTION
4709: -- Exception Processing
4710: WHEN NO_DATA_FOUND THEN
4868: min(gl_posted_date) gl_posted_date,
4869: min(sla_hdr_event_id) sla_hdr_event_id,
4870: min(sla_hdr_creation_date) sla_hdr_creation_date,
4871: min(sla_entity_id) sla_entity_id
4872: FROM fv_gtas_trx_reg_temp
4873: WHERE document_source = 'Purchasing'
4874: AND document_category = 'Receiving'
4875: GROUP BY document_number;
4876:
4880: --Added for bug 7253838
4881: --For PO receipts merge events having the
4882: --same receipt number
4883: FOR pur_recpt IN merge_po_recpt LOOP
4884: UPDATE fv_gtas_trx_reg_temp
4885: SET transaction_date = pur_recpt.transaction_date,
4886: creation_date_time = pur_recpt.creation_date_time,
4887: journal_creation_date = pur_recpt.journal_creation_date,
4888: journal_modified_date = pur_recpt.journal_modified_date,
4909: l_var2 := ' credit ';
4910: END IF;
4911:
4912: l_select :=
4913: ' insert into fv_gtas_trx_reg_temp (
4914: treasury_symbol_id ,
4915: set_of_books_id ,
4916: code_combination_id ,
4917: fund_value ,
5014: sla_hdr_event_id,
5015: sla_hdr_creation_date,
5016: sla_entity_id,
5017: -1111
5018: FROM fv_gtas_trx_reg_temp
5019: WHERE '||l_var2 || ' <> 0
5020: and --(document_source <> ''Receivables'' and
5021: document_category <> ''Trade Receipts''
5022: GROUP BY treasury_symbol_id ,
5075: END LOOP;
5076:
5077: -- update with -1111 so that the rows
5078: -- are retained for reporting
5079: UPDATE fv_gtas_trx_reg_temp
5080: SET period_activity = -1111
5081: WHERE --document_source = 'Receivables'
5082: --AND
5083: document_category = 'Trade Receipts';
5083: document_category = 'Trade Receipts';
5084:
5085: -- Delete the grouped rows and retain
5086: -- rows required for the report
5087: DELETE FROM fv_gtas_trx_reg_temp
5088: WHERE NVL(period_activity, -0000) <> -1111;
5089:
5090: log(l_module, 'End');
5091: EXCEPTION
5194: BEGIN
5195: --Group payables lines so that any duplicate lines
5196: --with different credit and debit amounts are
5197: --reported on one line with net amount
5198: INSERT INTO fv_gtas_trx_reg_temp (
5199: treasury_symbol_id ,
5200: set_of_books_id ,
5201: code_combination_id ,
5202: fund_value ,
5352: reversal_flag ,
5353: (sla_hdr_event_id),
5354: (sla_hdr_creation_date),
5355: (sla_entity_id)
5356: FROM fv_gtas_trx_reg_temp
5357: WHERE document_source = 'Payables'
5358: AND document_category = 'Purchase Invoices'
5359: group by treasury_symbol_id, set_of_books_id,
5360: code_combination_id, fund_value, account_number,
5367: availability_flag, legislation_flag, PYA_CODE, (journal_creation_date),
5368: (journal_modified_date), period_name, (gl_date), (gl_posted_date), reversal_flag,
5369: (sla_hdr_event_id), (sla_hdr_creation_date), (sla_entity_id) );
5370:
5371: DELETE from fv_gtas_trx_reg_temp
5372: WHERE document_source = 'Payables'
5373: AND document_category = 'Purchase Invoices'
5374: and period_activity <> -8888;
5375: