To generate a PDF using JavaScript in Oracle APEX from a collection

 To generate a PDF using JavaScript in Oracle APEX from a collection, you can follow these steps:

1. Create a button or link on your APEX page to trigger the PDF generation.
2. Add a dynamic action to the button or link to execute JavaScript code.
3. In the JavaScript code, retrieve the data from the collection and format it as needed.
4. Use a JavaScript library like jsPDF or pdfmake to generate the PDF document.
5. Download the generated PDF document.

Here's an example of how you can achieve this:

1. Create a button or link on your APEX page:
  - Button Name: "Generate PDF"
  - Button Action: "Defined by Dynamic Action"

2. Add a dynamic action to the button or link:
  - Event: "Click"
  - Action: "Execute JavaScript Code"

3. In the JavaScript code, retrieve the data from the collection and format it:
```javascript
// Retrieve data from the collection
var collectionData = apex.collection.get('YOUR_COLLECTION_NAME');

// Format the data as needed
var formattedData = '';
for (var i = 0; i < collectionData.length; i++) {
 formattedData += collectionData[i].column1 + ' - ' + collectionData[i].column2 + '\n';
}
```

4. Use a JavaScript library like jsPDF or pdfmake to generate the PDF document:
  - Include the library in your APEX application (e.g., by uploading the library file to the Static Application Files).
  - Use the library's API to create and format the PDF document based on the formatted data.

Here's an example using jsPDF:
```javascript
// Generate the PDF document
var doc = new jsPDF();
doc.text(formattedData, 10, 10);

// Save the PDF document
doc.save('generated_pdf.pdf');
```

5. Download the generated PDF document:
  - The `doc.save('generated_pdf.pdf')` line in the above code will automatically download the PDF document with the specified filename.

Make sure to replace `'YOUR_COLLECTION_NAME'` with the actual name of your collection, and adjust the formatting and PDF generation code as per your requirements.

Note: Ensure that you have the necessary permissions and libraries set up in your Oracle APEX environment to execute JavaScript code and generate PDF documents.

How to call a REST API with PL/SQL

Answer:

Here are the general steps on how to call a REST API using PL/SQL:

1. Register the REST API: First thing you must do is to let Oracle know that a REST service exists and is available. You can define your REST service with Oracle using the APEX_WEB_SERVICE package.

2. Create a HTTP request to call the REST API: Once the REST API is registered in Oracle, you can use the UTL_HTTP package to create a HTTP request to call the REST API.

Here's a simple example of what code might look like:

DECLARE
l_url VARCHAR2(500);
l_response CLOB;
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
BEGIN
--Define API url
l_url := 'http://<api_url>';

-- Make a HTTP request to the REST API
l_http_request := UTL_HTTP.begin_request(l_url, 'POST', 'HTTP/1.1');

-- Set Header parameters
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/x-www-form-urlencoded');

-- Send Request and get Response
l_http_response := UTL_HTTP.get_response(l_http_request);

-- Get the JSON response
UTL_HTTP.read_text(l_http_response, l_response);

UTL_HTTP.end_response(l_http_response);
EXCEPTION
WHEN utl_http.end_of_body THEN
dbms_output.put_line('Response:'||l_response);
WHEN OTHERS THEN
dbms_output.put_line('Error in http request:'||SQLERRM);
END;
/


Disclaimer: You need to replace '<api_url>' with your actual REST API URL.
Also, error handling and security considerations like SSL certificate, handling sensitive information etc., should be properly implemented.

Keep in mind that network ACLs have to be properly configured so PL/SQL can reach network resources. Also, don't forget to handle any potential exceptions.

Please note, in Oracle 19c and above you can also use the DBMS_CLOUD package to send HTTP requests, which can simplify the code significantly if you are working in the Oracle Cloud.

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