/* Formatted on 2005/09/22 18:53 (Formatter Plus v4.8.0) */ CREATE OR REPLACE PROCEDURE briouser.proc_deferred_rev IS /* ----------------------------------------------------------------- Description: This procedure is used to gather deferred revenue info. There are a number of reports fed from this procedure - 1) Deferred Revenue 2) Tiered Maintenance Author: POS Creation Date: 29th Oct 2003 Change Log: ----------------------------------------------------------------- tester2 30July03 CMR1000 JMcC Sample log of change 16Sept03 CMR2001 LAR(JMcC) Include GL Manuals 8Oct03 CMR2290 JMcC Take out orig_defer_amt for gl manuals 5th Nov CMR3017 JMcC Added delete for gl's with no so 3rd Dec 03 CMR3458 JMcC Added Warranty UPG to update Stmt 11th Dec 03 CMR3666 JMcC Added Prepaid Maint to delete condition 20Jan04 CMR4208 POS Added 245102, 245121 to GL 29Jan04 CMR4435 POS Added Ship-to for GL entries, changed product source and allowed redirects to stay extra insert for GL for manual receivables 30Jan04 CMR4453 POS break out inserts for deferrollup into separate AR and GL. Add decode for Spot and EMU Fixed Exchange Rates 24Feb04 CMR4951 JMcC Fix to stop code deleting accounts 245112 and 245114 25Feb04 CMR5063 JMcC Update the orig_defer_amt for account 245112 01Mar04 CMR5167 MB Update the orig_defer_amount for account 245112 and 245114 for Manuals 10Mar04 CMR5434 MB Update the Org_Id if null 05Mar04 CMR5272 POS change invoice date on gl inserts 07Apr04 CMR5898 POS add nvl for order number field 28Apr04 CMR6430 MB Fix link for ST update and add ST amount to Month1 for GL entries 18May04 CMR7098 POS Add account 245131 for non PAS Conversions 30Nov04 SIRMG00012856 JMcC Added condition to bring in Warranty manuals and Change date to original invoice date 06Dec04 SIRMG00015522 JMcC Commented out condition limiting running of code to only BP02 07dEC04 SIRMG00012345 POS ADDED INSTALLAT_LOCATION 08Dec04 SIRMG00015640 MB Change Gl_currency_api to use Exchange_Date 21Feb05 SIRMG00015882 CD Add "Prepaid Maintenance" to the NRT Deferred Revenue 21Feb05 SIRMG00016682 CD Add Unearned Account 245117 to the NRT Deferred Revenue 23Feb05 SIRMG00008780 CD In "AR with OE links" query, clean up fields based on Winpeak Transactions Interface 15Mar05 SIRMG00006679 CD Added set of books currency rate to deferollup to be used as computed item in report 15Mar05 SIRMG00017557 CD Bring user_sort_char_7 field through to Deferrollup table 23Mar05 SIRMG00018734 CD In "Manual AR" query, clean up fields based on Winpeak Transactions Interface 05Apr05 SIRMG00019015 CD Take out Account 245115 on inserting into deferrollup table 19Apr05 SIRMG00019368 CD Changes for Cost-Based Optimizer 05May05 SIRMG00018736 JZ Add current qtr rollup for cs revenue project 16May05 SIRMG00020308 FT Add enduser 02Jun05 SIRMG00019364 CD Code Performance. Condense update statements. 21July05 SIRMG22259 JZ Add earned_date to deferrollup_snap for revenue report 01Sep05 SIRMG00021215 FT Add Channels Information to Sales PPM Report 01Sep05 SIRMG00012855 COH Add amount_due_original, amount_due_remaing, usd_value to defferrolup 08Sep05 SIRMG00023855 NC Add delete stmt for 2 trx #'s that are erroring out API ----------------------------------------------------------------- */ v_slno NUMBER; v_date DATE; v_month NUMBER; v_server VARCHAR2 (10); v_curr_qtr_end_date DATE; v_earned_date DATE; v_day1 NUMBER; --SIRMG00012855 v_day2 NUMBER; --SIRMG00012855 v_curr_qtr_day1 DATE; --SIRMG00012855 v_curr_qtr_day7 DATE; --SIRMG00012855 --SIR19364 v_month_day1 DATE; err_cde NUMBER; err_msg VARCHAR2 (100); CURSOR c_get_meageddebt IS SELECT DISTINCT trx_number, set_of_books_id FROM monthendcashcollections; CURSOR c_quarterdates IS SELECT end_date FROM briouser.quarterdates WHERE current_qtr = 'Y'; --SIRMG00012855 CURSOR get_month_end IS SELECT trx_number, SUM (amount_due_original) amount_due_original, SUM (amount_due_remaining) amount_due_remaining, SUM (usd_value) usd_value FROM briouser.monthendcashcollections a GROUP BY trx_number; BEGIN OPEN c_quarterdates; FETCH c_quarterdates INTO v_curr_qtr_end_date; IF c_quarterdates%NOTFOUND THEN v_curr_qtr_end_date := SYSDATE; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Error in Proc_Deferred_Rev - No Curr Qtr End Date found', 'Sysdate set as Curr Qtr End Date', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; END IF; CLOSE c_quarterdates; SELECT defermonth INTO v_date FROM briouser.defermonth; --SIR19364 minor alteration SELECT TO_CHAR (defermonth, 'YYYYMM') INTO v_month FROM defermonth; --SIR19364 SELECT ROUND (defermonth, 'MONTH') INTO v_month_day1 FROM briouser.defermonth; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Start', 'Deferral', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; EXECUTE IMMEDIATE 'TRUNCATE TABLE briouser.deferrep DROP STORAGE'; COMMIT; INSERT INTO deferrep SELECT DISTINCT MIN (ssc1.salesrep_id), header_id, 'OE' FROM oe.so_sales_credits ssc1 WHERE ssc1.sales_credit_type_id = 1 AND ssc1.line_id IS NULL AND PERCENT IN ( SELECT MAX (PERCENT) FROM oe.so_sales_credits ssc2 WHERE ssc1.header_id = ssc2.header_id AND ssc2.sales_credit_type_id = 1) GROUP BY header_id; COMMIT; EXECUTE IMMEDIATE 'TRUNCATE TABLE briouser.deferrepar DROP STORAGE'; COMMIT; INSERT INTO deferrepar SELECT DISTINCT MIN (ssc1.salesrep_id), customer_trx_id, 'AR' FROM ar.ra_cust_trx_line_salesreps_all ssc1 WHERE ssc1.revenue_amount_split IS NOT NULL GROUP BY customer_trx_id; COMMIT; EXECUTE IMMEDIATE 'TRUNCATE TABLE briouser.ar_payments_schedule_all DROP STORAGE'; COMMIT; INSERT INTO briouser.ar_payments_schedule_all SELECT customer_trx_id, status FROM ar.ar_payment_schedules_all WHERE terms_sequence_number = 1; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'APSA', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- table for oracle product rollup --SIR15882 added 'EMC PROFESSIONAL SERVICES' --SIR17557 Add user_sort_char_7, family, prod_type, recon_item EXECUTE IMMEDIATE 'TRUNCATE TABLE briouser.oracle_productrollup DROP STORAGE'; COMMIT; INSERT INTO briouser.oracle_productrollup (inventory_item_id, model, product_type, cat_type, capability, user_sort_char_7, family, prod_type, recon_item) SELECT DISTINCT msi.inventory_item_id, msi.segment1, mc.segment2, mc.segment3, mc.segment4, ebh.user_sort_char_7, ebh.family, ebh.product_type, ebh.recon_item FROM briouser.mtl_system_items msi, inv.mtl_categories mc, inv.mtl_item_categories mic, emcinv.emcinv_brm_hierarchy ebh WHERE msi.inventory_item_id = mic.inventory_item_id AND mic.category_id = mc.category_id AND msi.attribute2 = ebh.brm_item_id(+) AND msi.item_type IN ('EMC MODEL', 'EMC PROFESSIONAL SERVICES') --ADDED THIS FOR WINPEAK INVOICES; AND mic.category_set_id = 46; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'OPR', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; EXECUTE IMMEDIATE 'TRUNCATE TABLE BRIOUSER.DEFER_AR_FILE DROP STORAGE'; COMMIT; -- LAR Added section 9/11/03 ------------------------------------------------------ EXECUTE IMMEDIATE 'TRUNCATE TABLE BRIOUSER.PSGCC DROP STORAGE'; -- SIR15882 (added 245101, 245104); SIR16682 (added 245117) -- SIR19368 Remove 245101, 245104. We only want the Winpeak trx's from these a\c's, which we get from the Manual AR query INSERT INTO briouser.psgcc SELECT * FROM gl.gl_code_combinations gcc WHERE gcc.segment3 IN ('245112', '245114', '245116', '245117', '245102', '245121', '245119', '245131' ); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Deferred Account', 'Completed', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; EXECUTE IMMEDIATE 'TRUNCATE TABLE BRIOUSER.PSGJL DROP STORAGE'; INSERT INTO briouser.psgjl SELECT gjl.* FROM gl.gl_je_lines gjl, briouser.psgcc psgcc WHERE gjl.code_combination_id = psgcc.code_combination_id; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Deferred Lines', 'Gathered', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --SIR6679 Added sob_currency_code --SIR17557 Add user_sort_char_7 INSERT INTO briouser.defer_ar_file (account_description, bill_to_customer_name, bill_to_customer_no, book_rate, brm_item, DAY, extended_amount, family, currency_code, invoice_date, ar_line_no, line_id, MONTH, order_number, org_id, quantity, quarter, recon_item, revenue_amount, seq_no, ship_to_customer_name, ship_to_customer_no, trx_number, trx_type, trx_type_description, YEAR, customer_trx_line_id, customer_trx_id, order_category, order_type, organisation_id, sales_channel_code, header_id, box_type, product_type, conversion_type_code, ordered_date, approved_date, opp_no, deal_number, list_price, product, parent_line_id, source_header_id, attribute12, revenue_amount_functional, inventory_item_id, segment1, segment2, segment4, segment5, segment6, segment7, gl_post_date, gl_date, link_to_line_id, sob_id, sow_curr_code, exchange_date, salesrep_id, status, SOURCE, so_line_no, so_line_number, orig_defer_amt, shortterm_unearned_rev, account_class, earnout_start_date, sob_currency_code, user_sort_char_7) SELECT gcc.segment3 account_description, rc.customer_name bill_to_cust_name, rc.customer_number bill_to_cust_no, 0 book_rate, ebh.brm_item, TO_CHAR (gjl.effective_date, 'DD') DAY, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)) extended_amount, ebh.family, gjh.currency_code currency_code, gjl.effective_date invoice_date, gjl.je_line_num ar_line_no, sla.line_id, TO_CHAR (gjl.effective_date, 'MM') MONTH, soh.order_number, soh.org_id, gjl.stat_amount qty, 'Q' || TO_CHAR (gjl.effective_date, 'QYYYY') qtr, ebh.recon_item, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)) revenue_amount, billings_stage_seq.NEXTVAL seq_no, rc.customer_name ship_to_customer_name, rc.customer_number ship_to_cust_no, TO_CHAR (gjh.doc_sequence_value) trx_number, gjh.je_source trx_type, 'GL JOURNAL (' || SUBSTR (gjl.description, 1, 65) || ')' trx_type_description, TO_CHAR (gjl.effective_date, 'YYYY') YEAR, 0 customer_trx_line_id, 0 customer_trx_id, soh.order_category, so_order_types_all.NAME order_type, NVL (sla.warehouse_id, '64') organisation_id, soh.sales_channel_code, soh.header_id, ebh.box_type, ebh.product_type, gjh.currency_conversion_type conversion_type_code, soh.date_ordered, soh.s8_date approved_date, soh.attribute12 opp_no, soh.attribute1 deal_number, sla.list_price * NVL (sla.attribute12, 1) list_price, msi.segment1 product, sla.parent_line_id, soh.source_header_id, sla.attribute12, (NVL (gjl.accounted_cr, 0)) - (NVL (gjl.accounted_dr, 0)) revenue_amount_functional, gjl.attribute5 inventory_item_id, gcc.segment1, gcc.segment2, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gjh.posted_date, gjl.effective_date gl_date, sla.link_to_line_id, gjl.set_of_books_id, soh.currency_code, gjl.effective_date exchange_date, deferrep.salesrep_id, gjh.status status, 'GL' SOURCE, sla.line_number, sla.line_number, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)), (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)), 'UNEARN', gjl.effective_date, gsb.currency_code, ebh.user_sort_char_7 FROM briouser.psgjl gjl, briouser.psgcc gcc, gl.gl_je_headers gjh, gl.gl_sets_of_books gsb, oe.so_headers_all soh, oe.so_lines_all sla, briouser.mtl_system_items msi, emcinv.emcinv_brm_hierarchy ebh, ar.ra_site_uses_all rsua, ar.ra_addresses_all raa, ar.ra_customers rc, ar.ra_site_uses_all srsua, ar.ra_addresses_all sraa, ar.ra_customers src, oe.so_order_types_all so_order_types_all, briouser.deferrep deferrep WHERE (gjh.je_source != 'Receivables') AND gjh.je_header_id = gjl.je_header_id AND soh.header_id(+) = gjl.attribute1 AND sla.line_id(+) = gjl.attribute4 AND gjl.code_combination_id = gcc.code_combination_id AND gjl.attribute5 = msi.inventory_item_id(+) --cmr 4435 AND msi.attribute2 = ebh.brm_item_id(+) AND soh.invoice_to_site_use_id = rsua.site_use_id(+) AND rsua.address_id = raa.address_id(+) AND raa.customer_id = rc.customer_id(+) AND soh.ship_to_site_use_id = srsua.site_use_id(+) AND srsua.address_id = sraa.address_id(+) AND sraa.customer_id = src.customer_id(+) AND soh.header_id = deferrep.link_id(+) AND so_order_types_all.order_type_id(+) = soh.order_type_id AND gjl.set_of_books_id = gsb.set_of_books_id(+); SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Inserted', 'GL Deferred Rev Non Rec', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --SIR6679 Added sob_currency_code --SIR17557 Add user_sort_char_7 INSERT INTO briouser.defer_ar_file (account_description, bill_to_customer_name, bill_to_customer_no, book_rate, brm_item, DAY, extended_amount, family, currency_code, invoice_date, ar_line_no, line_id, MONTH, order_number, org_id, quantity, quarter, recon_item, revenue_amount, seq_no, ship_to_customer_name, ship_to_customer_no, trx_number, trx_type, trx_type_description, YEAR, customer_trx_line_id, customer_trx_id, order_category, order_type, organisation_id, sales_channel_code, header_id, box_type, product_type, conversion_type_code, ordered_date, approved_date, opp_no, deal_number, list_price, product, parent_line_id, source_header_id, attribute12, revenue_amount_functional, inventory_item_id, segment1, segment2, segment4, segment5, segment6, segment7, gl_post_date, gl_date, link_to_line_id, sob_id, sow_curr_code, exchange_date, salesrep_id, status, SOURCE, so_line_no, so_line_number, orig_defer_amt, shortterm_unearned_rev, account_class, earnout_start_date, sob_currency_code, user_sort_char_7) SELECT gcc.segment3 account_description, rc.customer_name bill_to_cust_name, rc.customer_number bill_to_cust_no, 0 book_rate, ebh.brm_item, TO_CHAR (rcta.trx_date, 'DD') DAY, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)) extended_amount, ebh.family, gjh.currency_code currency_code, rcta.trx_date invoice_date, gjl.je_line_num ar_line_no, gjl.attribute4 line_id, TO_CHAR (rcta.trx_date, 'MM') MONTH, NVL (soh.order_number, sales_order), soh.org_id, gjl.stat_amount qty, 'Q' || TO_CHAR (rcta.trx_date, 'QYYYY') qtr, ebh.recon_item, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)) revenue_amount, billings_stage_seq.NEXTVAL seq_no, rc.customer_name ship_to_customer_name, rc.customer_number ship_to_cust_no, reference_4 trx_number, gjh.je_source trx_type, 'GL JOURNAL (' || SUBSTR (gjl.description, 1, 65) || ')' trx_type_description, TO_CHAR (gjl.effective_date, 'YYYY') YEAR, rctla.customer_trx_line_id, rctla.customer_trx_id, soh.order_category, so_order_types_all.NAME order_type, NVL (sla.warehouse_id, '64') organisation_id, soh.sales_channel_code, soh.header_id header_id, ebh.box_type, ebh.product_type, gjh.currency_conversion_type conversion_type_code, soh.date_ordered, soh.s8_date approved_date, soh.attribute12 opp_no, soh.attribute1 deal_number, sla.list_price * NVL (sla.attribute12, 1) list_price, msi.segment1 product, sla.parent_line_id, soh.source_header_id, sla.attribute12, (NVL (gjl.accounted_cr, 0)) - (NVL (gjl.accounted_dr, 0)) revenue_amount_functional, gjl.attribute5 inventory_item_id, gcc.segment1, gcc.segment2, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gjh.posted_date, gjl.effective_date gl_date, sla.link_to_line_id, gjl.set_of_books_id, soh.currency_code, rcta.trx_date exchange_date, --GJL.EFFECTIVE_DATE EXCHANGE_DATE, SIR19364 deferrep.salesrep_id rep, gjh.status status, 'GL' SOURCE, sla.line_number, sla.line_number, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)), (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)), account_class, gjl.effective_date, gsb.currency_code, ebh.user_sort_char_7 FROM briouser.psgjl gjl, briouser.psgcc gcc, gl.gl_je_headers gjh, gl.gl_sets_of_books gsb, oe.so_headers_all soh, oe.so_lines_all sla, briouser.mtl_system_items msi, emcinv.emcinv_brm_hierarchy ebh, ar.ra_site_uses_all rsua, ar.ra_addresses_all raa, ar.ra_customers rc, ar.ra_site_uses_all srsua, ar.ra_addresses_all sraa, ar.ra_customers src, ar.ra_cust_trx_line_gl_dist_all rctlgda, ar.ra_customer_trx_lines_all rctla, ar.ra_customer_trx_all rcta, oe.so_order_types_all so_order_types_all, briouser.deferrep WHERE ( gjh.je_source = 'Receivables' AND gcc.segment3 IN ('245102', '245121', '245119', '245131') ) AND gjl.reference_3 = rctlgda.cust_trx_line_gl_dist_id AND rctlgda.customer_trx_line_id = rctla.customer_trx_line_id AND rctla.interface_line_attribute6 = sla.line_id AND rcta.customer_trx_id = rctla.customer_trx_id AND gcc.code_combination_id = gjl.code_combination_id --AND SLA.LINE_ID (+) = GJL.ATTRIBUTE4 AND gjh.je_header_id = gjl.je_header_id AND soh.header_id(+) = sla.header_id AND sla.inventory_item_id = msi.inventory_item_id(+) AND msi.attribute2 = ebh.brm_item_id(+) AND soh.invoice_to_site_use_id = rsua.site_use_id(+) AND rsua.address_id = raa.address_id(+) AND raa.customer_id = rc.customer_id(+) AND soh.ship_to_site_use_id = srsua.site_use_id(+) AND srsua.address_id = sraa.address_id(+) AND sraa.customer_id = src.customer_id(+) AND soh.header_id = deferrep.link_id(+) AND so_order_types_all.order_type_id(+) = soh.order_type_id AND gjl.set_of_books_id = gsb.set_of_books_id(+); SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Inserted', 'GL Deferred Rev Rec', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --cmr 4435 --SIR6679 Added sob_currency_code --SIR17557 Add user_sort_char_7 INSERT INTO briouser.defer_ar_file (account_description, bill_to_customer_name, bill_to_customer_no, book_rate, brm_item, DAY, extended_amount, family, currency_code, invoice_date, ar_line_no, line_id, MONTH, order_number, org_id, quantity, quarter, recon_item, revenue_amount, seq_no, ship_to_customer_name, ship_to_customer_no, trx_number, trx_type, trx_type_description, YEAR, customer_trx_line_id, customer_trx_id, order_category, order_type, organisation_id, sales_channel_code, header_id, box_type, product_type, conversion_type_code, ordered_date, approved_date, opp_no, deal_number, list_price, product, parent_line_id, source_header_id, attribute12, revenue_amount_functional, inventory_item_id, segment1, segment2, segment4, segment5, segment6, segment7, gl_post_date, gl_date, link_to_line_id, sob_id, sow_curr_code, exchange_date, salesrep_id, status, SOURCE, so_line_no, so_line_number, orig_defer_amt, shortterm_unearned_rev, account_class, earnout_start_date, sob_currency_code, user_sort_char_7) SELECT gcc.segment3 account_description, rc.customer_name bill_to_cust_name, rc.customer_number bill_to_cust_no, 0 book_rate, ebh.brm_item, TO_CHAR (rcta.trx_date, 'DD') DAY, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)) extended_amount, ebh.family, gjh.currency_code currency_code, rcta.trx_date invoice_date, gjl.je_line_num ar_line_no, gjl.attribute4 line_id, TO_CHAR (rcta.trx_date, 'MM') MONTH, NVL (rctla.sales_order, rcta.interface_header_attribute1), -- CMR 5898 rcta.org_id, gjl.stat_amount qty, 'Q' || TO_CHAR (rcta.trx_date, 'QYYYY') qtr, ebh.recon_item, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)) revenue_amount, billings_stage_seq.NEXTVAL seq_no, rc1.customer_name ship_to_customer_name, rc1.customer_number ship_to_cust_no, reference_4 trx_number, gjh.je_source trx_type, 'GL JOURNAL (' || SUBSTR (gjl.description, 1, 65) || ')' trx_type_description, TO_CHAR (gjl.effective_date, 'YYYY') YEAR, rctla.customer_trx_line_id, rctla.customer_trx_id, '', rcta.interface_header_attribute2 order_type, rcta.interface_header_attribute10 organisation_id, '', '' header_id, ebh.box_type, ebh.product_type, gjh.currency_conversion_type conversion_type_code, rcta.trx_date, '' approved_date, '' opp_no, '' deal_number, 0 list_price, msi.segment1 product, 0, '', 0, (NVL (gjl.accounted_cr, 0)) - (NVL (gjl.accounted_dr, 0)) revenue_amount_functional, gjl.attribute5 inventory_item_id, gcc.segment1, gcc.segment2, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gjh.posted_date, gjl.effective_date gl_date, 0, gjl.set_of_books_id, rcta.invoice_currency_code, rcta.trx_date exchange_date, --GJL.EFFECTIVE_DATE EXCHANGE_DATE,--SIR19364 deferrepar.salesrep_id rep, gjh.status status, 'GL' SOURCE, 0, 0, (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)), (NVL (gjl.entered_cr, 0)) - (NVL (gjl.entered_dr, 0)), account_class, gjl.effective_date, gsb.currency_code, ebh.user_sort_char_7 FROM briouser.psgjl gjl, briouser.psgcc gcc, gl.gl_je_headers gjh, gl.gl_sets_of_books gsb, --OE.SO_HEADERS_ALL SOH, briouser.mtl_system_items msi, emcinv.emcinv_brm_hierarchy ebh, ar.ra_customers rc, ar.ra_customers rc1, ar.ra_cust_trx_line_gl_dist_all rctlgda, ar.ra_customer_trx_lines_all rctla, ar.ra_customer_trx_all rcta, briouser.deferrepar WHERE ( gjh.je_source = 'Receivables' AND gcc.segment3 IN ('245102', '245121', '245119', '245131') ) AND gjl.reference_3 = rctlgda.cust_trx_line_gl_dist_id AND rctlgda.customer_trx_line_id = rctla.customer_trx_line_id AND rctla.interface_line_attribute6 IS NULL AND rcta.customer_trx_id = rctla.customer_trx_id --and rctla.sales_order =soh.order_number(+) --and nvl(rctla.sales_order, '60') like '6%' --and length(nvl(rctla.sales_order,'EIGHTYEI')) =8 AND gcc.code_combination_id = gjl.code_combination_id --AND SLA.LINE_ID (+) = GJL.ATTRIBUTE4 AND gjh.je_header_id = gjl.je_header_id AND rctla.inventory_item_id = msi.inventory_item_id(+) AND msi.attribute2 = ebh.brm_item_id(+) AND rc.customer_id(+) = rcta.bill_to_customer_id AND rc1.customer_id(+) = rcta.ship_to_customer_id AND rcta.customer_trx_id = deferrepar.link_id(+) AND gjl.set_of_books_id = gsb.set_of_books_id(+); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Inserted', 'GL Deferred Rev Rec Man', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --SIR16682 Added 245117 DELETE FROM defer_ar_file WHERE order_number IS NULL AND SOURCE = 'GL' AND UPPER (NVL (trx_type, 'O')) <> 'MANUAL' AND account_description IN ('245116', '245112', '245114', '245117'); --REMOVED TSG ACCOUNTS FROM THIS COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Deleted', 'GL Deferred Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- LAR End of added section 9/11/03 ------------------------------------------------- -- ar transactions with links to oe --SIR6679 Added sob_currency_code --SIR17557 Add user_sort_char_7 --SIR19364 add deferred_months INSERT INTO briouser.defer_ar_file (so_line_no, account_description, bill_to_customer_name, bill_to_customer_no, book_rate, brm_item, DAY, extended_amount, family, currency_code, invoice_date, ar_line_no, line_id, MONTH, order_number, org_id, quantity, quarter, recon_item, revenue_amount, seq_no, ship_to_customer_name, ship_to_customer_no, trx_number, trx_type, trx_type_description, YEAR, customer_trx_line_id, customer_trx_id, order_category, order_type, organisation_id, sales_channel_code, header_id, box_type, product_type, conversion_type_code, ordered_date, approved_date, opp_no, deal_number, list_price, product, parent_line_id, source_header_id, attribute12, revenue_amount_functional, inventory_item_id, segment1, segment2, segment4, segment5, segment6, segment7, gl_post_date, gl_date, link_to_line_id, sob_id, sow_curr_code, exchange_date, earnout_start_date, account_class, initial_warranty, post_warranty, warranty_concession, model_link, defer_percent, status, SOURCE, mrc_posting_control_id, autorule_complete_flag, salesrep_id, sob_currency_code, user_sort_char_7, deferred_months) SELECT /*+ RULE */ so_lines_all.line_number so_line_no, gl_code_combinations.segment3 account_description, racustomers.customer_name bill_to_name, racustomers.customer_number bill_to_no, racustomer_trx_all.exchange_rate book_rate, opr.capability brm_item, TO_CHAR (NVL (ra_cust_trx_line_gl_dist_all.gl_date, racustomer_trx_all.trx_date ), 'DD' ) DAY, (NVL (ra_cust_trx_line_gl_dist_all.acctd_amount, 0) ) extended_amount, opr.family family, racustomer_trx_all.invoice_currency_code currency_code, racustomer_trx_all.trx_date invoice_date, racustomer_trx_lines_all.line_number ar_line_no, so_lines_all.line_id line_id, TO_CHAR (NVL (ra_cust_trx_line_gl_dist_all.gl_date, racustomer_trx_all.trx_date ), 'MM' ) MONTH, racustomer_trx_all.interface_header_attribute1 order_number, racustomer_trx_all.org_id org_id, DECODE (quantity_invoiced, NULL, DECODE (racustomer_trx_all.reason_code, 'CREDIT and REBILL', -quantity_credited, 'AR-CREDIT AND REBILL', -quantity_credited, 'AR-CONV TO 3RD PARTY LEASE', -quantity_credited, quantity_credited ), quantity_invoiced ) quantity, trx_date_breakdown.quarter (NVL (ra_cust_trx_line_gl_dist_all.gl_date, racustomer_trx_all.trx_date ) ) quarter, opr.cat_type recon_item, (NVL (ra_cust_trx_line_gl_dist_all.amount, 0)) revenue_amount, --SO_SALES_CREDITS.SALESREP_ID REPID_ORACLE, billings_stage_seq.NEXTVAL seq_no, racustomers$1.customer_name ship_to_name, racustomers$1.customer_number ship_to_no, racustomer_trx_all.trx_number trx_number, 'A', 'C', --RA_CUST_TRX_TYPES_ALL.TYPE TRX_TYPE, --RA_CUST_TRX_TYPES_ALL.DESCRIPTION TRX_TYPE_DESCRIPTION, TO_CHAR (NVL (ra_cust_trx_line_gl_dist_all.gl_date, racustomer_trx_all.trx_date ), 'YYYY' ) YEAR, racustomer_trx_lines_all.customer_trx_line_id customer_trx_line_id, racustomer_trx_lines_all.customer_trx_id customer_trx_id, so_headers_all.order_category order_category, --SIR08780 undo Winpeak -- will not come through this query so_order_types_all.NAME order_type, --SIR08780 undo Winpeak -- will not come through this query so_lines_all.warehouse_id organisation_id, so_headers_all.sales_channel_code sales_channel_code, so_headers_all.header_id header_id, --SIR08780 undo Winpeak -- will not come through this query --DECODE(RA_CUST_TRX_TYPES_ALL.TYPE,'CM',NVL(SO_HEADERS_ALL.SOURCE_HEADER_ID,SO_HEADERS_ALL.HEADER_ID), SO_HEADERS_ALL.HEADER_ID) HEADER_ID, opr.product_type box_type, opr.capability product_type, DECODE (racustomer_trx_all.exchange_rate_type, NULL, '1000', 'EMU FIXED', '1000', racustomer_trx_all.exchange_rate_type ) conversion_type_code, so_headers_all.date_ordered ordered_date, --SO_SALES_CREDITS.PERCENT PERCENT, so_headers_all.s8_date approved_date, so_headers_all.attribute2 opp_no, so_headers_all.attribute1 deal_number, --SIR08780 undo Winpeak -- will not come through this query racustomer_trx_lines_all.unit_standard_price list_price, opr.model product, so_lines_all.parent_line_id parent_line_id, so_headers_all.source_header_id source_header_id, so_lines_all.attribute12 attribute12, NVL (ra_cust_trx_line_gl_dist_all.acctd_amount, 0 ) revenue_amount_functional, NVL (so_lines_all.inventory_item_id, racustomer_trx_lines_all.inventory_item_id ) inventory_item_id, gl_code_combinations.segment1 segment1, gl_code_combinations.segment2 segment2, gl_code_combinations.segment4 segment4, gl_code_combinations.segment5 segment5, gl_code_combinations.segment6 segment6, gl_code_combinations.segment7 segment7, ra_cust_trx_line_gl_dist_all.gl_posted_date gl_post_date, ra_cust_trx_line_gl_dist_all.gl_date gl_date, so_lines_all.link_to_line_id link_to_line_id, racustomer_trx_all.set_of_books_id, so_headers_all.currency_code, racustomer_trx_all.trx_date exchange_date, --RACUSTOMER_TRX_ALL.EXCHANGE_DATE,SIR19364 --QUOTA_FLAG, TRUNC (racustomer_trx_lines_all.rule_start_date), ra_cust_trx_line_gl_dist_all.account_class, so_lines_all.attribute1, so_lines_all.attribute2, so_lines_all.attribute4, so_lines_all.attribute3, ra_cust_trx_line_gl_dist_all.PERCENT, apsa.status, 'OE', ra_cust_trx_line_gl_dist_all.mrc_posting_control_id, racustomer_trx_lines_all.autorule_complete_flag, deferrep.salesrep_id, gsb.currency_code, opr.user_sort_char_7, FLOOR (MONTHS_BETWEEN (ra_cust_trx_line_gl_dist_all.gl_date, v_month_day1 ) ) --SIR19364 FROM ar.ra_customer_trx_all racustomer_trx_all, ar.ra_customer_trx_lines_all racustomer_trx_lines_all, ar.ra_cust_trx_line_gl_dist_all ra_cust_trx_line_gl_dist_all, briouser.ar_payments_schedule_all apsa, ar.ra_customers racustomers, ar.ra_customers racustomers$1, gl.gl_code_combinations gl_code_combinations, oe.so_lines_all so_lines_all, oe.so_headers_all so_headers_all, oe.so_order_types_all so_order_types_all, briouser.oracle_productrollup opr, briouser.deferrep, gl.gl_sets_of_books gsb WHERE ( racustomer_trx_all.complete_flag = 'Y' AND NVL (racustomer_trx_all.interface_header_context, 'O') <> 'CONVERSIONS' AND NVL (so_order_types_all.attribute4, 'O') NOT IN ('CAP', 'CV', 'EINV', 'EXP', 'HOST', 'INV', 'TRADE') AND racustomer_trx_lines_all.interface_line_attribute6 IS NOT NULL AND ( ( so_lines_all.attribute3 > 10 OR opr.product_type IN ('WARRANTY', 'TRAINING') OR opr.cat_type = 'PREPAID' ) --SIR15882 Prepaid OR ( opr.product_type = 'SOFTWARE' AND gl_code_combinations.segment3 = '245114' ) OR ( opr.product_type = 'CUSTOMER SERVICE' AND gl_code_combinations.segment3 = '245117' ) ) ) AND racustomer_trx_all.customer_trx_id = apsa.customer_trx_id(+) AND opr.inventory_item_id(+) = racustomer_trx_lines_all.inventory_item_id AND racustomer_trx_all.customer_trx_id = racustomer_trx_lines_all.customer_trx_id AND ra_cust_trx_line_gl_dist_all.set_of_books_id(+) = racustomer_trx_lines_all.set_of_books_id AND ra_cust_trx_line_gl_dist_all.customer_trx_id(+) = racustomer_trx_lines_all.customer_trx_id AND ra_cust_trx_line_gl_dist_all.customer_trx_line_id(+) = racustomer_trx_lines_all.customer_trx_line_id AND so_lines_all.line_id(+) = racustomer_trx_lines_all.interface_line_attribute6 AND racustomers.customer_id(+) = racustomer_trx_all.bill_to_customer_id AND racustomers$1.customer_id(+) = racustomer_trx_all.ship_to_customer_id AND gl_code_combinations.code_combination_id(+) = ra_cust_trx_line_gl_dist_all.code_combination_id AND so_headers_all.header_id(+) = so_lines_all.header_id AND so_headers_all.header_id = deferrep.link_id(+) AND so_order_types_all.order_type_id(+) = so_headers_all.order_type_id AND racustomer_trx_all.set_of_books_id = gsb.set_of_books_id(+); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'AR_FILE', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- ar manual transactions --SIR6679 Added sob_currency_code --SIR17557 Add user_sort_char_7 --SIRMG18734 Add Winpeak --SIR19364 add deferred_months INSERT INTO briouser.defer_ar_file (account_description, bill_to_customer_name, bill_to_customer_no, book_rate, brm_item, DAY, extended_amount, family, currency_code, invoice_date, ar_line_no, line_id, MONTH, order_number, org_id, quantity, quarter, recon_item, revenue_amount, seq_no, ship_to_customer_name, ship_to_customer_no, trx_number, trx_type, trx_type_description, YEAR, customer_trx_line_id, customer_trx_id, order_category, order_type, organisation_id, sales_channel_code, header_id, box_type, product_type, conversion_type_code, ordered_date, approved_date, opp_no, deal_number, list_price, product, parent_line_id, source_header_id, attribute12, revenue_amount_functional, inventory_item_id, segment1, segment2, segment4, segment5, segment6, segment7, gl_post_date, gl_date, link_to_line_id, sob_id, sow_curr_code, exchange_date, earnout_start_date, account_class, initial_warranty, post_warranty, warranty_concession, model_link, defer_percent, status, SOURCE, mrc_posting_control_id, autorule_complete_flag, salesrep_id, sob_currency_code, user_sort_char_7, deferred_months) SELECT /*+ RULE */ gl_code_combinations.segment3 account_description, racustomers.customer_name bill_to_name, racustomers.customer_number bill_to_no, racustomer_trx_all.exchange_rate book_rate, opr.capability brm_item, TO_CHAR (NVL (ra_cust_trx_line_gl_dist_all.gl_date, racustomer_trx_all.trx_date ), 'DD' ) DAY, (NVL (ra_cust_trx_line_gl_dist_all.acctd_amount, 0) ) extended_amount, opr.family family, racustomer_trx_all.invoice_currency_code currency_code, racustomer_trx_all.trx_date invoice_date, racustomer_trx_lines_all.line_number ar_line_no, 0, --SO_LiNES_ALL.LiNE_iD LINE_ID,SIR19364 instead of nulls, zero TO_CHAR (NVL (ra_cust_trx_line_gl_dist_all.gl_date, racustomer_trx_all.trx_date ), 'MM' ) MONTH, racustomer_trx_all.interface_header_attribute1 order_number, racustomer_trx_all.org_id org_id, DECODE (quantity_invoiced, NULL, DECODE (racustomer_trx_all.reason_code, 'CREDIT and REBILL', -quantity_credited, 'AR-CREDIT AND REBILL', -quantity_credited, 'AR-CONV TO 3RD PARTY LEASE', -quantity_credited, quantity_credited ), quantity_invoiced ) quantity, trx_date_breakdown.quarter (NVL (ra_cust_trx_line_gl_dist_all.gl_date, racustomer_trx_all.trx_date ) ) quarter, opr.cat_type recon_item, (NVL (ra_cust_trx_line_gl_dist_all.amount, 0)) revenue_amount, --SO_SALES_CREDITS.SALESREP_ID REPID_ORACLE, billings_stage_seq.NEXTVAL seq_no, racustomers$1.customer_name ship_to_name, racustomers$1.customer_number ship_to_no, racustomer_trx_all.trx_number trx_number, 'A', 'C', --RA_CUST_TRX_TYPES_ALL.TYPE TRX_TYPE, --RA_CUST_TRX_TYPES_ALL.DESCRIPTION TRX_TYPE_DESCRIPTION, TO_CHAR (NVL (ra_cust_trx_line_gl_dist_all.gl_date, racustomer_trx_all.trx_date ), 'YYYY' ) YEAR, racustomer_trx_lines_all.customer_trx_line_id customer_trx_line_id, racustomer_trx_lines_all.customer_trx_id customer_trx_id, DECODE (racustomer_trx_all.interface_header_context, 'WINPEAK', 'R', so_headers_all.order_category ) order_category, --SIR18734 DECODE (racustomer_trx_all.interface_header_context, 'WINPEAK', 'Sale Of Maintenance', so_order_types_all.NAME ) order_type, --SIR18734 so_lines_all.warehouse_id organisation_id, so_headers_all.sales_channel_code sales_channel_code, DECODE (racustomer_trx_all.interface_header_context, 'WINPEAK', fn_getnumformat (racustomer_trx_lines_all.customer_trx_id), so_headers_all.header_id ) header_id, --SIR18734 --DECODE(RA_CUST_TRX_TYPES_ALL.TYPE,'CM',NVL(SO_HEADERS_ALL.SOURCE_HEADER_ID,SO_HEADERS_ALL.HEADER_ID), SO_HEADERS_ALL.HEADER_ID) HEADER_ID, opr.product_type box_type, opr.capability product_type, DECODE (racustomer_trx_all.exchange_rate_type, NULL, '1000', 'EMU FIXED', '1000', racustomer_trx_all.exchange_rate_type ) conversion_type_code, so_headers_all.date_ordered ordered_date, --SO_SALES_CREDITS.PERCENT PERCENT, so_headers_all.s8_date approved_date, so_headers_all.attribute2 opp_no, DECODE (racustomer_trx_all.interface_header_context, 'WINPEAK', racustomer_trx_all.interface_header_attribute1, so_headers_all.attribute1 ) deal_number, --SIR18734 racustomer_trx_lines_all.unit_standard_price list_price, opr.model product, so_lines_all.parent_line_id parent_line_id, so_headers_all.source_header_id source_header_id, so_lines_all.attribute12 attribute12, NVL (ra_cust_trx_line_gl_dist_all.acctd_amount, 0 ) revenue_amount_functional, NVL (so_lines_all.inventory_item_id, racustomer_trx_lines_all.inventory_item_id ) inventory_item_id, gl_code_combinations.segment1 segment1, gl_code_combinations.segment2 segment2, gl_code_combinations.segment4 segment4, gl_code_combinations.segment5 segment5, gl_code_combinations.segment6 segment6, gl_code_combinations.segment7 segment7, ra_cust_trx_line_gl_dist_all.gl_posted_date gl_post_date, --RA_CUST_TRX_LINE_GL_DIST_ALL.Original_Gl_Date GL_DATE, SIR19364 wrong GL date ra_cust_trx_line_gl_dist_all.gl_date, so_lines_all.link_to_line_id link_to_line_id, racustomer_trx_all.set_of_books_id, so_headers_all.currency_code, racustomer_trx_all.trx_date exchange_date, --RACUSTOMER_TRX_ALL.EXCHANGE_DATE, SIR19364 --QUOTA_FLAG, TRUNC (racustomer_trx_lines_all.rule_start_date), ra_cust_trx_line_gl_dist_all.account_class, so_lines_all.attribute1, racustomer_trx_lines_all.accounting_rule_duration, so_lines_all.attribute4, so_lines_all.attribute3, ra_cust_trx_line_gl_dist_all.PERCENT, apsa.status, 'MANUAL', ra_cust_trx_line_gl_dist_all.mrc_posting_control_id, racustomer_trx_lines_all.autorule_complete_flag, deferrepar.salesrep_id, gsb.currency_code, opr.user_sort_char_7, FLOOR (MONTHS_BETWEEN (ra_cust_trx_line_gl_dist_all.gl_date, v_month_day1 ) ) --SIR19364 FROM ar.ra_customer_trx_all racustomer_trx_all, ar.ra_customer_trx_lines_all racustomer_trx_lines_all, ar.ra_cust_trx_line_gl_dist_all ra_cust_trx_line_gl_dist_all, briouser.ar_payments_schedule_all apsa, ar.ra_customers racustomers, ar.ra_customers racustomers$1, gl.gl_code_combinations gl_code_combinations, oe.so_lines_all so_lines_all, oe.so_headers_all so_headers_all, oe.so_order_types_all so_order_types_all, briouser.oracle_productrollup opr, briouser.deferrepar, gl.gl_sets_of_books gsb WHERE ( racustomer_trx_all.complete_flag = 'Y' AND NVL (racustomer_trx_all.interface_header_context, 'O') <> 'CONVERSIONS' AND racustomer_trx_lines_all.interface_line_attribute6 IS NULL AND ( ( so_lines_all.attribute3 > 10 OR opr.product_type IN ('WARRANTY', 'TRAINING') OR opr.cat_type = 'PREPAID' ) --SIR15882 Prepaid OR ( opr.product_type = 'SOFTWARE' AND gl_code_combinations.segment3 = '245114' ) OR ( opr.product_type = 'CUSTOMER SERVICE' AND gl_code_combinations.segment3 = '245117' ) OR (racustomer_trx_all.interface_header_context = 'WINPEAK' ) -- and GL_CODE_COMBINATIONS.SEGMENT3 IN ('245101','245104'))SIR19368 ) ) AND racustomer_trx_all.customer_trx_id = apsa.customer_trx_id(+) AND opr.inventory_item_id(+) = racustomer_trx_lines_all.inventory_item_id AND racustomer_trx_all.customer_trx_id = racustomer_trx_lines_all.customer_trx_id AND ra_cust_trx_line_gl_dist_all.set_of_books_id(+) = racustomer_trx_lines_all.set_of_books_id AND ra_cust_trx_line_gl_dist_all.customer_trx_id(+) = racustomer_trx_lines_all.customer_trx_id AND ra_cust_trx_line_gl_dist_all.customer_trx_line_id(+) = racustomer_trx_lines_all.customer_trx_line_id AND so_lines_all.line_id(+) = racustomer_trx_lines_all.interface_line_attribute6 AND racustomers.customer_id(+) = racustomer_trx_all.bill_to_customer_id AND racustomers$1.customer_id(+) = racustomer_trx_all.ship_to_customer_id AND gl_code_combinations.code_combination_id(+) = ra_cust_trx_line_gl_dist_all.code_combination_id AND so_headers_all.header_id(+) = so_lines_all.header_id AND racustomer_trx_all.customer_trx_id = deferrepar.link_id(+) AND so_order_types_all.order_type_id(+) = so_headers_all.order_type_id AND racustomer_trx_all.set_of_books_id = gsb.set_of_books_id(+); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Inserted Trx', 'Manuals', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --SIRMG00015640 Use Exchage_Date instead of Invoice_Date in all references to the gl_currency_api --UPDATE DEFER_AR_FILE --SET EXCHANGE_DATE = INVOICE_DATE; --COMMIT; --SIRMG00012856 update to find original invoice date --SIR19368 UPDATE defer_ar_file a --SIRMG00015640 Use Exchange_Date instead of Invoice_Date in all references to the gl_currency_api --SET INVOICE_DATE = NVL((SELECT min(C.TRX_DATE) SET exchange_date = NVL ((SELECT MIN (c.trx_date) FROM ar.ra_customer_trx_all b, ar.ra_customer_trx_all c WHERE b.previous_customer_trx_id = c.customer_trx_id AND a.customer_trx_id = b.customer_trx_id AND b.previous_customer_trx_id IS NOT NULL GROUP BY b.customer_trx_id), exchange_date ) WHERE SOURCE IN ('OE', 'MANUAL'); SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated Trx', 'With Original Inv Date', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; UPDATE briouser.defer_ar_file SET exchange_date = v_curr_qtr_end_date WHERE exchange_date > v_curr_qtr_end_date; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated trx', 'Setting to Curr Qtr End Date any exchange date greater than this', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; /* --SIR6679 --cmr 4453 begin update briouser.defer_ar_file --SIRMG00015640 --set orig_defer_amt_usd = gl_currency_api.convert_amount(CURRENCY_CODE,'USD',invoice_date,DECODE(conversion_type_code,'User','1000','Spot','1000','EMU FIXED','1000',conversion_type_code),orig_defer_amt); set orig_defer_amt_usd = gl_currency_api.convert_amount(CURRENCY_CODE,'USD',EXCHANGE_date,DECODE(conversion_type_code,'User','1000','Spot','1000','EMU FIXED','1000',conversion_type_code),orig_defer_amt); commit; exception when others then Select nvl(max(slno),0)+1 into v_slno from Briouser.UpdateLog; Insert into BRIOUSER.UpdateLog values(v_slno,'Errors','Did not convert to USD',sysdate,to_char(sysdate,'hh24:mi:ss')); Commit; end; */ -- convert to USD UPDATE briouser.defer_ar_file SET revenue_amt_usd = revenue_amount, st_unearned_usd = shortterm_unearned_rev WHERE currency_code IN ('USD'); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Revenue_amt_usd', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; BEGIN -- convert to USD UPDATE briouser.defer_ar_file --SIRMG00015640 --set revenue_amt_usd = gl_currency_api.convert_amount(CURRENCY_CODE,'USD',invoice_date,DECODE(conversion_type_code,'User','1000','Spot','1000','EMU FIXED','1000',conversion_type_code),revenue_amount), --st_unearned_usd = gl_currency_api.convert_amount(CURRENCY_CODE,'USD',invoice_date,DECODE(conversion_type_code,'User','1000','Spot','1000','EMU FIXED','1000',conversion_type_code),SHORTTERM_UNEARNED_REV) SET revenue_amt_usd = gl_currency_api.convert_amount (currency_code, 'USD', exchange_date, DECODE (conversion_type_code, 'User', '1000', 'Spot', '1000', 'EMU FIXED', '1000', conversion_type_code ), revenue_amount ), st_unearned_usd = gl_currency_api.convert_amount (currency_code, 'USD', exchange_date, DECODE (conversion_type_code, 'User', '1000', 'Spot', '1000', 'EMU FIXED', '1000', conversion_type_code ), shortterm_unearned_rev ) WHERE currency_code NOT IN ('USD'); COMMIT; EXCEPTION WHEN OTHERS THEN SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Errors', 'Did not convert to USD', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; END; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Converted', 'Revenue_amt_usd', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- get the warranty months etc of the product line UPDATE briouser.defer_ar_file a SET (a.initial_warranty, a.post_warranty, a.warranty_concession) = (SELECT b.initial_warranty, b.post_warranty, b.warranty_concession FROM (SELECT oe.so_lines_all.header_id, NVL (MAX (so_lines_all.attribute1), 0 ) initial_warranty, NVL (MAX (so_lines_all.attribute2), 0 ) post_warranty, NVL (MAX (so_lines_all.attribute4), 0 ) warranty_concession, so_lines_all.attribute3 model_link FROM oe.so_lines_all, oe.so_headers_all WHERE so_headers_all.header_id = so_lines_all.header_id AND so_lines_all.attribute3 IS NOT NULL GROUP BY so_lines_all.attribute3, oe.so_lines_all.header_id) b WHERE a.header_id = b.header_id AND a.model_link = b.model_link) WHERE a.recon_item IN ('PREPAID', 'STD', 'PRM', 'WARRANTY UPG') --CMR3458, JMcC, Dec 3rd 03 AND SOURCE = 'OE'; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Warranty model link', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; UPDATE briouser.defer_ar_file a SET a.initial_warranty = 24, a.post_warranty = 0, a.warranty_concession = 0 WHERE a.recon_item IN ('STD', 'PRM') AND a.model_link IS NULL AND SOURCE = 'OE'; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'Warranty Mth', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --get the original line number of the parent UPDATE briouser.defer_ar_file a SET (a.so_line_number) = (SELECT DISTINCT b.line_number || '-' FROM oe.so_lines_all b WHERE a.parent_line_id = b.line_id) WHERE SOURCE = 'OE'; COMMIT; -- concatenate the line number fields UPDATE briouser.defer_ar_file a SET a.so_line_number = so_line_number || so_line_no; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'So_line_number', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- get link to line no from the product line /*update briouser.defer_ar_file a set (a.LINK_TO_SO_LINE_NO)= (select distinct b.so_line_number from briouser.defer_ar_file b where a.header_id = b.header_id and a.model_link = b.model_link and a.customer_trx_id = b.customer_trx_id and b.recon_item != 'PREPAID') where a.recon_item = 'PREPAID' and source ='OE'; commit; */ --delete the product lines DELETE FROM briouser.defer_ar_file WHERE recon_item NOT IN ('PREPAID', 'STD', 'PRM', 'PREPAID MAINT', 'WARRANTY UPG', 'TRAINING', 'CUSTOMER EDUCATION', 'LEVEL1', 'CONSULTING', 'LEGATO' ) -- CMR 3666, JMCC, Dec 11th 03 AND account_description NOT IN ('245101', '245104', '245102', '245119', '245121', '245112', '245114', '245117', '245131' ) --cmr 4435 to allow redirects to stay in AND user_sort_char_7 <> 'LEGATO' -- SIRMG00017557 user_sort_char_7 AND order_type <> 'Sale Of Maintenance'; -- SIRMG00018734 COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Deleted', 'Unwanted records', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; DELETE FROM defer_ar_file WHERE order_type LIKE 'OP-OPER%' OR order_type LIKE 'RT-RENTAL%'; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'Updates', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- take first line of unearned to get the original deferred amount for OE -- SIR19364 condense 4 orig_defer_amt updates into two UPDATE briouser.defer_ar_file a SET orig_defer_amt = (SELECT DISTINCT revenue_amount FROM briouser.defer_ar_file b --where account_class ='UNEARN' WHERE account_class = 'UNEARN' AND revenue_amount != 0 AND ROUND (defer_percent, 0) = 100 AND a.line_id = b.line_id AND a.customer_trx_id = b.customer_trx_id AND a.customer_trx_line_id = b.customer_trx_line_id) --where source ='OE' WHERE SOURCE IN ('OE', 'MANUAL') AND account_description NOT IN ('245112', '245114'); --CMR4951, JMcC, Feb24th COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Orig_defer_amt Unearn', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; UPDATE briouser.defer_ar_file a SET orig_defer_amt = (SELECT DISTINCT revenue_amount FROM briouser.defer_ar_file b --where account_class ='UNEARN' WHERE account_class = 'REV' AND revenue_amount != 0 AND ROUND (defer_percent, 0) = 100 AND a.line_id = b.line_id AND a.customer_trx_id = b.customer_trx_id AND a.customer_trx_line_id = b.customer_trx_line_id) --where source ='OE' WHERE SOURCE IN ('OE', 'MANUAL') AND account_description IN ('245112', '245114'); --CMR4951, JMcC, Feb24th COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Orig_defer_amt Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; /*SIR19364 remove the original 4. Replaced by the one just above this. -- take first line of unearned to get the original deferred amount for OE update briouser.defer_ar_file a set orig_defer_amt = (select distinct revenue_amount from briouser.defer_ar_file b where account_class ='UNEARN' and revenue_amount !=0 and round(defer_percent,0) = 100 and A.LINE_ID =B.LINE_ID and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id) where source ='OE' and account_description not in ('245112','245114');--CMR4951, JMcC, Feb24th COMMIT; -- take first line of unearned to get the original deferred amount for OE update briouser.defer_ar_file a set orig_defer_amt = (select distinct revenue_amount from briouser.defer_ar_file b where account_class ='REV' and revenue_amount !=0 and round(defer_percent,0) = 100 and A.LINE_ID =B.LINE_ID and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id) where source ='OE' and account_description in ('245112','245114');--CMR5063, JMcC, Feb25th COMMIT; --SIR15882 comment out this statement --delete from briouser.defer_ar_file --where orig_defer_amt is null; --commit; -- take first line of unearned to get the original deferred amount for Manual update briouser.defer_ar_file a set orig_defer_amt = (select distinct revenue_amount from briouser.defer_ar_file b where account_class ='UNEARN' and revenue_amount !=0 and round(defer_percent,0) = 100 and A.customer_trx_line_ID =B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id) where source ='MANUAL' and account_description not in ('245112','245114');--CMR5167, MB, Mar 1st COMMIT; -- take first line of unearned to get the original deferred amount for Manual update briouser.defer_ar_file a set orig_defer_amt = (select distinct revenue_amount from briouser.defer_ar_file b where account_class ='REV' and revenue_amount !=0 and round(defer_percent,0) = 100 and A.customer_trx_line_ID =B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id) where source ='MANUAL' and account_description in ('245112','245114');--CMR5167, MB, Mar 1st COMMIT; */ -- end SIR19364 -- convert to USD UPDATE briouser.defer_ar_file SET orig_defer_amt_usd = orig_defer_amt WHERE currency_code IN ('USD'); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'orig_defer_amt_usd', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- SIR23855 DELETE FROM briouser.defer_ar_file WHERE trx_number IN ('NOR5001057', 'NOR6171'); COMMIT; BEGIN -- convert to USD UPDATE briouser.defer_ar_file --SIRMG00015640 --set orig_defer_amt_usd = gl_currency_api.convert_amount(CURRENCY_CODE,'USD',invoice_date,DECODE(conversion_type_code,'User','1000','Spot','1000','EMU FIXED','1000',conversion_type_code),orig_defer_amt); SET orig_defer_amt_usd = gl_currency_api.convert_amount (currency_code, 'USD', exchange_date, DECODE (conversion_type_code, 'User', '1000', 'Spot', '1000', 'EMU FIXED', '1000', conversion_type_code ), orig_defer_amt ) WHERE currency_code <> 'USD'; COMMIT; EXCEPTION WHEN OTHERS THEN SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Errors', 'Did not convert to USD', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; END; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Converted', 'Orig Defer Amt', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- start of SIR6679 use sob_currency_code to get the sob_rate, which we'll use as a computed item in the report BEGIN UPDATE briouser.defer_ar_file SET sob_rate = gl_currency_api.get_rate (currency_code, NVL (sob_currency_code, currency_code ), exchange_date, DECODE (conversion_type_code, 'User', '1000', 'Spot', '1000', 'EMU FIXED', '1000', conversion_type_code ) ); COMMIT; EXCEPTION WHEN OTHERS THEN SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Errors', 'Did not get Set of Books currency rate', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; END; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Converted', 'Set of Books currency rate', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- end of SIR6679 --SIR19364 condense 4 prior months updates into one UPDATE briouser.defer_ar_file a SET (prior_mths_earned_rev, prior_months_us) = (SELECT NVL (SUM (revenue_amount), 0), NVL (SUM (revenue_amt_usd), 0) FROM briouser.defer_ar_file b WHERE account_class = 'REV' AND a.customer_trx_id = b.customer_trx_id AND a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR (gl_date, 'YYYYMM') < v_month) WHERE SOURCE IN ('OE', 'MANUAL'); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Prior Months', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; /* update briouser.defer_ar_file a set PRIOR_MTHS_EARNED_REV = (select distinct NVL(SUM(revenue_amount),0) from briouser.defer_ar_file b where account_class ='REV' and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') < v_month) where source ='OE'; COMMIT; update briouser.defer_ar_file a set PRIOR_MONTHS_US = (select distinct NVL(SUM(revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='REV' and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') < v_month) where source ='OE'; COMMIT; update briouser.defer_ar_file a set PRIOR_MTHS_EARNED_REV = (select distinct NVL(SUM(revenue_amount),0) from briouser.defer_ar_file b where account_class ='REV' and A.customer_trx_line_ID =B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') < v_month) where source ='MANUAL'; COMMIT; update briouser.defer_ar_file a set PRIOR_MONTHS_US = (select distinct NVL(SUM(revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='REV' and A.customer_trx_line_ID =B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') < v_month) where source ='MANUAL'; COMMIT; */ --end SIR19364 --SIR19364 condense 4 current month updates into one UPDATE briouser.defer_ar_file a SET (curr_mths_earned_rev, curr_months_usd) = (SELECT NVL (SUM (revenue_amount), 0), NVL (SUM (revenue_amt_usd), 0) FROM briouser.defer_ar_file b WHERE account_class = 'REV' AND a.customer_trx_id = b.customer_trx_id AND a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR (gl_date, 'YYYYMM') = v_month) WHERE SOURCE IN ('OE', 'MANUAL'); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Current Months', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; /* update briouser.defer_ar_file a set CURR_MTHS_EARNED_REV = (select distinct NVL(SUM(revenue_amount),0) from briouser.defer_ar_file b where account_class ='REV' and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = v_month) where source ='OE'; COMMIT; update briouser.defer_ar_file a set CURR_MONTHS_USD = (select distinct NVL(SUM(revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='REV' and a.customer_trx_id = b.customer_trx_id and A.customer_trx_line_ID = B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = v_month) where source ='OE'; COMMIT; update briouser.defer_ar_file a set CURR_MTHS_EARNED_REV = (select distinct NVL(SUM(revenue_amount),0) from briouser.defer_ar_file b where account_class ='REV' and A.customer_trx_line_ID = B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = v_month) where source ='MANUAL'; COMMIT; update briouser.defer_ar_file a set CURR_MONTHS_USD = (select distinct NVL(SUM(revenue_amt_USD),0) from briouser.defer_ar_file b where account_class ='REV' and A.customer_trx_line_ID = B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = v_month) where source ='MANUAL'; COMMIT; */ --SIR19364 UPDATE briouser.defer_ar_file SET avg_amount = (orig_defer_amt * defer_percent) / 100; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Average Amount', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --SIR19364 condense 4 short-term updates into one --put back in customer_trx_id link for all UPDATE briouser.defer_ar_file a SET (shortterm_unearned_rev, st_unearned_usd) = (SELECT NVL (SUM (-revenue_amount), 0), NVL (SUM (-revenue_amt_usd), 0) FROM briouser.defer_ar_file b WHERE account_class = 'UNEARN' AND a.line_id = b.line_id AND a.customer_trx_id = b.customer_trx_id AND a.customer_trx_line_id = b.customer_trx_line_id --CMR6430 issue with ST column not matching Months, missing link AND TO_CHAR (gl_date, 'YYYYMM') <= v_month + 100 AND TO_CHAR (gl_date, 'YYYYMM') > v_month) WHERE SOURCE IN ('OE', 'MANUAL'); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Short Term', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; /* update briouser.defer_ar_file a set shortterm_unearned_rev = (select distinct NVL(SUM(-revenue_amount),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID =B.LINE_ID --and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id --CMR6430 issue with ST column not matching Months, missing link AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') <= v_month + 100 AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') > v_month) where source ='OE'; COMMIT; update briouser.defer_ar_file a set shortterm_unearned_rev = (select distinct NVL(SUM(-revenue_amount),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') <= v_month + 100 AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') > v_month) where source in ('MANUAL'); COMMIT; Select nvl(max(slno),0)+1 into v_slno from Briouser.UpdateLog; Insert into BRIOUSER.UpdateLog values(v_slno,'Completed','ShortTerm',sysdate,to_char(sysdate,'hh24:mi:ss')); Commit; update briouser.defer_ar_file a set st_unearned_usd = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID =B.LINE_ID --and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id -- CMR 6430 issue with ST column not matching Months, missing link AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') <= v_month + 100 AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') > v_month) where source ='OE'; COMMIT; update briouser.defer_ar_file a set st_unearned_usd = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') <= v_month + 100 AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') > v_month) where source ='MANUAL'; COMMIT; */ -- end SIR19364 UPDATE briouser.defer_ar_file a SET longterm_unearned_rev = orig_defer_amt - ( curr_mths_earned_rev + prior_mths_earned_rev + shortterm_unearned_rev ); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Long Term Unearned Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; UPDATE briouser.defer_ar_file a SET lt_unearned_usd = orig_defer_amt_usd - (curr_months_usd + prior_months_us + st_unearned_usd); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Long Term Unearned USD', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; SELECT SUBSTR (GLOBAL_NAME, 1, 4) INTO v_server FROM GLOBAL_NAME; --SIRMG00015522, JMcC, Commenting out if stmt below --if v_server = 'BP02' then --CMR 6430 Populate Month1 with the values from the ST Column. UPDATE briouser.defer_ar_file a SET month1 = st_unearned_usd WHERE SOURCE = 'GL'; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Month1 GL', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- start SIR19364 replace 25 month updates with one UPDATE briouser.defer_ar_file a SET revenue_per_month = (SELECT SUM (-revenue_amt_usd) FROM briouser.defer_ar_file b WHERE account_class = 'UNEARN' AND a.customer_trx_line_id = b.customer_trx_line_id AND a.line_id = b.line_id AND a.deferred_months = b.deferred_months) WHERE account_class = 'UNEARN' AND SOURCE IN ('OE', 'MANUAL'); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Months', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --end SIR19364 /* update briouser.defer_ar_file a set month1 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,1),'YYYY') || TO_CHAR(add_months(v_date,1),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month1 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,1),'YYYY') || TO_CHAR(add_months(v_date,1),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month1 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,1),'YYYY') || TO_CHAR(add_months(v_date,1),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month2 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,2),'YYYY') || TO_CHAR(add_months(v_date,2),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month2 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,2),'YYYY') || TO_CHAR(add_months(v_date,2),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month3 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,3),'YYYY') || TO_CHAR(add_months(v_date,3),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month3 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,3),'YYYY') || TO_CHAR(add_months(v_date,3),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month4 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,4),'YYYY') || TO_CHAR(add_months(v_date,4),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month4 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,4),'YYYY') || TO_CHAR(add_months(v_date,4),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month5 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,5),'YYYY') || TO_CHAR(add_months(v_date,5),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month5 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,5),'YYYY') || TO_CHAR(add_months(v_date,5),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month6 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,6),'YYYY') || TO_CHAR(add_months(v_date,6),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month6 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,6),'YYYY') || TO_CHAR(add_months(v_date,6),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month7 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,7),'YYYY') || TO_CHAR(add_months(v_date,7),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month7 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,7),'YYYY') || TO_CHAR(add_months(v_date,7),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month8 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,8),'YYYY') || TO_CHAR(add_months(v_date,8),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month8 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,8),'YYYY') || TO_CHAR(add_months(v_date,8),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month9 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,9),'YYYY') || TO_CHAR(add_months(v_date,9),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month9 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,9),'YYYY') || TO_CHAR(add_months(v_date,9),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month10 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,10),'YYYY') || TO_CHAR(add_months(v_date,10),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month10 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,10),'YYYY') || TO_CHAR(add_months(v_date,10),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month11 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,11),'YYYY') || TO_CHAR(add_months(v_date,11),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month11 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,11),'YYYY') || TO_CHAR(add_months(v_date,11),'MM') ) where source ='MANUAL'; update briouser.defer_ar_file a set month12 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.LINE_ID = B.LINE_ID and a.customer_trx_line_id = b.customer_trx_line_id AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,12),'YYYY') || TO_CHAR(add_months(v_date,12),'MM') ) where source ='OE'; update briouser.defer_ar_file a set month12 = (select distinct NVL(SUM(-revenue_amt_usd),0) from briouser.defer_ar_file b where account_class ='UNEARN' and A.customer_trx_line_ID =B.customer_trx_line_ID AND TO_CHAR(GL_DATE,'YYYY') || TO_CHAR(GL_DATE,'MM') = TO_CHAR(add_months(v_date,12),'YYYY') || TO_CHAR(add_months(v_date,12),'MM') ) where source ='MANUAL'; */ --SIRMG00015522, JMcC, Commenting out end if below --end if; --SIR19364 condense 4 current qtr rollup updates into one UPDATE briouser.defer_ar_file a SET (curr_qtr_earned_rev, curr_qtr_mths_usd) = (SELECT NVL (SUM (revenue_amount), 0), NVL (SUM (revenue_amount), 0) FROM briouser.defer_ar_file b, briouser.quarterdates c WHERE account_class = 'REV' AND a.customer_trx_id = b.customer_trx_id AND a.customer_trx_line_id = b.customer_trx_line_id AND 'Q' || TO_CHAR (gl_date, 'Q') || TO_CHAR (gl_date, 'YYYY') = c.quarter AND c.current_qtr = 'Y') WHERE SOURCE IN ('OE', 'MANUAL'); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Updated', 'Curr Qtr Earned', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; /* --05May05 SIRMG00018736 JZ Add current qtr rollup for cs revenue project update briouser.defer_ar_file a set CURR_QTR_EARNED_REV = (select distinct NVL(SUM(revenue_amount),0) from briouser.defer_ar_file b, briouser.quarterdates c where account_class ='REV' and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id AND 'Q' || TO_CHAR(GL_DATE,'Q') || TO_CHAR(GL_DATE,'YYYY') = c.quarter And c.current_qtr='Y') where source ='OE'; COMMIT; -- 05May05 SIRMG00018736 JZ Add current qtr rollup for cs revenue project update briouser.defer_ar_file a set CURR_QTR_MTHS_USD = (select distinct NVL(SUM(revenue_amt_usd),0) from briouser.defer_ar_file b, briouser.quarterdates c where account_class ='REV' and a.customer_trx_id = b.customer_trx_id and a.customer_trx_line_id = b.customer_trx_line_id AND 'Q' || TO_CHAR(GL_DATE,'Q') || TO_CHAR(GL_DATE,'YYYY') = c.quarter And c.current_qtr='Y') where source ='OE'; COMMIT; --05May05 SIRMG00018736 JZ Add current qtr rollup for cs revenue project update briouser.defer_ar_file a set CURR_QTR_EARNED_REV = (select distinct NVL(SUM(revenue_amount),0) from briouser.defer_ar_file b, briouser.quarterdates c where account_class ='REV' and A.customer_trx_line_ID =B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id AND 'Q' || TO_CHAR(GL_DATE,'Q') || TO_CHAR(GL_DATE,'YYYY') = c.quarter And c.current_qtr='Y') where source ='MANUAL'; COMMIT; --05May05 SIRMG00018736 JZ Add current qtr rollup for cs revenue project update briouser.defer_ar_file a set CURR_QTR_MTHS_USD = (select distinct NVL(SUM(revenue_amt_usd),0) from briouser.defer_ar_file b, briouser.quarterdates c where account_class ='REV' and A.customer_trx_line_ID =B.customer_trx_line_ID and a.customer_trx_id = b.customer_trx_id AND 'Q' || TO_CHAR(GL_DATE,'Q') || TO_CHAR(GL_DATE,'YYYY') = c.quarter And c.current_qtr='Y') where source ='MANUAL'; COMMIT; */ EXECUTE IMMEDIATE 'TRUNCATE TABLE briouser.DEFERROLLUP DROP STORAGE'; COMMIT; --SIR6679 Added sob_rate --SIR17557 Add user_sort_char_7 -- SIR19015 Remove account 245115 -- SIR18736 Add curr_qtr_earned_rev and curr_qtr_mths_usd INSERT INTO briouser.deferrollup SELECT al1.bill_to_customer_name, NVL (al1.so_line_number, al1.customer_trx_line_id), al1.sob_id, al1.status, al1.trx_number, al1.warranty_concession, al1.product_type, al1.product, al1.post_warranty, al1.prior_mths_earned_rev, al1.orig_defer_amt, al1.org_name, al1.org_id, al1.ordered_date, al1.order_number, al1.link_to_so_line_no, al1.initial_warranty, al1.earnout_start_date, al1.deal_number, al1.currency_code, al1.curr_mths_earned_rev, al1.box_type, al1.header_id, al1.model_link, al1.family, al1.recon_item, SYSDATE AS update_time, al1.sob_name, al1.account_class, al1.account_description, al1.order_type, al1.sales_channel_code, al1.segment7, al1.segment6, al1.segment5, al1.segment4, al1.segment2, al1.segment1, al1.SOURCE, al1.customer_trx_id, al1.trx_type, al1.invoice_date, 0, 0, al1.orig_defer_amt_usd, al1.prior_months_us, al1.curr_months_usd, al1.state, al1.salesrep_id, siebelbrmareaid, brmareaname, siebelreportinglevelid, reportinglevelname, siebeldivisionid, divisionname, siebelsuperregionid, superregionname, siebelregionid, regionname, siebelsuperareaid, superareaname, siebelareaid, areaname, NVL (siebeldistrict, 'CLEANUP') AS siebeldistrict, b.districtname, NVL (repname, 'CLEANUP') AS rep_name, divisionmgr, regionmgr, districtmgr, areamgr, al1.shortterm_unearned_rev, al1.st_unearned_usd, al1.longterm_unearned_rev, al1.lt_unearned_usd, al1.month1, al1.month2, al1.month3, al1.month4, al1.month5, al1.month6, al1.month7, al1.month8, al1.month9, al1.month10, al1.month11, al1.month12, 'N' AS inmeageddebt, brm_item, model, al1.ship_to_customer_name, '', al1.sob_rate, al1.user_sort_char_7, curr_qtr_earned_rev, curr_qtr_mths_usd, NULL, NULL, NULL --SIRMG00012855 FROM briouser.defer_ar_file al1, siebel.salesperson_details b, siebel.hierarchyrollup c WHERE al1.salesrep_id = b.oraclerepid(+) AND al1.invoice_date >= b.startdate(+) AND al1.invoice_date <= b.enddate(+) AND b.siebelarea = c.buffer_id(+) AND (al1.account_description IN ('245101', '245102', '245104', '245112', '245114', '245116', '245117', '245119', '245121', '245131' ) ) --SIR16682 Added 245117 AND SOURCE IN ('GL'); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Inserted', 'GL into Deferrollup', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --cmr 4453 --SIR6679 Added sob_rate --SIR17557 Add user_sort_char_7 -- SIR99999 Remove account 245115 -- SIR18736 Add curr_qtr_earned_rev and curr_qtr_mths_usd --SIR19364 use revenue_per_month field instead of individual month fields. INSERT INTO briouser.deferrollup SELECT al1.bill_to_customer_name, NVL (al1.so_line_number, al1.customer_trx_line_id), al1.sob_id, al1.status, al1.trx_number, AVG (al1.warranty_concession) AS conc, al1.product_type, al1.product, AVG (al1.post_warranty) AS post_warr, AVG (al1.prior_mths_earned_rev) AS prior_mths, al1.orig_defer_amt, al1.org_name, al1.org_id, al1.ordered_date, al1.order_number, al1.link_to_so_line_no, AVG (al1.initial_warranty) AS warr, al1.earnout_start_date, al1.deal_number, al1.currency_code, AVG (al1.curr_mths_earned_rev) AS curr_mths, al1.box_type, al1.header_id, al1.model_link, al1.family, al1.recon_item, SYSDATE AS update_time, al1.sob_name, al1.account_class, al1.account_description, al1.order_type, al1.sales_channel_code, al1.segment7, al1.segment6, al1.segment5, al1.segment4, al1.segment2, al1.segment1, al1.SOURCE, al1.customer_trx_id, al1.trx_type, al1.invoice_date, 0, 0, al1.orig_defer_amt_usd, AVG (al1.prior_months_us) AS prior_mths_usd, AVG (al1.curr_months_usd) AS curr_mths_usd, al1.state, al1.salesrep_id, siebelbrmareaid, brmareaname, siebelreportinglevelid, reportinglevelname, siebeldivisionid, divisionname, siebelsuperregionid, superregionname, siebelregionid, regionname, siebelsuperareaid, superareaname, siebelareaid, areaname, NVL (siebeldistrict, 'CLEANUP') AS siebeldistrict, b.districtname, NVL (repname, 'CLEANUP') AS rep_name, divisionmgr, regionmgr, districtmgr, areamgr, AVG (al1.shortterm_unearned_rev) AS st_unearned_rev, AVG (al1.st_unearned_usd) AS st_unearned_usd, AVG (al1.longterm_unearned_rev) AS lt_unearned_rev, AVG (al1.lt_unearned_usd) AS lt_unearned_usd, --SIR19364 SUM (DECODE (al1.deferred_months, 1, al1.revenue_per_month, 0) ) AS month1, SUM (DECODE (al1.deferred_months, 2, al1.revenue_per_month, 0) ) AS month2, SUM (DECODE (al1.deferred_months, 3, al1.revenue_per_month, 0) ) AS month3, SUM (DECODE (al1.deferred_months, 4, al1.revenue_per_month, 0) ) AS month4, SUM (DECODE (al1.deferred_months, 5, al1.revenue_per_month, 0) ) AS month5, SUM (DECODE (al1.deferred_months, 6, al1.revenue_per_month, 0) ) AS month6, SUM (DECODE (al1.deferred_months, 7, al1.revenue_per_month, 0) ) AS month7, SUM (DECODE (al1.deferred_months, 8, al1.revenue_per_month, 0) ) AS month8, SUM (DECODE (al1.deferred_months, 9, al1.revenue_per_month, 0) ) AS month9, SUM (DECODE (al1.deferred_months, 10, al1.revenue_per_month, 0 ) ) AS month10, SUM (DECODE (al1.deferred_months, 11, al1.revenue_per_month, 0 ) ) AS month11, SUM (DECODE (al1.deferred_months, 12, al1.revenue_per_month, 0 ) ) AS month12, /* AVG ( AL1.MONTH1 ) AS MONTH1,AVG ( AL1.MONTH2 ) AS MONTH2,AVG ( AL1.MONTH3 ) AS MONTH3, AVG ( AL1.MONTH4 ) AS MONTH4,AVG ( AL1.MONTH5 ) AS MONTH5,AVG ( AL1.MONTH6 ) AS MONTH6, AVG ( AL1.MONTH7 ) AS MONTH7,AVG ( AL1.MONTH8 ) AS MONTH8,AVG ( AL1.MONTH9 ) AS MONTH9, AVG ( AL1.MONTH10 ) AS MONTH10,AVG ( AL1.MONTH11 ) AS MONTH11,AVG ( AL1.MONTH12 ) AS MONTH12,*/-- SIR19364 'N' AS inmeageddebt, brm_item, model, al1.ship_to_customer_name, '', al1.sob_rate, al1.user_sort_char_7, curr_qtr_earned_rev, curr_qtr_mths_usd, NULL, NULL, NULL --SIRMG00012855 FROM briouser.defer_ar_file al1, siebel.salesperson_details b, siebel.hierarchyrollup c WHERE al1.salesrep_id = b.oraclerepid(+) AND al1.invoice_date >= b.startdate(+) AND al1.invoice_date <= b.enddate(+) AND b.siebelarea = c.buffer_id(+) AND (al1.account_description IN ('245101', '245102', '245104', '245112', '245114', '245115', '245116', '245117', '245119', '245121', '245131' ) ) --SIR16682 Added 245117 AND SOURCE IN ('OE', 'MANUAL') GROUP BY al1.bill_to_customer_name, al1.so_line_number, al1.customer_trx_line_id, al1.sob_id, al1.status, al1.trx_number, al1.product_type, al1.product, al1.orig_defer_amt, al1.org_name, al1.org_id, al1.ordered_date, al1.order_number, al1.link_to_so_line_no, al1.earnout_start_date, al1.deal_number, al1.currency_code, al1.box_type, al1.header_id, al1.model_link, al1.family, al1.recon_item, al1.sob_name, al1.account_class, al1.account_description, al1.order_type, al1.sales_channel_code, al1.segment7, al1.segment6, al1.segment5, al1.segment4, al1.segment2, al1.segment1, al1.SOURCE, al1.customer_trx_id, al1.trx_type, al1.invoice_date, al1.orig_defer_amt_usd, al1.state, al1.salesrep_id, siebelbrmareaid, brmareaname, siebelreportinglevelid, reportinglevelname, siebeldivisionid, divisionname, siebelsuperregionid, superregionname, siebelregionid, regionname, siebelsuperareaid, superareaname, siebelareaid, areaname, siebeldistrict, b.districtname, divisionmgr, regionmgr, districtmgr, areamgr, repname, al1.shortterm_unearned_rev, al1.longterm_unearned_rev, al1.lt_unearned_usd, al1.st_unearned_usd, month1, month2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12, brm_item, model, al1.ship_to_customer_name, al1.sob_rate, al1.user_sort_char_7, curr_qtr_earned_rev, curr_qtr_mths_usd; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Inserted', 'OE into Deferrollup', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; UPDATE briouser.deferrollup SET districtname = 'CLEANUP' WHERE districtname IS NULL; COMMIT; UPDATE briouser.deferrollup SET rep_name = 'CLEANUP' WHERE rep_name IS NULL; COMMIT; UPDATE deferrollup SET districtname = '1-1OEPH', siebelbrmareaid = '1-3Y1HD', brmareaname = 'Intl/Corporate BRM Area', reportinglevelname = 'International Corp Adjust Area2-ICADJ', siebelareaid = '1-1OEP5', siebeldistrict = '1-1OEPH' WHERE (districtname = 'CLEANUP' OR rep_name = 'CLEANUP') AND org_id NOT IN (0, 30, 52, 101, 99); UPDATE deferrollup SET districtname = '1-1OC7Y', siebelbrmareaid = '1-3Y1HG', brmareaname = 'Dom/Corporate BRM Area', reportinglevelname = 'Domestic Corp Adjust Area-DADJ', siebelareaid = '1-1OC7V', siebeldistrict = '1-1OC7Y' WHERE (districtname = 'CLEANUP' OR rep_name = 'CLEANUP') AND org_id IN (0, 30, 52, 101, 99); COMMIT; UPDATE deferrollup a SET (a.siebeldistrict, a.rep_name, a.siebelbrmareaid, a.brmareaname, a.siebelreportinglevelid, a.reportinglevelname, a.siebeldivisionid, a.divisionname, a.siebelsuperregionid, a.superregionname, a.siebelregionid, a.regionname, a.siebelsuperareaid, a.superareaname, a.siebelareaid, a.areaname, a.districtname, districtmgr, areamgr, divisionmgr, regionmgr) = (SELECT b.siebeldistrict, b.repname, c.siebelbrmareaid, c.brmareaname, c.siebelreportinglevelid, c.reportinglevelname, c.siebeldivisionid, c.divisionname, c.siebelsuperregionid, c.superregionname, c.siebelregionid, c.regionname, c.siebelsuperareaid, c.superareaname, c.siebelareaid, c.areaname, b.districtname, b.districtmgr, areamgr, divisionmgr, regionmgr FROM siebel.salesperson_details b, siebel.hierarchyrollup c WHERE active IN ('Y', 'L') AND b.oraclerepid(+) = a.salesrep_id AND b.siebelarea = c.buffer_id(+)) WHERE districtname NOT IN (SELECT DISTINCT buffer_id FROM siebel.hierarchyrollup) OR districtname = 'CLEANUP' OR rep_name = 'CLEANUP'; COMMIT; --CMR5434 UPDATE briouser.deferrollup a SET a.org_id = (SELECT b.orgid FROM orgidsetofbooks b WHERE b.sobid = a.sob_id) WHERE org_id IS NULL; COMMIT; UPDATE briouser.deferrollup a SET a.org_name = (SELECT MAX (b.NAME) FROM hr.hr_all_organization_units b WHERE a.org_id = b.organization_id(+)); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'Org Updates', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; UPDATE briouser.deferrollup a SET a.sob_name = (SELECT MAX (b.short_name) FROM gl.gl_sets_of_books b WHERE a.sob_id = b.set_of_books_id(+)); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'SOB Updates', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; UPDATE briouser.deferrollup a SET a.state = (SELECT MAX (state) FROM ar.ra_addresses_all raa, ar.ra_customer_trx_all rcta, ar.ra_site_uses_all rsua WHERE a.customer_trx_id = rcta.customer_trx_id AND rcta.ship_to_site_use_id = rsua.site_use_id AND rsua.address_id = raa.address_id(+)); COMMIT; UPDATE briouser.deferrollup SET month1 = orig_defer_amt_usd WHERE account_description IN ('245119', '245102', '245121', '245131'); COMMIT; UPDATE briouser.deferrollup SET earnout_start_date = ADD_MONTHS (earnout_start_date, 1) WHERE account_description = '245119'; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'State Updates', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --POS to add brmitem and model for Leo 30/12/2003 EXECUTE IMMEDIATE 'TRUNCATE TABLE briouser.modellink_revenue DROP STORAGE'; INSERT INTO briouser.modellink_revenue (parent_line_id, header_id, attribute3, revenue_amount, model, customer_trx_line_id) SELECT DISTINCT parent_line_id, header_id, a.attribute3, 0, c.model, b.customer_trx_line_id FROM oe.so_lines_all a, ar.ra_customer_trx_lines_all b, briouser.oracle_productrollup c WHERE a.line_id = interface_line_attribute6(+) AND a.inventory_item_id = c.inventory_item_id(+) AND a.attribute3 IS NOT NULL AND c.cat_type NOT IN ('PREPAID', 'PRECONTRACTED') --and c.capability in ('SFT','HDW') AND b.revenue_amount IS NOT NULL; UPDATE briouser.deferrollup a SET (model) = (SELECT MAX (b.model) FROM briouser.modellink_revenue b WHERE a.model_link = b.attribute3 AND a.header_id = b.header_id); COMMIT; UPDATE briouser.deferrollup a SET (brm_item) = (SELECT DISTINCT d.brm_item FROM briouser.mtl_system_items c, emcinv.emcinv_brm_hierarchy d WHERE a.model = c.segment1 AND c.attribute2 = d.brm_item_id(+)); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'Updates Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; --SIRMG00012345 UPDATE briouser.deferrollup a SET a.installat_location_id = (SELECT MAX (b.installat_location) FROM briouser.customer_dim b WHERE a.header_id = b.header_id(+)); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'INSTALL_AT', 'Updates Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; UPDATE briouser.deferrollup b SET header_id = (SELECT DISTINCT header_id FROM briouser.brio_so_headers_all a WHERE a.order_number = b.order_number) WHERE header_id IS NULL; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Null Header_Ids', 'Updates Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- SIR19368 update null deal numbers UPDATE briouser.deferrollup b SET deal_number = (SELECT attribute1 FROM briouser.brio_so_headers_all a WHERE a.header_id = b.header_id) WHERE deal_number IS NULL; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Null Deal_Numbers', 'Updates Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; EXECUTE IMMEDIATE 'TRUNCATE TABLE BRIOUSER.DEFERROLLUPREP DROP STORAGE'; INSERT INTO deferrolluprep (bill_to_customer_name, so_line_number, sob_id, status, trx_number, conc, product_type, product, post_warr, prior_mths, orig_defer_amt, org_name, org_id, ordered_date, order_number, link_to_so_line_no, warr, earnout_start_date, deal_number, currency_code, curr_mths, box_type, header_id, model_link, family, recon_item, update_time, sob_name, account_class, account_description, order_type, sales_channel_code, segment7, segment6, segment5, segment4, segment2, segment1, SOURCE, customer_trx_id, trx_type, invoice_date, shortermmonths, longtermmonths, orig_defer_amt_usd, prior_mths_usd, curr_mths_usd, state, salesrep_id, siebelbrmareaid, brmareaname, siebelreportinglevelid, reportinglevelname, siebeldivisionid, divisionname, siebelsuperregionid, superregionname, siebelregionid, regionname, siebelsuperareaid, superareaname, siebelareaid, areaname, siebeldistrict, districtname, rep_name, divisionmgr, regionmgr, districtmgr, areamgr, st_unearned_rev, st_unearned_usd, lt_unearned_rev, lt_unearned_usd, month1, month2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12, inmeageddebt, brm_item, model, ship_to_customer_name, installat_location_id, sob_rate, user_sort_char_7, curr_qtr_earned_rev, curr_qtr_mths_usd, PERCENT, quotarep) --SIR 21215 SELECT bill_to_customer_name, so_line_number, sob_id, status, trx_number, conc, product_type, product, post_warr, (prior_mths * PERCENT) / 100, (orig_defer_amt * PERCENT) / 100, org_name, org_id, ordered_date, al1.order_number, link_to_so_line_no, warr, earnout_start_date, deal_number, currency_code, (curr_mths * PERCENT) / 100, box_type, al1.header_id, model_link, family, recon_item, update_time, sob_name, account_class, account_description, order_type, sales_channel_code, segment7, segment6, segment5, segment4, segment2, segment1, SOURCE, customer_trx_id, trx_type, invoice_date, shortermmonths, longtermmonths, (orig_defer_amt_usd * PERCENT) / 100, (prior_mths_usd * PERCENT) / 100, (curr_mths_usd * PERCENT) / 100, state, al1.salesrep_id, siebelbrmareaid, brmareaname, siebelreportinglevelid, reportinglevelname, siebeldivisionid, divisionname, siebelsuperregionid, superregionname, siebelregionid, regionname, siebelsuperareaid, superareaname, siebelareaid, areaname, siebeldistrict, districtname, rep_name, divisionmgr, regionmgr, districtmgr, areamgr, (st_unearned_rev * PERCENT) / 100, (st_unearned_usd * PERCENT) / 100, (lt_unearned_rev * PERCENT) / 100, (lt_unearned_usd * PERCENT) / 100, (month1 * PERCENT) / 100, (month2 * PERCENT) / 100, (month3 * PERCENT) / 100, (month4 * PERCENT) / 100, (month5 * PERCENT) / 100, (month6 * PERCENT) / 100, (month7 * PERCENT) / 100, (month8 * PERCENT) / 100, (month9 * PERCENT) / 100, (month10 * PERCENT) / 100, (month11 * PERCENT) / 100, (month12 * PERCENT) / 100, inmeageddebt, brm_item, model, ship_to_customer_name, installat_location_id, sob_rate, user_sort_char_7, (curr_qtr_earned_rev * PERCENT) / 100, (curr_qtr_mths_usd * PERCENT) / 100, PERCENT, DECODE (e.sales_credit_type_id, 1, 'Y', 2, 'N', 'X') --SIR 21215 FROM briouser.deferrollup al1, oe.so_sales_credits e --SIEBEL.SALESPERSON_DETAILS B, --SIEBEL.HIERARCHYROLLUP C, --BRIOUSER.SALESOPS_ENDUSER D WHERE -- AL1.ORDER_NUMBER = D.ORDER_NUMBER (+) al1.account_description = 245116 AND al1.header_id = e.header_id(+); --and E.salesrep_id = B.ORACLEREPID (+) --AND E.CREATION_DATE >= B.STARTDATE (+) --AND E.CREATION_DATE <= B.ENDDATE (+) -- SIR 21215 AND E.SALES_CREDIT_TYPE_ID = 1; --And B.SIEBELAREA= C.BUFFER_ID(+); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Deferrollup_Rep', 'Updates Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; EXECUTE IMMEDIATE 'TRUNCATE TABLE BRIOUSER.DEFERROLLUP_SNAP DROP STORAGE'; COMMIT; INSERT INTO deferrollup_snap SELECT bill_to_customer_name, so_line_number, sob_id, status, trx_number, conc, product_type, product, post_warr, prior_mths, orig_defer_amt, org_name, org_id, ordered_date, order_number, link_to_so_line_no, warr, earnout_start_date, deal_number, currency_code, curr_mths, box_type, header_id, model_link, family, recon_item, update_time, sob_name, account_class, account_description, order_type, sales_channel_code, segment7, segment6, segment5, segment4, segment2, segment1, SOURCE, customer_trx_id, trx_type, invoice_date, shortermmonths, longtermmonths, orig_defer_amt_usd, prior_mths_usd, curr_mths_usd, state, salesrep_id, al1.siebelbrmareaid, al1.brmareaname, al1.siebelreportinglevelid, al1.reportinglevelname, al1.siebeldivisionid, al1.divisionname, al1.siebelsuperregionid, al1.superregionname, al1.siebelregionid, al1.regionname, al1.siebelsuperareaid, al1.superareaname, NVL (b.siebelarea, 'CLEANUP'), al1.areaname, NVL (b.siebeldistrict, 'CLEANUP'), NVL (b.districtname, 'CLEANUP'), NVL (b.repname, 'CLEANUP'), al1.divisionmgr, al1.regionmgr, al1.districtmgr, al1.areamgr, st_unearned_rev, st_unearned_usd, lt_unearned_rev, lt_unearned_usd, month1, month2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12, inmeageddebt, brm_item, model, ship_to_customer_name, installat_location_id, (TO_CHAR (SYSDATE, 'MM') || (TO_CHAR (SYSDATE, 'YYYY'))), sob_rate, user_sort_char_7, PERCENT, curr_qtr_earned_rev, curr_qtr_mths_usd, '', '', quotarep --SIR 21215 FROM briouser.deferrolluprep al1, siebel.salesperson_details b, siebel.hierarchyrollup c WHERE al1.salesrep_id = b.oraclerepid(+) AND al1.invoice_date >= b.startdate(+) AND al1.invoice_date <= b.enddate(+) --AND E.SALES_CREDIT_TYPE_ID = 1 AND b.siebelarea = c.buffer_id(+); COMMIT; UPDATE briouser.deferrollup_snap SET districtname = 'CLEANUP' WHERE districtname IS NULL; COMMIT; UPDATE briouser.deferrollup_snap SET rep_name = 'CLEANUP' WHERE rep_name IS NULL; COMMIT; UPDATE deferrollup_snap SET districtname = '1-1OEPH', siebelbrmareaid = '1-3Y1HD', brmareaname = 'Intl/Corporate BRM Area', reportinglevelname = 'International Corp Adjust Area2-ICADJ', siebelareaid = '1-1OEP5', siebeldistrictid = '1-1OEPH' WHERE (districtname = 'CLEANUP' OR rep_name = 'CLEANUP') AND org_id NOT IN (0, 30, 52, 101, 99); UPDATE deferrollup_snap SET districtname = '1-1OC7Y', siebelbrmareaid = '1-3Y1HG', brmareaname = 'Dom/Corporate BRM Area', reportinglevelname = 'Domestic Corp Adjust Area-DADJ', siebelareaid = '1-1OC7V', siebeldistrictid = '1-1OC7Y' WHERE (districtname = 'CLEANUP' OR rep_name = 'CLEANUP') AND org_id IN (0, 30, 52, 101, 99); COMMIT; UPDATE deferrollup_snap a SET (a.siebeldistrictid, a.rep_name, a.siebelbrmareaid, a.brmareaname, a.siebelreportinglevelid, a.reportinglevelname, a.siebeldivisionid, a.divisionname, a.siebelsuperregionid, a.superregionname, a.siebelregionid, a.regionname, a.siebelsuperareaid, a.superareaname, a.siebelareaid, a.areaname, a.districtname, districtmgr, areamgr, divisionmgr, regionmgr) = (SELECT b.siebeldistrict, b.repname, c.siebelbrmareaid, c.brmareaname, c.siebelreportinglevelid, c.reportinglevelname, c.siebeldivisionid, c.divisionname, c.siebelsuperregionid, c.superregionname, c.siebelregionid, c.regionname, c.siebelsuperareaid, c.superareaname, c.siebelareaid, c.areaname, b.districtname, b.districtmgr, areamgr, divisionmgr, regionmgr FROM siebel.salesperson_details b, siebel.hierarchyrollup c WHERE active IN ('Y', 'L') AND b.oraclerepid(+) = a.salesrep_id AND b.siebelarea = c.buffer_id(+)) WHERE siebeldistrictid NOT IN (SELECT DISTINCT siebeldistrictid FROM siebel.hierarchyrollup) OR districtname = 'CLEANUP' OR rep_name = 'CLEANUP'; COMMIT; -- 05May05 SIRMG00018736 JZ Add current qtr rollup for cs revenue project EXECUTE IMMEDIATE 'TRUNCATE TABLE BRIOUSER.SHIPTO_LOCATION DROP STORAGE'; INSERT INTO briouser.shipto_location SELECT DISTINCT sha.header_id, rc.customer_name, rc.customer_number, rsua.LOCATION --SIR14723 FROM oe.so_headers_all sha, ar.ra_site_uses_all rsua, ar.ra_addresses_all raa, ar.ra_customers rc WHERE sha.ship_to_site_use_id = rsua.site_use_id AND rsua.address_id = raa.address_id AND raa.customer_id = rc.customer_id; COMMIT; UPDATE briouser.customer_dim a SET (a.installat_name, a.installat_location) = (SELECT MAX (customer_name), MAX (shipto_location) FROM briouser.shipto_location b WHERE a.header_id = b.header_id GROUP BY b.header_id) WHERE a.installat_location IS NULL; COMMIT; UPDATE briouser.deferrollup_snap a SET a.installat_location_id = (SELECT MAX (b.installat_location) FROM briouser.customer_dim b WHERE a.header_id = b.header_id(+)); COMMIT; -- 05May05 SIRMG00018736 JZ Add current qtr rollup for cs revenue project UPDATE deferrollup_snap a SET rep_name = (SELECT MAX (repname) FROM siebel.salesperson_details b WHERE a.salesrep_id = b.oraclerepid); COMMIT; -- 21July05 SIRMG22259 JZ add earned_date to deferrollup_snap for revenue report OPEN c_quarterdates; FETCH c_quarterdates INTO v_curr_qtr_end_date; IF SYSDATE > v_curr_qtr_end_date THEN v_earned_date := v_curr_qtr_end_date; ELSE v_earned_date := SYSDATE; END IF; CLOSE c_quarterdates; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Started', 'Update earned_date', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; -- 21July05 SIRMG22259 JZ add earned_date to deferrollup_snap for revenuew report UPDATE deferrollup_snap SET earned_date = v_earned_date WHERE curr_mths_usd IS NOT NULL AND curr_mths_usd != 0 AND curr_qtr_mths_usd IS NOT NULL AND curr_qtr_mths_usd != 0; COMMIT; -- 21July05 SIRMG22259 JZ add earned_date to deferrollup_snap for revenuew report UPDATE deferrollup_snap SET earned_date = v_earned_date WHERE curr_qtr_mths_usd IS NOT NULL AND curr_qtr_mths_usd != 0 AND earned_date IS NULL; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Ended', 'Update earned_date', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'IDW Snap', 'Updates Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; FOR i IN c_get_meageddebt LOOP UPDATE deferrollup SET inmeageddebt = 'Y' WHERE trx_number = i.trx_number AND sob_id = i.set_of_books_id; END LOOP; COMMIT; --DELETE FROM DEFERROLLUP --WHERE INMEAGEDDEBT = 'Y'; --COMMIT; --SIRMG00012855 populate Amount_due_original,Amount_due_remaining,Usd_value --This update only runs for the 1st 7 days of Qtr end SELECT variable_2, variable_3 INTO v_day1, v_day2 FROM control_file WHERE variable_1 = 'DEFERROLLUP'; SELECT (start_date + v_day1), (start_date + v_day2) INTO v_curr_qtr_day1, v_curr_qtr_day7 FROM quarterdates WHERE current_qtr = 'Y'; IF SYSDATE BETWEEN v_curr_qtr_day1 AND v_curr_qtr_day7 THEN SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Starting', 'AR Open Bal Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; FOR get_month_end_rec IN get_month_end LOOP UPDATE briouser.deferrollup b SET ar_amount_due_original = get_month_end_rec.amount_due_original, ar_amount_due_remaining = get_month_end_rec.amount_due_remaining, ar_usd_value = get_month_end_rec.usd_value WHERE trx_number = get_month_end_rec.trx_number; END LOOP; COMMIT; SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'AR Open Bal Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; END IF; --SIRMG00012855 SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, 'Completed', 'Proc Def Rev', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; EXCEPTION WHEN OTHERS THEN err_cde := SQLCODE; err_msg := SUBSTR (SQLERRM, 1, 100); SELECT NVL (MAX (slno), 0) + 1 INTO v_slno FROM briouser.updatelog; INSERT INTO briouser.updatelog VALUES (v_slno, err_msg || 'WARNING!! ERROR(' || err_cde || ')', 'Proc_Deferred_Rev XCPTN HANDLED', SYSDATE, TO_CHAR (SYSDATE, 'hh24:mi:ss')); COMMIT; END; -- Procedure PROC_DEFERRED_REV / GRANT EXECUTE ON briouser.proc_deferred_rev TO nrtops / GRANT EXECUTE ON briouser.proc_deferred_rev TO nrt_readwrite_role / GRANT EXECUTE ON briouser.proc_deferred_rev TO hcl_role /