Problem with date parameters in IOS

0

im having trouble when running my personal report, the problem persists on the date, can you help me with that?

this is my report and it does work on jasperserver and directly on ireport preview but not on mobile, and i reviewed the parameters, and the one that gives me trouble is the DATE one

the available parameters are
p_org_id (integer)
p_item_id (integer)
p_cust_id (integer)
p_fecha_ini (date)
p_fecha_fin (date)

Input Controls
p_fecha_ini (single value, datatype:date)
p_fecha_fin (single value, datatype:date)
p_org_id (single value, datatype:number)

SQL:

SELECT fac.*, 0 n_cred, cargo.n_cargo
    FROM (  SELECT sold_from_org_id organization_id,
                   (SELECT name
                      FROM hr.hr_all_organization_units
                     WHERE organization_id = $P{p_org_id})
                      name,
                   sold_to_org_id num_cliente,
                   account_number cta_cliente,
                   party_name cliente,
                   ROUND (SUM (cajas), 2) cjs,
                   ROUND (SUM (kilos), 2) kg,
                   ROUND (SUM (importe), 2) importe,
                   ROUND (
                      SUM (importe) / DECODE (SUM (kilos), 0, 0.001, SUM (kilos)), --aquí agregamos esta linea decode para evitar divsión por 0, no jaló de otra forma
                      2)
                      precio_unit_prom
              FROM (  SELECT oola.sold_from_org_id,
                             oola.sold_to_org_id,
                             account_number,
                             party_name,
                             SUM (apps.xxcalv_ventas_fnc_pub (
                                     'PIEZAS_A_KILOS',
                                     p_segment1   => msib.segment1,
                                     p_trx_qty    => mtln.transaction_quantity,
                                     p_item_id    => mmt.inventory_item_id)
                                  * -1)
                                kilos,
                             SUM (apps.xxcalv_ventas_fnc_pub (
                                     'A_CAJAS_360',
                                     p_segment1     => msib.segment1,
                                     p_trx_qty      => mtln.transaction_quantity,
                                     p_trx_qty2     => mtln.secondary_transaction_quantity,
                                     p_grade_code   => mtln.grade_code,
                                     p_item_id      => mmt.inventory_item_id)
                                  * -1)
                                cajas,
                             ROUND (
                                SUM (
                                     rctla.unit_selling_price
                                   * mtln.transaction_quantity
                                   * -1),
                                2)
                                importe
                        FROM inv.mtl_material_transactions mmt,
                             ont.oe_order_lines_all oola,
                             ont.oe_order_headers_all ooha,
                             inv.mtl_system_items_b msib,
                             inv.mtl_transaction_lot_numbers mtln,
                             ar.ra_customer_trx_all rcta,
                             ar.hz_parties party,
                             ar.ra_customer_trx_lines_all rctla,
                             ar.hz_cust_accounts cust_acct
                       WHERE     1 = 1
                             AND rcta.customer_trx_id = rctla.customer_trx_id
                             AND oola.line_id = rctla.interface_line_attribute6
                             AND oola.sold_to_org_id = cust_acct.cust_account_id(+)
                             AND cust_acct.party_id = party.party_id(+)

                             AND rcta.org_id = $P{p_org_id}
                             AND mmt.trx_source_line_id = oola.line_id
                             AND oola.header_id = ooha.header_id
                             AND transaction_type_id IN (33, 15)
                             AND msib.inventory_item_id =
                                    NVL ($P{p_item_id}, msib.inventory_item_id)
                             AND mmt.organization_id =
                                    TO_NUMBER (
                                       apps.xxcalv_ventas_fnc_pub (
                                          'INV_ORG_ID',
                                          p_org_id   => $P{p_org_id}))
                             AND oola.sold_to_org_id =
                                    DECODE ($P{p_cust_id},
                                            9999, oola.sold_to_org_id,
                                            NULL, oola.sold_to_org_id,
                                            $P{p_cust_id})
                             AND msib.inventory_item_id = mmt.inventory_item_id
                             AND mtln.transaction_id = mmt.transaction_id
                             AND msib.organization_id = mmt.organization_id
                             AND TRUNC (ooha.ordered_date) BETWEEN NVL (
                                                                      TRUNC (
                                                                         TO_DATE (
                                                                            $P{p_fecha_ini},
                                                                            'DD/MM/RRRR HH24:MI:SS')),
                                                                      TRUNC (
                                                                         ooha.ordered_date))
                                                               AND NVL (
                                                                      TRUNC (
                                                                         TO_DATE (
                                                                            $P{p_fecha_fin},
                                                                            'DD/MM/RRRR HH24:MI:SS')),
                                                                      TRUNC (
                                                                         ooha.ordered_date))
                             AND interface_header_attribute10 = mmt.organization_id
                              AND rcta.interface_header_attribute1 = order_number(+)  --5jan13 Cambiamos esta linea al final de la consulta para que evite las referencias no numericas de la segunda carga en AR
                    GROUP BY account_number,
                             party_name,
                             trx_number,
                             oola.sold_from_org_id,
                             oola.sold_to_org_id
                    UNION ALL
                      SELECT oola.sold_from_org_id,
                             oola.sold_to_org_id,
                             account_number,
                             party_name,
                             ROUND (SUM (apps.xxcalv_ventas_fnc_pub (
                                            'PIEZAS_A_KILOS',
                                            p_segment1   => msib.segment1,
                                            p_trx_qty    => mtln.transaction_quantity,
                                            p_item_id    => mmt.inventory_item_id)
                                         * -1),
                                    2)
                                kilos,
                             ROUND (SUM (apps.xxcalv_ventas_fnc_pub (
                                            'A_CAJAS_360',
                                            p_segment1     => msib.segment1,
                                            p_trx_qty      => mtln.transaction_quantity,
                                            p_trx_qty2     => mtln.secondary_transaction_quantity,
                                            p_grade_code   => mtln.grade_code,
                                            p_item_id      => mmt.inventory_item_id)
                                         * -1),
                                    2)
                                cajas,
                             SUM (
                                apps.xxcalv_ventas_fnc_pub (
                                   'PRECIO_UNIT_FIX',
                                   p_precio    => oola.unit_selling_price,
                                   p_line_id   => oola.line_id)
                                * mtln.transaction_quantity
                                * -1)
                                importe
                        FROM inv.mtl_material_transactions mmt,
                             ont.oe_order_lines_all oola,
                             ont.oe_order_headers_all ooha,
                             inv.mtl_system_items_b msib,
                             inv.mtl_transaction_lot_numbers mtln,
                             ar.hz_parties party,
                             ar.hz_cust_accounts cust_acct
                       WHERE     1 = 1
                             AND oola.sold_to_org_id = cust_acct.cust_account_id(+)
                             AND cust_acct.party_id = party.party_id(+)
                             AND mmt.trx_source_line_id = oola.line_id
                             AND oola.header_id = ooha.header_id
                             AND transaction_type_id IN (54)
                             AND msib.inventory_item_id =
                                    NVL ($P{p_item_id}, msib.inventory_item_id)
                             AND mmt.organization_id =
                                    TO_NUMBER (
                                       apps.xxcalv_ventas_fnc_pub (
                                          'INV_ORG_ID',
                                          p_org_id   => $P{p_org_id}))
                             AND oola.sold_to_org_id =
                                    DECODE ($P{p_cust_id},
                                            9999, oola.sold_to_org_id,
                                            NULL, oola.sold_to_org_id,
                                            $P{p_cust_id})
                             AND msib.inventory_item_id = mmt.inventory_item_id
                             AND mtln.transaction_id = mmt.transaction_id
                             AND msib.organization_id = mmt.organization_id
                             AND TRUNC (ooha.ordered_date) BETWEEN NVL (
                                                                      TRUNC (
                                                                         TO_DATE (
                                                                            $P{p_fecha_ini},
                                                                            'DD/MM/RRRR HH24:MI:SS')),
                                                                      TRUNC (
                                                                         ooha.ordered_date))
                                                               AND NVL (
                                                                      TRUNC (
                                                                         TO_DATE (
                                                                            $P{p_fecha_fin},
                                                                            'DD/MM/RRRR HH24:MI:SS')),
                                                                      TRUNC (
                                                                         ooha.ordered_date))
                    GROUP BY account_number,
                             party_name,
                             oola.sold_from_org_id,
                             oola.sold_to_org_id
                    ORDER BY account_number)
          GROUP BY account_number,
                   party_name,
                   sold_from_org_id,
                   sold_to_org_id) fac,
         (  SELECT sold_to_customer_id cliente_id,
                   ROUND (SUM (revenue_amount), 2) n_cargo
              FROM (  SELECT trx.customer_trx_id,
                             trxl.customer_trx_line_id,
                             trxl.revenue_amount,
                             trxl.creation_date,
                             trx.sold_to_customer_id,
                             party.party_name,
                             trxl.description,
                             trxl.quantity_credited,
                             trx.trx_number,
                             trx.trx_date
                        FROM ar.ra_customer_trx_all trx,
                             ar.ra_customer_trx_lines_all trxl,
                             ar.hz_parties party,
                             ar.hz_cust_accounts cust_acct
                       WHERE     1 = 1
                             AND trx.cust_trx_type_id = 1031
                             AND trxl.customer_trx_id = trx.customer_trx_id
                             AND revenue_amount IS NOT NULL
                             AND cust_acct.cust_account_id =
                                    trx.sold_to_customer_id
                             AND party.party_id = cust_acct.party_id
                             AND trx.sold_to_customer_id =
                                    DECODE ($P{p_cust_id},
                                            9999, trx.sold_to_customer_id,
                                            NULL, trx.sold_to_customer_id,
                                            $P{p_cust_id})
                             AND TRUNC (trx.trx_date) BETWEEN NVL (
                                                                 TRUNC (
                                                                    TO_DATE (
                                                                       $P{p_fecha_ini},
                                                                       'DD/MM/RRRR HH24:MI:SS')),
                                                                 TRUNC (
                                                                    trx.trx_date))
                                                          AND NVL (
                                                                 TRUNC (
                                                                    TO_DATE (
                                                                       $P{p_fecha_fin},
                                                                       'DD/MM/RRRR HH24:MI:SS')),
                                                                 TRUNC (
                                                                    trx.trx_date))
                    ORDER BY trx.trx_date)
          GROUP BY sold_to_customer_id, party_name) cargo
   WHERE 1 = 1 AND fac.num_cliente = cargo.cliente_id(+)
ORDER BY cliente

 
fernandezmanzur's picture
Joined: Jan 21 2013 - 2:15pm
Last seen: 6 years 7 months ago

2 Answers:

0

Hi Fernandez,

Since you resolve your parameter values from dates, make a simple report with dummy query like "select 1" and test the values returned by your parameters. Check the Date Format configuration in the Administration Guide.

Regards,

Olga

oesina's picture
225
Joined: Jun 20 2011 - 10:27am
Last seen: 6 years 1 month ago
0

Thanks Olga, but still doesn't work; ive tried so many conversions with the unix timestamp wich is the time that works on iOS; but gain, in the preview it shows the date and also the seconds if i convert it backwards; but when i enter the app jaspermobile, it shows or either an error or null... i can't figure out how to do that...

 

in the dummy i tried select to_char( to_date('01011970','ddmmyyyy') + 1/24/60/60 * $P{p_fecha_ini}, 'dd-mon-yyyy hh24:mi:ss') from dual

or also select to_char( to_date('01011970','ddmmyyyy') +  $P{p_fecha_ini} * 86400 from dual and so many others

its courious because if i run it just like the first one i showed, it works fine, just lacks from all the info because of the date... i.ex. i get 563,000 instead of 572,000

 

thanks, best regards!

fernandezmanzur's picture
Joined: Jan 21 2013 - 2:15pm
Last seen: 6 years 7 months ago
Feedback
randomness