API code to Create Credit Memo and apply it to invoice

I received a request to post code for creating a credit Memo and apply it to invoice to nullify the balance. Below is the sample code using ar_credit_memo_api_pub.create_request API


SET SERVEROUTPUT ON;

/* Formatted on 7/30/2014 7:04:39 PM (QP5 v5.115.810.9015) */
DECLARE
   -- This script was tested in 11i instance --
   v_return_status          VARCHAR2 (1);
   p_count                  NUMBER;
   v_msg_count              NUMBER;
   v_msg_data               VARCHAR2 (2000);
   v_request_id             NUMBER;
   v_context                VARCHAR2 (2);
   l_cm_lines_tbl           arw_cmreq_cover.cm_line_tbl_type_cover;
   l_customer_trx_id        NUMBER;
   cm_trx_id                NUMBER;
   v_interface_header_rec   arw_cmreq_cover.pq_interface_rec_type;
   ind                      NUMBER;
   l_trx_number             VARCHAR2 (30);

   CURSOR c_inv (p_trx_number varchar2
   )
   IS
      SELECT rct.trx_number,
             rct.customer_trx_id,
             rctl.customer_trx_line_id,
             rctl.quantity_invoiced,
             unit_selling_price
      FROM ra_customer_trx_all rct, ra_customer_trx_lines_all rctl
      WHERE     rct.customer_trx_id = rctl.customer_trx_id
            AND trx_number = p_trx_number
            AND line_type = 'LINE';

   PROCEDURE set_context
   IS
   BEGIN
      DBMS_APPLICATION_INFO.set_client_info (0);
      mo_global.set_policy_context ('S', 0);
   END set_context;
BEGIN
   -- Setting the context ----
   set_context;

   DBMS_OUTPUT.put_line ('Invoking Credit Memo Creation process');

   l_trx_number                 := '20116773';

   FOR lc_inv IN c_inv (l_trx_number)
   LOOP
      ind                 := 1;
      l_customer_trx_id   := lc_inv.customer_trx_id;

      l_cm_lines_tbl (ind).customer_trx_line_id   :=
         lc_inv.customer_trx_line_id;
      l_cm_lines_tbl (ind).quantity_credited   :=
         lc_inv.quantity_invoiced * -1;
      l_cm_lines_tbl (ind).price   :=
         lc_inv.unit_selling_price;
      l_cm_lines_tbl (ind).extended_amount   :=
         lc_inv.quantity_invoiced * lc_inv.unit_selling_price * -1;
   END LOOP;

   ar_credit_memo_api_pub.create_request (          -- standard api parameters
                                          p_api_version => 1.0,
                                          p_init_msg_list => fnd_api.g_true,
                                          p_commit => fnd_api.g_false-- credit memo request parameters
                                          ,
                                          p_customer_trx_id => l_customer_trx_id,
                                          p_line_credit_flag => 'Y',
                                          p_cm_line_tbl => l_cm_lines_tbl,
                                          p_cm_reason_code => 'RETURN',
                                          p_skip_workflow_flag => 'Y',
                                          p_batch_source_name => 'XX_ORDER_ENTRY',
                                          p_interface_attribute_rec => v_interface_header_rec,
                                          p_credit_method_installments => NULL,
                                          p_credit_method_rules => NULL,
                                          x_return_status => v_return_status,
                                          x_msg_count => v_msg_count,
                                          x_msg_data => v_msg_data,
                                          x_request_id => v_request_id
   );
   DBMS_OUTPUT.put_line ('Message count ' || v_msg_count);

   IF v_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('l_msg_data ' || v_msg_data);
   ELSIF v_msg_count > 1
   THEN
      LOOP
         p_count   := p_count + 1;
         v_msg_data   :=
            fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

         IF v_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || v_msg_data);
      END LOOP;
   END IF;

   IF v_return_status <> 'S'
   THEN
      DBMS_OUTPUT.put_line ('Failed');
   ELSE
      SELECT cm_customer_trx_id
      INTO cm_trx_id
      FROM ra_cm_requests_all
      WHERE request_id = v_request_id;

      DBMS_OUTPUT.put_line (' CM trx_id = ' || cm_trx_id);
   -- You can issue a COMMIT; at this point if you want to save the created credit memo to the database
   -- COMMIT;
   END IF;
END;

BI/XML Publisher: Leading and Trailing zeroes truncated for excel reports

Microsoft Excel is too smart and it identifies whether the value in the cell is a Text or number and applies formatting accordingly. This sometimes becomes an issue for us when we are trying to generate an excel report. For example item number 0003463262360 has all the numbers and starts with zero, this when printed in excel report displays it as 3463262360. Hence all the leading zeroes are truncated. Same issue happens when we have decimal and trailing zeroes.

FO formatting options can be used to get away with this problem. Below is the syntax for same.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?ITEM_NUMBER?>
</fo:bidi-override> 

Bom Explosion Query

Below query can be handy to explode and Query BOM Details

 /* Formatted on 7/30/2014 7:01:32 PM (QP5 v5.115.810.9015) */
SELECT DISTINCT
       LPAD (' ', LEVEL * 2) || LEVEL order_level,
       msib.segment1 assembly_item,
       msib.description assembly_description,
       msib.inventory_item_status_code assembly_item_status,
       SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH,
       msib2.segment1 AS component_item,
       msib2.description component_item_description,
       msib2.inventory_item_status_code component_item_status,
       bic.item_num,
       bic.operation_seq_num,
       bic.component_quantity
FROM bom.bom_components_b bic,
     bom.bom_structures_b bom,
     inv.mtl_system_items_b msib,
     inv.mtl_system_items_b msib2,
     mtl_parameters mp
WHERE 1 = 1 AND bic.bill_sequence_id = bom.bill_sequence_id
      AND SYSDATE BETWEEN bic.effectivity_date
                      AND  NVL (bic.disable_date, SYSDATE)
      AND bom.assembly_item_id = msib.inventory_item_id
      AND bom.organization_id = msib.organization_id
      AND bic.component_item_id = msib2.inventory_item_id
      AND bom.organization_id = msib2.organization_id
      AND mp.organization_id = msib.organization_id
      AND mp.organization_code = :p_org_code           /* organization here */
      AND bom.alternate_bom_designator IS NULL
START WITH msib.segment1 = :p_item_number /*  component item to be used here */
CONNECT BY NOCYCLE prioR bic.component_item_id = msib.inventory_item_id
ORDER BY        PATH

Query to List Active Serial Number for an Items

Below query can be used to display active serial number for Item, Organization combination

/* Formatted on 7/30/2014 6:55:59 PM (QP5 v5.115.810.9015) */
SELECT msi.segment1 item_number,
       msn.serial_number,
       msn.current_status_name,
       msn.status_code,
       msn.current_subinventory_code,
       ml.concatenated_segments
FROM mtl_serial_numbers_all_v msn,
     mtl_system_items_b msi,
     mtl_item_locations_kfv ml,
     mtl_parameters mp
WHERE     msi.inventory_item_id = msn.inventory_item_id
      AND mp.organization_code = msn.organization_code
      AND ml.inventory_location_id = msn.current_locator_id
      AND msn.current_status = 3
      AND mp.organization_id = msi.organization_id
      AND mp.organization_code = :org_code
      AND msi.segment1 = :item

Accounting Entries for Order to Cash Cycle (O2C)

A quick summary of accounting entries in Order to Cash cycle

    Sales order creation – No entries
    Pick release:
    Inventory Stage A/c…………………Debit
    Inventory Finished goods a/c……..Credit
    Ship confirm:
    Cogs A/c ……………………………Debit
    Inventory Organization a/c………Credit
    Receviable:
    Receviable A/c………………………Debit
    Revenue A/c………………………Credit
    Tax ………………..…………………Credit
    Freight…………..….……………….Credit
    Cash:
    Cash A/c Dr…………………………Debit
    Receivable A/c……………………….Credit

Accounting Entries in Procure to Pay (P2P) cycle

A quick review on accounting entries in Procure to Pay cycle.

    Purchase Requisition creation: No entry
    Purchase Order creation: No entry
    Inventory Receipt:
    Inventory A/c…………….Debit
    AP Accrual A/C………Credit(This A/c We are giving in Financial Option)
    At the time of Matching the Invoice with Purchase Order
    AP Accrual A/c………….Debit
    Supplier A/c…………..Credit
    At the time of making payment to supplier
    Supplier A/C…………… Debit
    Bank A/c…………….Credit

Query to display BOL, MBOL, Trip details for a Sales Order

/* Formatted on 7/30/2014 6:48:46 PM (QP5 v5.115.810.9015) */
SELECT ooh.order_number,
       wnd.name delivery_name,
       wt.name trip_name,
       ool.line_number,
       ool.ordered_item,
       ool.flow_status_code,
       DECODE (wdd.released_status,
               'R',
               'Ready For Release',
               'B',
               'Back Ordered',
               'S',
               'Released To Warehouse',
               'D',
               'Cancelled',
               'N',
               'Not Ready For Release',
               'Y',
               'Staged or Pick Confirmed',
               'C',
               'Interfaced/Shipped',
               'I',
               'Interfaced/Shipped',
               'O',
               'Not Shipped'
       )
          delivery_status,
       rct.trx_number invoice_number,
       wdd.released_status,
       ood.organization_name || ' (' || ood.organization_code || ')'
          ship_from_org,
       hp_carrier.party_name carrier_name,
       wdi.sequence_number bol_number,
       wds.departure_net_weight ship_weight,
       wds.actual_departure_date ship_date,
       ooh.cust_po_number,
          SUBSTR (hp.party_name, 1, 30)
       || ' '
       || SUBSTR (hl_ship.address1, 1, 36)
       || ' '
       || SUBSTR (hl_ship.address2, 1, 36)
       || ' '
       || SUBSTR (hl_ship.city, 1, 30)
       || ' '
       || SUBSTR (hl_ship.province, 1, 2)
       || ' '
       || SUBSTR (hl_ship.postal_code, 1, 8)
          ship_to_address
FROM apps.hr_locations hl,
     org_organization_definitions ood,
     apps.oe_order_headers_all ooh,
     oe_order_lines_all ool,
     apps.hz_locations hl_ship,
     apps.hz_parties hp,
     apps.hz_party_sites hps,
     apps.hz_cust_acct_sites_all hcas,
     apps.hz_cust_site_uses_all hcsu,
     apps.hz_party_sites hps_bill,
     apps.hz_cust_acct_sites_all hcas_bill,
     apps.hz_cust_site_uses_all hcsu_bill,
     apps.wsh_delivery_details wdd,
     apps.wsh_new_deliveries wnd,
     apps.wsh_delivery_assignments wda,
     apps.wsh_trips wt,
     apps.wsh_delivery_legs wdl,
     apps.wsh_trip_stops wds,
     apps.wsh_document_instances wdi,
     apps.hz_parties hp_carrier,
     ra_customer_trx_all rct
WHERE     1 = 1
      AND ooh.header_id = ool.header_id
      AND ood.organization_id = ool.ship_from_org_id
      AND ooh.ship_from_org_id = hl.inventory_organization_id
      AND hl_ship.location_id = hps.location_id
      AND hp.party_id = hps.party_id
      AND hps.party_site_id = hcas.party_site_id
      AND hps_bill.party_site_id = hcas_bill.party_site_id
      AND hps_bill.party_id = hp.party_id
      AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
      AND hcsu.site_use_id = ooh.ship_to_org_id
      AND hcas_bill.cust_acct_site_id = hcsu_bill.cust_acct_site_id
      AND hcsu_bill.site_use_id = ooh.invoice_to_org_id
      AND ooh.header_id = wdd.source_header_id(+)
      AND wda.delivery_detail_id(+) = wdd.delivery_detail_id
      AND wda.delivery_id = wnd.delivery_id(+)
      AND ool.line_id = wdd.source_line_id
      AND wt.trip_id(+) = wds.trip_id
      AND wds.stop_id(+) = wdl.pick_up_stop_id
      AND wdl.delivery_id(+) = wnd.delivery_id
      AND hp_carrier.party_id(+) = wt.carrier_id
      AND wdi.entity_id(+) = wdl.delivery_leg_id
      AND wdi.entity_name(+) = 'WSH_DELIVERY_LEGS'
      AND TO_CHAR (ooh.order_number) = rct.ct_reference(+)
      AND TO_CHAR (ooh.order_number) = rct.interface_header_attribute1(+)
      AND interface_header_context(+) = 'ORDER ENTRY'
      AND ooh.order_number = :order_number
ORDER BY ool.flow_status_code, ooh.order_number, ool.line_number

To find the Request set and Concurrent Program attached responcibility

--------------------------------------
  Query to find concurrent program:-
--------------------------------------

/* Formatted on 7/30/2014 6:36:53 PM (QP5 v5.115.810.9015) */
SELECT frt.responsibility_name,
       frg.request_group_name,
       frgu.request_unit_type,
       frgu.request_unit_id,
       fcpt.user_concurrent_program_name
FROM fnd_responsibility fr,
     fnd_responsibility_tl frt,
     fnd_request_groups frg,
     fnd_request_group_units frgu,
     fnd_concurrent_programs_tl fcpt
WHERE     frt.responsibility_id = fr.responsibility_id
      AND frg.request_group_id = fr.request_group_id
      AND frgu.request_group_id = frg.request_group_id
      AND fcpt.concurrent_program_id = frgu.request_unit_id
      AND frt.language = USERENV ('LANG')
      AND fcpt.language = USERENV ('LANG')
      AND fcpt.user_concurrent_program_name = :conc_prg_name
ORDER BY 1, 2, 3, 4


----------------------------------
   Query to find Request Set:-
----------------------------------

/* Formatted on 7/30/2014 6:37:22 PM (QP5 v5.115.810.9015) */
SELECT frt.responsibility_name,
       frg.request_group_name,
       frgu.request_unit_type,
       frgu.request_unit_id,
       fcpt.user_request_set_name
FROM apps.fnd_responsibility fr,
     apps.fnd_responsibility_tl frt,
     apps.fnd_request_groups frg,
     apps.fnd_request_group_units frgu,
     apps.fnd_request_sets_tl fcpt
WHERE     frt.responsibility_id = fr.responsibility_id
      AND frg.request_group_id = fr.request_group_id
      AND frgu.request_group_id = frg.request_group_id
      AND fcpt.request_set_id = frgu.request_unit_id
      AND frt.language = USERENV ('LANG')
      AND fcpt.language = USERENV ('LANG')
      AND fcpt.user_request_set_name = :request_set_name
ORDER BY 1, 2, 3, 4

REGISTRATION OF OAF PAGE IN APPS.

Now we are going to learn about how to register the pages in apps.

First create the page in JDeveloper and compile the page.

After compiling the page now we have to move the class files to Java top in server.

Step1:-Move the class file from local machine  to $java top

local machine path:-   <JDeveloper patch directory>/jdevhome/jdev/myclasses  

the class file is located in this folder move that to

Server path:-   /oracle/apps/r12/visr12/apps/apps_st/comn/java/classes

Move the whole folder where your project located.

Here I am using WinSCP tool to move the files.

Step2:- XML import utility

            After moving the files to the server we to import the xml files.

Open the command prompt.

change the directory to:-     C:\dev\oaf\jdevbin\oaext\bin

                                               

after that import the following:-

make the fallowing path in a single line for that in notepad go format uncheck word wrap:-

import C:\dev\oaf\jdevhome\jdev\myprojects\sangu\oracle\apps\po\searchPRJ\webui\SearchPG.xml -rootdir C:\dev\oaf\jdevhome\jdev\myprojects -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsr12.com)(PORT=1521))(CONNECT_DATA=(SID=visr12)))"

NOTE

C:\dev\oaf\jdevhome\jdev\myprojects\sangu\oracle\apps\po\searchPRJ\webui\SearchPG.xml

(This is the path where the oaf pages stores in our local machine)

Give the proper username, password, host name, port name, db name)

From unix server:

Run the below command from the path.

$JAVA_TOP/sangu/oracle/apps/po/searchPRJ/webui

java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/sangu/oracle/apps/po/searchPRJ/webui/SearchPG.xml -rootdir  $JAVA_TOP -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=focusthreadr12.com)(PORT=1521))(CONNECT_DATA=(SID= VISR12)))"

 

Step3:- Login to Oracle Apps

                Switch to Application Developer Responsibility Select Function.

            Application Developer--- > Function

                Create a function

Name:-               (EMP_SEARCH)

            User Function Name:-    (EMP_SEARCH)

After that go to Properties tab and select as

Type:- SSWA jsp function

Next Go to Web HTML tab and give the path of your page

HTML Call:- OA.jsp?page=/sangu/oracle/apps/po/searchPRJ/webui/SearchPG

Save  and exit.

Step4:- Create a menu-attach the function

Application Developer--- > Menu

Provide the following details.

Menu:-  (SANGU_EMP)

            User Menu Name:- (SANGU_EMP)

            Menu Type:- Standard.

Next give :-

                Seq:-

                Prompt:-   (Emp Search)

                Function:- EMP_SEARCH (Give the function Name u have created).

Step5:-Crete the Responsibility and attach the menu.

                System Administrater-- >Security-- >ResponsibilityàDefine.

               

Provide the following

                Responsibility Name:-  (Sangu_OAF)

                Application:-Purchasing.

                Rsponsibility Key :- (SANGU_OAF)

                MENU :- SANGU_EMP (Select the menu that u have created)

               

DataGrup

                Name:-Standered

                Application:- Purchasing.

            

Step6:-Attach the responsibility to user.

            System Administrater-- >Security-- >User -- >Define.

                

This finishes the registration Switch to the responsibility u created .

U will see the page.