Function to get the Receivables application apply date

CREATE OR REPLACE FUNCTION xxxxxx_get_apply_date (
   p_payment_schedule_id   IN   NUMBER,
   p_org_id            IN   NUMBER,
   p_from_date         IN   DATE,
   p_to_date           IN   DATE
)
   RETURN VARCHAR2
IS
   CURSOR c1
   IS 
   SELECT app.apply_date apply_date                                -- Q1
      FROM   ra_customer_trx_all ct
            ,ra_customer_trx_lines_all ctl
            ,ra_cust_trx_types_all ctt
            ,ar_receipt_methods rm
            ,ar_cash_receipts_all cr
            ,ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_cash_receipt_history_all crh
      WHERE  app.status = 'APP'
      AND    app.cash_receipt_id = cr.cash_receipt_id(+)
      AND    cr.receipt_method_id = rm.receipt_method_id(+)
      --AND    app.payment_schedule_id = ps.payment_schedule_id
      AND    ps.cust_trx_type_id = ctt.cust_trx_type_id(+)
      AND    app.applied_customer_trx_id = ct.customer_trx_id(+)
      AND    app.applied_customer_trx_line_id = ctl.customer_trx_line_id(+)
      AND    cr.cash_receipt_id = crh.cash_receipt_id(+)
      AND    crh.current_record_flag(+) = 'Y'
      --AND    ps.payment_schedule_id = :p_payment_schedule_id
      --AND    ct.customer_trx_id = :p_customer_trx_id
      --AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS',crh.status) IS NULL  -- RECEIPT_STATE ***
      AND    ct.org_id = p_org_id
      AND    ps.payment_schedule_id = app.payment_schedule_id
      AND    app.payment_schedule_id IN (
                SELECT app1.payment_schedule_id
                FROM   ar_receivable_applications_all app1
                WHERE  app1.receivable_application_id =
                                                 app.receivable_application_id
                AND    app1.applied_payment_schedule_id =
                                                         p_payment_schedule_id)
      AND    app.gl_date <= p_to_date                             
      UNION ALL
      SELECT app.apply_date apply_date                              -- Q1_1
      FROM   ra_customer_trx_all ct
            ,ra_customer_trx_lines_all ctl
            ,ra_cust_trx_types_all ctt
            ,ar_receipt_methods rm
            ,ar_cash_receipts_all cr
            ,ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_cash_receipt_history_all crh
      WHERE  app.status = 'APP'
      AND    app.cash_receipt_id = cr.cash_receipt_id(+)
      AND    cr.receipt_method_id = rm.receipt_method_id(+)
      --AND    app.payment_schedule_id = ps.payment_schedule_id
      AND    ps.cust_trx_type_id = ctt.cust_trx_type_id(+)
      AND    app.applied_customer_trx_id = ct.customer_trx_id(+)
      AND    app.applied_customer_trx_line_id = ctl.customer_trx_line_id(+)
      AND    cr.cash_receipt_id = crh.cash_receipt_id(+)
      AND    crh.current_record_flag(+) = 'Y'
      --AND    ps.payment_schedule_id = :p_payment_schedule_id
      --AND    ct.customer_trx_id = :p_customer_trx_id
      AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS'
                                                   ,crh.status) IS NULL
      -- RECEIPT_STATE ***
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    ct.org_id = p_org_id
      AND    app.applied_payment_schedule_id IN (
                SELECT app1.applied_payment_schedule_id
                FROM   ar_receivable_applications_all app1
                WHERE  app1.receivable_application_id =
                                                 app.receivable_application_id
                AND    app1.payment_schedule_id = p_payment_schedule_id)
      AND    app.gl_date <= p_to_date
      UNION ALL
      SELECT app.apply_date apply_date                                     -- Q3
      FROM   ar_payment_schedules_all ps
            ,ar_payment_schedules_all psa
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all art
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.applied_payment_schedule_id = psa.payment_schedule_id
      AND    app.status IN ('ACC', 'OTHER ACC')
      AND    art.receivables_trx_id(+) = app.receivables_trx_id
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    ps.org_id = p_org_id
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    app.gl_date <= p_to_date    
      UNION ALL
      SELECT adj.apply_date apply_date                                    -- Q4
      FROM   ar_adjustments_all adj
            ,ar_receivables_trx_all rt
            ,ra_customer_trx_all ct
      WHERE  adj.status NOT IN ('R', 'U')
      AND    adj.receivables_trx_id = rt.receivables_trx_id(+)
      AND    adj.customer_trx_id = ct.customer_trx_id(+)
      AND    ct.org_id = p_org_id
      AND    adj.payment_schedule_id = p_payment_schedule_id
      /*AND    arpt_sql_func_util.get_lookup_meaning ('APPROVAL_TYPE'
                                                   ,adj.status) IS NULL*/
      AND    adj.gl_date <= p_to_date
      UNION ALL
      SELECT app.apply_date apply_date                                         -- Q5
      FROM   ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all rt
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.receivables_trx_id = rt.receivables_trx_id
      AND    app.status = 'ACTIVITY'
      AND    SIGN (app.applied_payment_schedule_id) < 0
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    ps.org_id = p_org_id
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    app.gl_date <= p_to_date
      UNION ALL
      SELECT app.apply_date apply_date                         -- Q6
      FROM   ar_payment_schedules_all ps
            ,ar_payment_schedules_all ps_dummy
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all rt
            ,ar_cash_receipt_history_all crh
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.applied_payment_schedule_id = ps_dummy.payment_schedule_id
      AND    ps_dummy.cash_receipt_id = crh.cash_receipt_id
      AND    crh.current_record_flag = 'Y'
      AND    app.receivables_trx_id = rt.receivables_trx_id
      AND    app.status = 'ACTIVITY'
      AND    SIGN (app.applied_payment_schedule_id) > 0
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS'
                                                   ,crh.status) IS NULL
      AND    ps.org_id = p_org_id
      AND    app.gl_date <= p_to_date;
    
  v_apply_date   VARCHAR2(2000) := NULL;
BEGIN
   FOR i IN c1
   LOOP
      v_apply_date := v_apply_date || ',' || to_char(i.apply_date,'DD-MON-YY');
   END LOOP;

   RETURN ltrim(v_apply_date,',');
END;
/

Function to get the Transaction Number

CREATE OR REPLACE FUNCTION xxxxxx_get_trx_number (
   p_payment_schedule_id   IN   NUMBER
  ,
   --p_inv_amt           IN   NUMBER,
   p_org_id                IN   NUMBER
  ,p_from_date             IN   DATE
  ,p_to_date               IN   DATE
)
   RETURN VARCHAR2
IS
   CURSOR c1
   IS
      SELECT ps.trx_number trx_rec_number                                -- Q1
      FROM   ra_customer_trx_all ct
            ,ra_customer_trx_lines_all ctl
            ,ra_cust_trx_types_all ctt
            ,ar_receipt_methods rm
            ,ar_cash_receipts_all cr
            ,ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_cash_receipt_history_all crh
      WHERE  app.status = 'APP'
      AND    app.cash_receipt_id = cr.cash_receipt_id(+)
      AND    cr.receipt_method_id = rm.receipt_method_id(+)
      --AND    app.payment_schedule_id = ps.payment_schedule_id
      AND    ps.cust_trx_type_id = ctt.cust_trx_type_id(+)
      AND    app.applied_customer_trx_id = ct.customer_trx_id(+)
      AND    app.applied_customer_trx_line_id = ctl.customer_trx_line_id(+)
      AND    cr.cash_receipt_id = crh.cash_receipt_id(+)
      AND    crh.current_record_flag(+) = 'Y'
      --AND    ps.payment_schedule_id = :p_payment_schedule_id
      --AND    ct.customer_trx_id = :p_customer_trx_id
      --AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS',crh.status) IS NULL  -- RECEIPT_STATE ***
      AND    ct.org_id = p_org_id
      AND    ps.payment_schedule_id = app.payment_schedule_id
      AND    app.payment_schedule_id IN (
                SELECT app1.payment_schedule_id
                FROM   ar_receivable_applications_all app1
                WHERE  app1.receivable_application_id =
                                                 app.receivable_application_id
                AND    app1.applied_payment_schedule_id =
                                                         p_payment_schedule_id)
      AND    app.gl_date <= p_to_date
      UNION ALL
      SELECT ps.trx_number trx_rec_number                              -- Q1_1
      FROM   ra_customer_trx_all ct
            ,ra_customer_trx_lines_all ctl
            ,ra_cust_trx_types_all ctt
            ,ar_receipt_methods rm
            ,ar_cash_receipts_all cr
            ,ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_cash_receipt_history_all crh
      WHERE  app.status = 'APP'
      AND    app.cash_receipt_id = cr.cash_receipt_id(+)
      AND    cr.receipt_method_id = rm.receipt_method_id(+)
      --AND    app.payment_schedule_id = ps.payment_schedule_id
      AND    ps.cust_trx_type_id = ctt.cust_trx_type_id(+)
      AND    app.applied_customer_trx_id = ct.customer_trx_id(+)
      AND    app.applied_customer_trx_line_id = ctl.customer_trx_line_id(+)
      AND    cr.cash_receipt_id = crh.cash_receipt_id(+)
      AND    crh.current_record_flag(+) = 'Y'
      --AND    ps.payment_schedule_id = :p_payment_schedule_id
      --AND    ct.customer_trx_id = :p_customer_trx_id
      AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS'
                                                   ,crh.status) IS NULL
      -- RECEIPT_STATE ***
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    ct.org_id = p_org_id
      AND    app.applied_payment_schedule_id IN (
                SELECT app1.applied_payment_schedule_id
                FROM   ar_receivable_applications_all app1
                WHERE  app1.receivable_application_id =
                                                 app.receivable_application_id
                AND    app1.payment_schedule_id = p_payment_schedule_id)
      AND    app.gl_date <= p_to_date
      UNION ALL
      /*SELECT DECODE (SIGN (ps.payment_schedule_id)
                    ,-1, arpt_sql_func_util.get_lookup_meaning
                                                              ('PAYMENT_TYPE'
                                                              ,app.status)
                    ,ps.trx_number
                    ) trx_rec_number                                     -- Q2
      FROM   ra_customer_trx_all ct
            ,ra_customer_trx_lines_all rtl
            ,ra_cust_trx_types_all ctt
            ,ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
      WHERE  app.applied_payment_schedule_id = ps.payment_schedule_id
      AND    ps.cust_trx_type_id = ctt.cust_trx_type_id(+)
      AND    app.applied_customer_trx_line_id = rtl.customer_trx_line_id(+)
      AND    app.status NOT IN ('ACC', 'ACTIVITY', 'OTHER ACC')
      AND    app.applied_customer_trx_id = ct.customer_trx_id(+)
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    ct.org_id = p_org_id
      AND    app.gl_date <= p_to_date
      UNION ALL*/
      SELECT DECODE (SIGN (ps.payment_schedule_id)
                    ,-1, arpt_sql_func_util.get_lookup_meaning
                                                              ('PAYMENT_TYPE'
                                                              ,app.status)
                    ,NULL
                    ) trx_rec_number                                     -- Q3
      FROM   ar_payment_schedules_all ps
            ,ar_payment_schedules_all psa
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all art
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.applied_payment_schedule_id = psa.payment_schedule_id
      AND    app.status IN ('ACC', 'OTHER ACC')
      AND    art.receivables_trx_id(+) = app.receivables_trx_id
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    ps.org_id = p_org_id
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    app.gl_date <= p_to_date
      UNION ALL
      SELECT DECODE (adj.receivables_trx_id
                    ,-15, (SELECT trx.trx_number
                           FROM   ra_customer_trx trx
                                 ,ra_customer_trx_lines lines
                           WHERE  trx.customer_trx_id = lines.customer_trx_id
                           AND    lines.br_ref_customer_trx_id =
                                                            ct.customer_trx_id
                           AND    lines.br_adjustment_id = adj.adjustment_id)
                    ,adj.adjustment_number
                    ) trx_rec_number                                     -- Q4
      FROM   ar_adjustments_all adj
            ,ar_receivables_trx_all rt
            ,ra_customer_trx_all ct
      WHERE  adj.status NOT IN ('R', 'U')
      AND    adj.receivables_trx_id = rt.receivables_trx_id(+)
      AND    adj.customer_trx_id = ct.customer_trx_id(+)
      AND    ct.org_id = p_org_id
      AND    adj.payment_schedule_id = p_payment_schedule_id
      /*AND    arpt_sql_func_util.get_lookup_meaning ('APPROVAL_TYPE'
                                                   ,adj.status) IS NULL*/
      AND    adj.gl_date <= p_to_date
      UNION ALL
      SELECT NULL trx_rec_number                                         -- Q5
      FROM   ar_payment_schedules_all ps
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all rt
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.receivables_trx_id = rt.receivables_trx_id
      AND    app.status = 'ACTIVITY'
      AND    SIGN (app.applied_payment_schedule_id) < 0
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    ps.org_id = p_org_id
      AND    ps.payment_schedule_id = app.applied_payment_schedule_id
      AND    app.gl_date <= p_to_date
      UNION ALL
      SELECT ps_dummy.trx_number trx_rec_number                          -- Q6
      FROM   ar_payment_schedules_all ps
            ,ar_payment_schedules_all ps_dummy
            ,ar_receivable_applications_all app
            ,ar_receivables_trx_all rt
            ,ar_cash_receipt_history_all crh
      WHERE  app.payment_schedule_id = ps.payment_schedule_id
      AND    app.applied_payment_schedule_id = ps_dummy.payment_schedule_id
      AND    ps_dummy.cash_receipt_id = crh.cash_receipt_id
      AND    crh.current_record_flag = 'Y'
      AND    app.receivables_trx_id = rt.receivables_trx_id
      AND    app.status = 'ACTIVITY'
      AND    SIGN (app.applied_payment_schedule_id) > 0
      AND    ps.payment_schedule_id = p_payment_schedule_id
      AND    arpt_sql_func_util.get_lookup_meaning ('RECEIPT_CREATION_STATUS'
                                                   ,crh.status) IS NULL
      AND    ps.org_id = p_org_id
      AND    app.gl_date <= p_to_date;

   v_trx_num   VARCHAR2 (2000) := NULL;
BEGIN
   FOR i IN c1
   LOOP
      v_trx_num := v_trx_num || ', ' || i.trx_rec_number;
   END LOOP;

   RETURN LTRIM (v_trx_num, ', ');
END;
/

Lock box loading process

1. Receivables>Interfaces>Lockbox

 

we will select the control file path in the Transmission Name

select 

New Transmission                   and  Tramission Name (from the list of values in which the data file name contains)

Submit Import

Data File  -  ar/11.5.0/bin/AR_XXXXBABU_Hdfc_kljuhyt.csv_05-05-2011160812.csv

Control File - XXXCUST.ctl

Tramission Format  - XXX_LB_8INV (used in XYZ)

And select Submit PostQuickCash   and submit then request id is created with this the data will moves to interface table and base (seeded) tables

If you not select Submit PostQuickCash then the data will be stored in the interface table only.

Then Process Lockboxes is submitted.

Receivable>setup>Receipts>Lockboxes> Transmission Formats

Here we will define the format which need to be used.

XXX_LB_8INV – this format is used in the XXX Proj.

 Click on Transmission Fields