get number from text

 regexp_replace(SOURCE, '[^0-9]', '') DD ,LTRIM(SOURCE,'Transport Task '), SUBSTR(SOURCE,15,20) TASK_ID

TP

 SELECT

  t.contract AS "Depot Code"

, NVL(ifsapp.company_site_api.get_description(t.contract),'Total') AS "Depot name"

, t.current_month_supply_tp "Current Month Supply (TP) Performance"                     -- dom amount

, t.current_month_supply_tp_vat "Current Month Supply (TP+Vat)"                 --dom amount -discount- vat (DOM + VAT)

, t.collection_against_current_month_supply "Collection against current month supply"     --this month invoice collection (cash + credit)

, t.collection_up_to_previous_month_credit "Collection upto Previous Month Credit"      --

, t.total_collection "Total Collection" --

, t.today_total_collection "Today Total Collection"                      -- total collection

, t.today_advance_coll_amount "Today Advance coll Balance"

FROM

(

SELECT

  cs.contract

, SUM(NVL(sa_value.total_tp_minus_discount,0)) current_month_supply_tp  -- dom amount

, SUM(NVL(sa_value.total_tp_minus_discount,0)) + SUM(NVL(sa_value.vat_dom_amount,0)) current_month_supply_tp_vat                 --dom amount -discount- vat (DOM + VAT)

, SUM(NVL(cur_month_coll.cur_month_coll_amount,0)) collection_against_current_month_supply     --this month invoice collection (cash + credit)

, SUM(NVL(pre_month_coll.coll_upto_pre_mont_amount,0)) collection_up_to_previous_month_credit      --

, SUM(NVL(cur_month_coll.cur_month_coll_amount,0)) + SUM(NVL(pre_month_coll.coll_upto_pre_mont_amount,0)) total_collection

, SUM(NVL(cur_month_coll.today_coll_amount,0)) today_total_collection                     -- total collection

, SUM(NVL(cur_month_coll.today_adv_coll_amount,0)) today_advance_coll_amount

FROM ifsapp.company_site cs LEFT JOIN

(

select coiij.contract,

                 sum(nvl (coiij.vat_dom_amount, 0)) as vat_dom_amount,

--                 sum(nvl (coiij.sale_unit_price, 0) * nvl (coiij.invoiced_qty, 0) tp,

--                 suM(nvl (coiij.sale_unit_price, 0) * nvl (coiij.invoiced_qty, 0) * nvl (coiij.discount,0)/100 ) Discount_amount,

                 sum(nvl (coiij.sale_unit_price, 0) * nvl (coiij.invoiced_qty, 0) - 

                 nvl (coiij.sale_unit_price, 0) * nvl (coiij.invoiced_qty, 0) * nvl (coiij.discount,0)/100  ) total_tp_minus_discount

            from customer_order_inv_item_join coiij

           where trunc(coiij.invoice_date) between to_date('&From_Date','dd/mm/yyyy') and to_date('&To_Date','dd/mm/yyyy') 

           AND coiij.contract BETWEEN '1003' AND '1029'

              and ifsapp.customer_order_api.get_order_id (coiij.order_no) != 'BE'     

--             and coiij.contract = '1015'

             group by coiij.contract

) sa_value ON cs.contract = sa_value.contract

    LEFT JOIN

(

-- current month

SELECT cpa.contract,

       SUM (cpa.pay_amount) AS cur_month_coll_amount,

       SUM (cpa.totay_pay_amount) AS today_coll_amount,

       SUM (cpa.cupoa_pay_amount) AS today_adv_coll_amount

FROM (SELECT ledger_item_id,

             ledger_item_series_id,

             SUBSTR (ifsapp.payment_per_currency_api.get_short_name ( ltcq.company, ltcq.series_id, ltcq.payment_id,ltcq.currency), 1, 4) contract,

             CASE WHEN ltcq.ledger_item_series_id IN ('CD', 'EX') THEN

                  NVL(DECODE ( is_new_ledger_item, 'FALSE', paid_amount - NVL (boe_discount_fee, 0), paid_amount), 0)

             END pay_amount,

             

             CASE WHEN ltcq.ledger_item_series_id IN ('CD', 'EX') AND TRUNC (ltcq.pay_date) = TO_DATE ('&To_Date', 'dd/mm/yyyy') THEN

                  NVL(DECODE ( is_new_ledger_item, 'FALSE', paid_amount - NVL (boe_discount_fee, 0), paid_amount), 0)

             END TOTAY_PAY_AMOUNT,

             

             CASE WHEN ltcq.ledger_item_series_id IN ('CUPOA') AND TRUNC (ltcq.pay_date) = TO_DATE ('&To_Date', 'dd/mm/yyyy') THEN

                  NVL ( DECODE ( is_new_ledger_item, 'FALSE', paid_amount - NVL (boe_discount_fee, 0), paid_amount), 0) - 

                  (ifsapp.x_egp_customer_offset (ledger_item_series_id, ledger_item_id, TO_DATE ('&To_Date', 'dd/mm/yyyy')))

             END cupoa_pay_amount

            FROM ifsapp.ledger_transaction_cu_qry ltcq

           WHERE     ltcq.party_type_db = 'CUSTOMER'

                 AND ltcq.payment_type_code_db = 'CUSTPAY'

                 AND ltcq.rolledback = 'FALSE'

                 AND ltcq.correction = 'FALSE'

                 AND ltcq.ledger_item_series_id IN ('CD', 'EX', 'CUPOA')

                 AND TRUNC (ltcq.pay_date) BETWEEN TO_DATE ('&From_Date', 'dd/mm/yyyy') AND TO_DATE ('&To_Date', 'dd/mm/yyyy')

--                 AND ifsapp.payment_per_currency_api.get_short_name (

--                        ltcq.company,

--                        ltcq.series_id,

--                        ltcq.payment_id,

--                        ltcq.currency) = '101402'

                        

                        ) cpa

         LEFT JOIN

         (SELECT coihua.series_id,

                 coihua.invoice_no,

                 coihua.invoice_date,

                 coihua.invoice_id,

                 coihua.contract

            FROM ifsapp.cust_order_inv_head_uiv_all coihua

           WHERE     coihua.party_type = 'CUSTOMER'

                 AND TRUNC (coihua.invoice_date) BETWEEN TO_DATE ('&From_Date', 'dd/mm/yyyy') AND TO_DATE ('&To_Date', 'dd/mm/yyyy')

--                 AND coihua.contract = '1014'

          UNION ALL

          SELECT mci.series_id,

                 mci.invoice_no,

                 mci.invoice_date,

                 mci.invoice_id,

                 ifsapp.cust_ord_customer_api.get_c_default_site (mci.identity) contract

            FROM man_cust_invoice mci

           WHERE     mci.party_type_db = 'CUSTOMER'

                 AND mci.series_id = 'EX'

--                 AND ifsapp.cust_ord_customer_api.get_c_default_site (

--                        mci.identity) = '1014'

                 AND TRUNC (mci.invoice_date) BETWEEN TO_DATE ('&From_Date', 'dd/mm/yyyy') AND TO_DATE ('&To_Date', 'dd/mm/yyyy')

) inv_sale

ON  inv_sale.invoice_no = cpa.ledger_item_id

AND inv_sale.series_id = cpa.ledger_item_series_id

GROUP BY cpa.contract

) cur_month_coll ON cs.contract = cur_month_coll.contract

LEFT JOIN

(

-- previous month

SELECT cpa.contract,

       SUM (cpa.pay_amount) AS coll_upto_pre_mont_amount

FROM (SELECT ledger_item_id,

             ledger_item_series_id,

             SUBSTR (ifsapp.payment_per_currency_api.get_short_name ( ltcq.company, ltcq.series_id, ltcq.payment_id,ltcq.currency), 1, 4) contract,

             CASE WHEN ltcq.ledger_item_series_id IN ('CD', 'EX') THEN

                  NVL(DECODE ( is_new_ledger_item, 'FALSE', paid_amount - NVL (boe_discount_fee, 0), paid_amount), 0)

             END pay_amount

             

            

            FROM ifsapp.ledger_transaction_cu_qry ltcq

           WHERE     ltcq.party_type_db = 'CUSTOMER'

                 AND ltcq.payment_type_code_db = 'CUSTPAY'

                 AND ltcq.rolledback = 'FALSE'

                 AND ltcq.correction = 'FALSE'

                 AND ltcq.ledger_item_series_id IN ('CD', 'EX')

                 AND TRUNC (ltcq.pay_date) BETWEEN TO_DATE ('&From_Date', 'dd/mm/yyyy') AND TO_DATE ('&To_Date', 'dd/mm/yyyy')

--                 AND ifsapp.payment_per_currency_api.get_short_name (

--                        ltcq.company,

--                        ltcq.series_id,

--                        ltcq.payment_id,

--                        ltcq.currency) = '101402'

                        

                        ) cpa

         LEFT JOIN

         (SELECT coihua.series_id,

                 coihua.invoice_no,

                 coihua.invoice_date,

                 coihua.invoice_id,

                 coihua.contract

            FROM ifsapp.cust_order_inv_head_uiv_all coihua

           WHERE     coihua.party_type = 'CUSTOMER'

                 AND TRUNC (coihua.invoice_date) <= LAST_DAY (ADD_MONTHS (to_date('&From_Date','dd/mm/yyyy'), -1))

--                 AND coihua.contract = '1014'

          UNION ALL

          SELECT mci.series_id,

                 mci.invoice_no,

                 mci.invoice_date,

                 mci.invoice_id,

                 ifsapp.cust_ord_customer_api.get_c_default_site (mci.identity) contract

            FROM man_cust_invoice mci

           WHERE     mci.party_type_db = 'CUSTOMER'

                 AND mci.series_id = 'EX'

--                 AND ifsapp.cust_ord_customer_api.get_c_default_site (

--                        mci.identity) = '1014'

                 AND TRUNC (mci.invoice_date) <= LAST_DAY (ADD_MONTHS (to_date('&From_Date','dd/mm/yyyy'), -1))

) inv_sale

ON  inv_sale.invoice_no = cpa.ledger_item_id

AND inv_sale.series_id = cpa.ledger_item_series_id

GROUP BY cpa.contract

) pre_month_coll ON cs.contract = pre_month_coll.contract

WHERE cs.contract BETWEEN '1003' AND '1029'

                      

GROUP BY ROLLUP(cs.contract)

) t

Conversion crore and Lac in Oracle Sql

 CREATE OR REPLACE FUNCTION number_to_core_lac(p_number IN NUMBER) RETURN VARCHAR2 IS

  l_crores NUMBER := TRUNC(p_number / 10000000);

  l_lacs   NUMBER := TRUNC(MOD(p_number, 10000000) / 100000);

BEGIN

  RETURN CASE 

           WHEN l_crores > 0 AND l_lacs > 0 THEN l_crores || ' Core, ' || l_lacs || ' Lac'

           WHEN l_crores > 0 THEN l_crores || ' Core'

           WHEN l_lacs > 0 THEN l_lacs || ' Lac'

           ELSE '0 Lac'

         END;

END;

/

SELECT number_to_core_lac(99994050000) AS "55 Core, 44 Lac" FROM DUAL;

Date Format in ifs

 DECLARE

    a_ VARCHAR2(32000) := NULL;

    default_date_ DATE := TO_DATE('&FROM_DATE', 'YYYY-MM-DD-HH24-MI-SS');  -- Assuming &FROM_DATE is a date

    extracted_dd VARCHAR2(2) := TO_NUMBER(TO_CHAR(default_date_, 'DD'));

BEGIN

    dbms_output.put_line(extracted_dd);

END;