Function ot get the on hand quantity by passing item code as parameter

CREATE OR REPLACE FUNCTION xx_get_on_hand_qty (
   m_inventory_item   IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   concatedstring         VARCHAR2 (20000);
   v_item_desc            VARCHAR2 (500);              --Added on 09-Jun-2009

   CURSOR get_on_hand_qty (
      c_segment1   IN   VARCHAR2
   )
   IS
      SELECT   SUM (a.primary_quantity) qty_on_hand,
               b.description,               

           --Added on 09-Jun-2009
               b.primary_uom_code uom_code,
               c.NAME org_name,
               c.organization_id
          FROM mtl_material_transactions a,
               mtl_system_items_b b,
               hr_organization_units c
         WHERE a.organization_id = b.organization_id
           AND a.inventory_item_id = b.inventory_item_id
           AND a.organization_id = c.organization_id
           AND b.organization_id = c.organization_id
           AND b.segment1 = c_segment1
      GROUP BY b.segment1,
               b.primary_uom_code,
               a.inventory_item_id,
               c.NAME,
               c.organization_id,
               b.description                            --Added on 09-Jun-2009
        HAVING SUM (a.primary_quantity) > 0      ---1 --Changed on 09-Jun-2009
      ORDER BY c.organization_id;

   lcu_get_tax_type_rec   get_on_hand_qty%ROWTYPE;
BEGIN
   --Added Below on 09-Jun-2009
   BEGIN
      SELECT DISTINCT description
                 INTO v_item_desc
                 FROM mtl_system_items_b msi
                WHERE msi.segment1 = m_inventory_item;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_item_desc := NULL;
   END;

   --End of Added on 09-Jun-2009
   concatedstring :=
      (   'The ON Hand Quantity for the ITEM : '
       || m_inventory_item
       || ': '
       || v_item_desc
       || ' IS '
       || CHR (10)
       || CHR (10)
      );

   OPEN get_on_hand_qty (m_inventory_item);

   FETCH get_on_hand_qty
    INTO lcu_get_tax_type_rec;

   IF get_on_hand_qty%NOTFOUND
   THEN
      concatedstring :=
         (   'The ON Hand Quantity for the ITEM : '
          || m_inventory_item
          || ' IS : 0'
         );

      CLOSE get_on_hand_qty;
   ELSE
      CLOSE get_on_hand_qty;

      FOR c1 IN get_on_hand_qty (m_inventory_item)
      LOOP
         concatedstring :=
               concatedstring
            || 'In : '
            || c1.org_name
            || ' Quantity Available : '
            || c1.qty_on_hand
            || CHR (10);
         DBMS_OUTPUT.put_line (concatedstring);
      END LOOP;
   END IF;

   concatedstring :=
         concatedstring
      || CHR (10)
      || 'I have checked with above circles and no spare stock is available for stock transfer.'
      || ' Hence this Indent is being raised.';
   --       RETURN (NVL(concatedstring),'Error');
   RETURN (concatedstring);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      concatedstring := 'Everything Failed!!!';
      RETURN (concatedstring);
   WHEN OTHERS
   THEN
      concatedstring :=
         (   'The ON Hand Quantity for the ITEM : '
          || m_inventory_item
          || ' IS : 0'
         );
      RETURN (concatedstring);
END xx_get_on_hand_qty;
/


CREATE OR REPLACE FUNCTION XX_GET_ON_HAND_QTY2(m_inventory_item IN VARCHAR2)
  RETURN VARCHAR2 IS
  concatedstring varchar2(20000);
  CURSOR get_on_hand_qty(c_segment1 IN VARCHAR2) IS
    SELECT SUM(a.primary_quantity) qty_on_hand,
           b.primary_uom_code uom_code,
           c.NAME ORG_NAME,
           c.organization_id
      FROM mtl_material_transactions a,
           mtl_system_items_b        b,
           hr_organization_units     c
     WHERE a.organization_id = b.organization_id
       AND a.inventory_item_id = b.inventory_item_id
       AND a.organization_id = c.organization_id
       AND b.organization_id = c.organization_id
       AND b.SEGMENT1 = c_segment1
     GROUP BY b.segment1,
              b.primary_uom_code,
              a.inventory_item_id,
              c.name,
              c.organization_id
    HAVING SUM(a.primary_quantity) > -1
     ORDER BY c.organization_id;
  lcu_get_tax_type_rec get_on_hand_qty%ROWTYPE;

BEGIN
  concatedstring := ('The ON Hand Quantity for the ITEM : ' ||
                    m_inventory_item || ' IS ' || CHR(10) || CHR(10));

  OPEN get_on_hand_qty(m_inventory_item);

  FETCH get_on_hand_qty
    into lcu_get_tax_type_rec;

  IF get_on_hand_qty%NOTFOUND THEN
 
    concatedstring := ('The ON Hand Quantity for the ITEM : ' ||
                      m_inventory_item || ' IS : 0');
    CLOSE get_on_hand_qty;
 
  ELSE
    CLOSE get_on_hand_qty;
    FOR C1 IN get_on_hand_qty(m_inventory_item) LOOP
      concatedstring := concatedstring || 'In : ' || C1.ORG_NAME ||
                        ' Quantity Available : ' || C1.qty_on_hand ||
                        CHR(10);
      dbms_output.put_line(concatedstring);
    END LOOP;
 
  END IF;
  --       RETURN (NVL(concatedstring),'Error');
  RETURN(concatedstring);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    concatedstring := 'Everything Failed!!!';
    RETURN(concatedstring);
  WHEN OTHERS THEN
    concatedstring := (SUBSTR(SQLCODE, 1, 6) || ': ' ||
                      SUBSTR(SQLERRM, 1, 200) /*'The ON Hand Quantity for the ITEM : ' || m_inventory_item || ' IS : 0'*/
                      );
    dbms_output.put_line(concatedstring);
    RETURN(concatedstring);
END XX_GET_ON_HAND_QTY2;
/


CREATE OR REPLACE FUNCTION xx_get_on_hand_qty4 (
   m_inventory_item   IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   concatedstring         VARCHAR2 (20000);
   concatedstring1         VARCHAR2 (20000);  
   v_item_desc            VARCHAR2 (500);              --Added on 09-Jun-2009

   CURSOR get_on_hand_qty (
      c_segment1   IN   VARCHAR2
   )
   IS     
      select
       sum(transaction_quantity) qty_on_hand,
       mtlb.description,
       mtlb.primary_uom_code uom_code,
       getorg(mtlo.organization_id) org_name,
       mtlo.organization_id
      from
          mtl_onhand_quantities mtlo,
          mtl_system_items_b mtlb
     where
       mtlb.inventory_item_id = mtlo.inventory_item_id
       and mtlb.organization_id = mtlo.organization_id
       and mtlb.segment1 = c_segment1
     group by mtlb.description, mtlb.primary_uom_code, mtlo.organization_id;

   lcu_get_tax_type_rec   get_on_hand_qty%ROWTYPE;
BEGIN

      FOR c1 IN get_on_hand_qty (m_inventory_item)
      LOOP
         concatedstring1 :=
               concatedstring1
            || 'In : '
            || c1.org_name
            || ' Quantity Available : '
            || c1.qty_on_hand
            || CHR (10);
          
         if c1.description is not null then
            v_item_desc := c1.description;
         end if;
         DBMS_OUTPUT.put_line (concatedstring1);
      END LOOP;


   if concatedstring1 is null then
  
   BEGIN
      SELECT DISTINCT description
                 INTO v_item_desc
                 FROM mtl_system_items_b msi
                WHERE msi.segment1 = m_inventory_item;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_item_desc := NULL;
   END;
  
      concatedstring1 :=
      (   'The ON Hand Quantity for the ITEM : '
       || m_inventory_item
       || ': '
       || v_item_desc
       || ' IS '
       || CHR (10)
       || CHR (10)
      );
  
      concatedstring1 := concatedstring1 ||
         (   'The ON Hand Quantity for the ITEM : '
          || m_inventory_item
          || ' IS : 0'
         );
  elsif concatedstring1 is not null then
  
   concatedstring :=
      (   'The ON Hand Quantity for the ITEM : '
       || m_inventory_item
       || ': '
       || v_item_desc
       || ' IS '
       || CHR (10)
       || CHR (10)
      );
 
  --  concatedstring := concatedstring1;
 
  end if;

   concatedstring := concatedstring ||
         concatedstring1
      || CHR (10)
      || 'I have checked with above circles and no spare stock is available for stock transfer.'
      || ' Hence this Indent is being raised.';
   --       RETURN (NVL(concatedstring),'Error');
  
   RETURN (concatedstring);
  
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      concatedstring := 'Everything Failed!!!';
      RETURN (concatedstring);
   WHEN OTHERS
   THEN
      concatedstring :=
         (   'The ON Hand Quantity for the ITEM : '
          || m_inventory_item
          || ' IS : 0'
         );
      RETURN (concatedstring);
END xx_get_on_hand_qty4;
/

Function to get Invoice number,invoice Amount,invoice type lookup code by passing invoice id and org id as parameter

CREATE OR REPLACE FUNCTION getinv (p1 IN NUMBER, p2 NUMBER)
   RETURN VARCHAR
AS
   v_invno   VARCHAR2 (50);
BEGIN
   SELECT invoice_num
     INTO v_invno
     FROM ap_invoices_all a
    WHERE a.invoice_id = p1 AND a.org_id = p2;

   RETURN v_invno;
END;
/

CREATE OR REPLACE FUNCTION getinvamt (p1 IN NUMBER)
   RETURN NUMBER
AS
   v_amt   ap_invoices_all.invoice_

amount%TYPE;
BEGIN
   SELECT invoice_amount
     INTO v_amt
     FROM ap_invoices_all a
    WHERE a.invoice_id = p1;

   RETURN v_amt;
END;
/

CREATE OR REPLACE FUNCTION getinvtype (p1 IN NUMBER)
   RETURN VARCHAR
AS
   v_invno   VARCHAR2 (50);
BEGIN
   SELECT invoice_type_lookup_code
     INTO v_invno
     FROM ap_invoices_all a
    WHERE a.invoice_id = p1;

   RETURN v_invno;
END;
/

Function to get the customer balance by passing customer id,invoice currency code,gl date as parameters in 11.5.10.2

CREATE OR REPLACE function getcusbal(p1 in number,p2 in varchar2,p_date in date)
return number as
v_cus_bal number;
begin
SELECT NVL(SUM(NVL(amount_due_

remaining,0)),0)
       INTO v_cus_bal
      FROM ar_payment_schedules
       WHERE customer_id = p1
       AND invoice_currency_code = P2
       AND gl_date <= p_date
       AND NVL(receipt_confirmed_flag,'Y')='Y';
return v_cus_bal;
end;
/

Function to get the customer name by passing customer id as parameter

CREATE OR REPLACE FUNCTION getcustomer(p1 IN NUMBER) RETURN VARCHAR2 AS
v_customer VARCHAR2(100);
BEGIN
SELECT DISTINCT customer_name INTO v_vendor FROM ra_customers
WHERE customer_id=p1;
RETURN v_customer;
END;
/

Function to get BankName by passing check id as parameter in 11.5.10.2

CREATE OR REPLACE FUNCTION getbank(cheque_id IN NUMBER) RETURN VARCHAR2 IS
m_bank_name VARCHAR2(250);
BEGIN

    SELECT abb.bank_name
      INTO m_bank_name
      FROM ap_checks_all aca, ap_bank_accounts_all abaa, ap_bank_branches abb
     WHERE aca.BANK_ACCOUNT_ID = abaa.BANK_ACCOUNT_ID
       AND abaa.BANK_BRANCH_ID = abb.BANK_BRANCH_ID
       AND aca.check_id = cheque_id;
   
    RETURN m_bank_name;
EXCEPTION
    WHEN others THEN
         RETURN NULL;

END;
/

Function to get Employee full name by passing person id as parameter

CREATE OR REPLACE FUNCTION getemp (p1 IN NUMBER)
   RETURN VARCHAR2
AS
   v_emp   VARCHAR2 (50);
BEGIN
   SELECT full_name
     INTO v_emp
     FROM per_people_f
    WHERE person_id = p1
      AND current_employee_flag = 'Y'
      AND effective_end_date > SYSDATE;

   RETURN v_emp;
END;
/

Function to get Fixed Assets Location by passing location id as parameter

CREATE OR REPLACE FUNCTION getFAloc(p1 IN NUMBER) RETURN VARCHAR2 AS
  v_loc VARCHAR2(500);
BEGIN
  SELECT A.SEGMENT1 || '.' || A.SEGMENT2 || '.' || A.SEGMENT3 || '.' ||
         A.SEGMENT4 || '.' || A.SEGMENT5
    INTO v_loc
    FROM fa_locations A
   WHERE location_id = p1;
  RETURN v_loc;
END;
/

Function to get the final approver of the Purchase order(PO) by passing po_header_id as parameter

CREATE OR REPLACE FUNCTION getfinalapprover(p_header_id IN number) RETURN varchar2 AS
x_final_approver varchar2(1000);
BEGIN
SELECT   po_employees_sv.get_emp_name (pah.employee_id) final_approver
 INTO    x_final_approver
               FROM po_action_history pah
              WHERE object_type_code = 'PO'
                AND action_code = 'APPROVE'
                AND ROWID IN (
                       SELECT   MAX (ROWID)
                           FROM po_action_history
                          WHERE action_code = 'APPROVE'
                            AND object_type_code = 'PO'
                       GROUP BY object_id)
                and object_id = p_header_id;
RETURN x_final_approver;
EXCEPTION
WHEN no_data_found THEN
RETURN NULL;
WHEN others THEN
RETURN NULL;
END;
/

Function to get the employee grade by passing person id and effective date

CREATE OR REPLACE FUNCTION getgrade (pid IN NUMBER, effdate IN DATE) RETURN VARCHAR2 IS

m_grade VARCHAR2(10);

BEGIN

    SELECT pg.NAME
      INTO m_grade
      FROM per_grades pg, per_all_assignments_f ppf
     WHERE ppf.GRADE_ID = pg.GRADE_ID
       AND ppf.person_id = pid
       AND TRUNC(effdate) BETWEEN TRUNC(ppf.EFFECTIVE_START_

DATE) AND TRUNC(ppf.EFFECTIVE_END_DATE);
--       AND ppf.ROWID IN (SELECT MAX(ROWID) FROM per_all_assignments_f GROUP BY person_id);
      
    RETURN m_grade;
   
EXCEPTION
    WHEN others THEN
         RETURN NULL;          

END;
/

Function to get item type by passing inventory item id as parameter

CREATE OR REPLACE FUNCTION Getitemtype(p1 IN NUMBER) RETURN VARCHAR2 AS
v_item_type VARCHAR2(30);
BEGIN
SELECT DISTINCT item_type INTO v_item_type FROM mtl_system_items
WHERE inventory_item_id=p1;
RETURN v_item_type;
END;
/

Function to get the item po description by passing po_header_id and po_line_id as parameters

CREATE OR REPLACE FUNCTION Getitempodesc(p1 IN NUMBER, p2 IN NUMBER) RETURN VARCHAR2 AS

v_tax VARCHAR2(240);

BEGIN

SELECT B.ITEM_DESCRIPTION  INTO v_tax FROM Po_headers_all A,

Po_lines_all B

WHERE A.PO_HEADER_ID=B.PO_HEADER_ID

AND A.PO_HEADER_ID=P1

AND B.PO_LINE_ID=P2;

RETURN(v_tax);



EXCEPTION



WHEN NO_DATA_FOUND THEN

RETURN (NULL);

END;
/

Function to get the Item description by passing parameters as inventory item id and organziation id

CREATE OR REPLACE FUNCTION getitemdesc(p1 in number, p2 in number) return varchar2 as
v_item_desc varchar2(240);
begin
select distinct description into v_item_desc from mtl_system_items
where inventory_item_id=p1 and organization_id = p2;
return v_item_desc;
end;
/

Function to get the Item description by passing parameter as inventory item id

CREATE OR REPLACE FUNCTION getitemd(p1 in number) return varchar2 as
v_item_desc varchar2(240);
begin
select distinct description into v_item_desc from mtl_system_items
where inventory_item_id=p1;
return v_item_desc;
end;
/

Function to get the Item_code by passing the inventory item id

CREATE OR REPLACE FUNCTION getitem(p1 in number) return varchar2 as
v_item_code varchar2(30);
begin
select distinct segment1 into v_item_code from mtl_system_items
where inventory_item_id=p1;
return v_item_code;
end;
/

Function to get the location from hr_location table

CREATE OR REPLACE function getloc(p1 in number) return varchar2 as

v_loc varchar2(50);

begin

select location_code into v_loc from hr_locations

where location_id=p1;

return v_loc;

end;
/

Function to Get PO Order Qty

CREATE OR REPLACE function getpoordqty(p1 in number) return number as
  v_qty number;
begin
  SELECT NVL(SUM(b.QUANTITY), 0)
    into v_qty
    FROM rcv_shipment_lines a, po_line_locations b
   WHERE a.PO_LINE_LOCATION_ID = b.LINE_LOCATION_ID
     AND a.SHIPMENT_LINE_ID = p1;
  return v_qty;
exception
  when no_data_found then
    return null;
end;
/

Sql Query to List all the responsibilities,application and organization attached to a User

SELECT fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date,
       fu.email_address, fa.application_name, fr.responsibility_id,
       fa.application_id, hou.NAME
  FROM fnd_profile_option_values fpov,
       fnd_profile_options fpo,
       hr_operating_units hou,
       fnd_responsibility_tl fr,
       fnd_application_tl fa,
       fnd_user_resp_groups_direct furg,
       fnd_user fu
 WHERE fpov.profile_option_id = fpo.profile_option_id
   AND fpov.application_id = fpo.application_id
   AND fpo.profile_option_name = 'ORG_ID'
   AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
   AND fpov.level_value = fr.responsibility_id
   AND fa.application_id = fr.application_id
   AND fpov.level_id = 10003
   AND fpov.level_value = fr.responsibility_id
   AND furg.user_id = fu.user_id
   AND furg.responsibility_id = fr.responsibility_id
   AND furg.start_date <= NVL(:todate,sysdate)

Sql query retrieves the Flex fields details

  SELECT   A.ID_FLEX_STRUCTURE_CODE,
                   B.ID_FLEX_CODE,
                   E.LANGUAGE,
                   D.FLEX_VALUE,
                   E.DESCRIPTION,
                   E.FLEX_VALUE_MEANING,
                   B.FLEX_VALUE_SET_ID,
                   B.APPLICATION_COLUMN_NAME,
                   B.SEGMENT_NAME,
                   C.FLEX_VALUE_SET_NAME,
                   D.FLEX_VALUE_ID                
        FROM     APPS.fnd_id_flex_structures A,
                   APPS.fnd_id_flex_segments B,
                   APPS.fnd_flex_value_sets C,
                   APPS.fnd_flex_values D,     
                   APPS.fnd_flex_values_tl E      
        WHERE  A.ID_FLEX_NUM=B.ID_FLEX_NUM
         --AND   A.ID_FLEX_CODE='GL#'
     --AND   B.ID_FLEX_CODE='GL#'
         AND   B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID
         AND   C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID
         AND   D.FLEX_VALUE_ID=E.FLEX_VALUE_ID
         AND   A.APPLICATION_ID IN (101,201)
         AND   E.LANGUAGE='US'
         ORDER BY A.ID_FLEX_STRUCTURE_CODE

Sql query retrieves processidstatusin concurrent Manager

 Select distinct Concurrent_Process_Id CpId, PID Opid,
      Os_Process_ID Osid,
      Q.Concurrent_Queue_Name Manager,
      P.process_status_code Status,
      To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
      from  Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
      where  Q.Application_Id = Queue_Application_ID
      And (Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID)
      And ( Spid = Os_Process_ID )
      And  Process_Status_Code not in ('K','S')
      Order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name ;

Sql query to display Concurrent program names or requests which are submitted in submit request

SELECT  FCR.request_id Request#,
 SUBSTR(TO_CHAR(FCR.actual_start_date,'DD-MON-RR HH24:MI'),1,15) StartDate,
 SUBSTR(TO_CHAR(FCR.actual_completion_date,'DD-MON-RR HH24:MI'),1,15) EndDATE,
 substr((FCR.requested_by||'-'||FU.user_name),1,20) UserName,
 decode(FCP.concurrent_program_name,'Report Set', substr(FCR.description,1,30),
   substr(FCP.concurrent_program_name,1,30)) Program_Name,
 FCR.phase_code||'-'||substr(FL1.meaning,1,10) Phase,
 FCR.status_code||'-'||substr(FL2.meaning,1,10) Status,
 FCR.hold_flag Hold
FROM  fnd_concurrent_requests FCR,
 fnd_concurrent_programs FCP,
 fnd_lookups FL1,
 fnd_lookups FL2,
 fnd_user FU
WHERE FCR.phase_code = FL1.lookup_code
AND FCR.status_code = FL2.lookup_code
AND FCR.requested_by=FU.user_id
AND FCR.program_application_id = FCP.application_id
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FL1.lookup_type = 'CP_PHASE_CODE'
AND FL2.lookup_type = 'CP_STATUS_CODE'
AND TRUNC(FCR.requested_start_date) = '&start_date' -- Concurrent Program Start Date
ORDER BY FCR.request_id,
 FCR.requested_start_date,
 FCR.phase_code,
 FCR.status_code

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