Wednesday, May 30, 2012

Passing parameters to xml publisher reports from concurrent manager not working

Ensure that below steps are in place and followed.

  1. Make sure that the parameter TOKENS defined in concurrent program request are in CAPS.If defined in small or init case then XML publisher will not recognize parameters. All parameters in Data definition template should also be defined in CAPS.



  2. XML Data definition has following statement under properties tag

         <property name="include_parameters" value="true"/>

  3. Parameters sequence in data definition file should match with concurrent program parameter sequences.

    Submit concurrent program FND_REQUEST.SUBMIT_REQUEST along with XML Publisher layout and printer options

    FND_REQUEST.SUBMIT_REQUEST submits concurrent request to be processed by a concurrent manager.

    Using submit_request will only submits the program and will not attach any layout or print option. Code below will help to set XML publisher template/layout along with print option.

    *****Add_layout and Add_printer procedures are optional in calling submit_request. Use only if you need to set them.

    Layout is submitted to a concurrent request using below procedure

    fnd_request.add_layout (
                        template_appl_name   => 'Template Application',
                        template_code        => 'Template Code',
                        template_language    => 'en', --Use language from template definition
                        template_territory   => 'US', --Use territory from template definition
                        output_format        => 'PDF' --Use output format from template definition

                         );


    Setting printer while submitting concurrent program

    fnd_submit.set_print_options (printer      => lc_printer_name
                                       ,style        => 'PDF Publisher'
                                       ,copies       => 1
                                       );


    fnd_request.add_printer (
                        printer => printer_name,
                        copies  => 1);




    DECLARE
       lc_boolean        BOOLEAN;
       ln_request_id     NUMBER;
       lc_printer_name   VARCHAR2 (100);
       lc_boolean1       BOOLEAN;
       lc_boolean2       BOOLEAN;
    BEGIN

          -- Initialize Apps 
          fnd_global.apps_initialize (>USER_ID<
                                     ,>RESP_ID<
                                     ,>RESP_APPL_ID<
                                     );
       -- Set printer options
       lc_boolean :=
          fnd_submit.set_print_options (printer      => lc_printer_name
                                       ,style        => 'PDF Publisher'
                                       ,copies       => 1
                                       );
       --Add printer

       lc_boolean1 :=
                    fnd_request.add_printer (printer      => lc_printer_name
                                             ,copies       => 1);
      --Set Layout

      lc_boolean2 :=
                   fnd_request.add_layout (
                                template_appl_name   => 'Template Application',
                                template_code        => 'Template Code',
                                template_language    => 'en', --Use language from template definition
                                template_territory   => 'US', --Use territory from template definition
                                output_format        => 'PDF' --Use output format from template definition
                                        );
       ln_request_id :=
          fnd_request.submit_request ('FND',                -- application
                                      'COCN_PGM_SHORT_NAME',-- program short name
                                      '',                   -- description
                                      '',                   -- start time
                                      FALSE,                -- sub request
                                      'Argument1',          -- argument1
                                      'Argument2',          -- argument2
                                      'N',                  -- argument3
                                      NULL,                 -- argument4
                                      NULL,                 -- argument5
                                      'Argument6',          -- argument6
                                      CHR (0)               -- represents end of arguments
                                     );
       COMMIT;

       IF ln_request_id = 0
       THEN
          dbms.output.put_line ('Concurrent request failed to submit');
       END IF;
    END;



    Handling Date formats and passing date to concurrent program

    Below note is only applicable if value set FND_STANDARD_DATE is used in oracle apps concurrent program.

    Date parameters are passed from concurrent programs to subroutines in the format of  YYYY/MM/DD HH:MM:SS (eg: 2012/01/31 00:00:00). Date from concurrent program cannot be used directly in the programs. To handle date input from concurrent program, it has to be accepted in variable of VARCHAR2 and  convert to date format using the expression

               TO_DATE (SUBSTR (<date_variable>, 1, 10), 'yyyy/mm/dd')
                                                                   OR
              select FND_DATE.CANONICAL_TO_DATE(<date_variable>) from dual;

    Passing date to fnd_request.submit_request

    Date can only be passed to fnd_request.submit_request in the format of  YYYY/MM/DD HH:MM:SS (eg: 2012/01/31 00:00:00). Any other format will lead to error or invalid date conversion. Once accepted from concurrent program date will be converted to the format of 'DD-MON-YY'. It should be converted before passing to submit_request using the expression

    to_char(to_date(<date_variable>,'DD-MON-YY'),'yyyy/mm/dd')||' 00:00:00'

    FND_ATTACHMENT Details Oracle Apps


    SELECT   fndattdoc.pk1_value
            ,st.short_text
            ,fdlt.long_text
            ,pk1_value hdr_attach_pk
            ,fnddoc.datatype_name hdr_attach_dtype
            ,fndattfn.function_name
            ,fndattdoc.entity_name
        FROM fnd_attachment_functions fndattfn
            ,fnd_doc_category_usages fndcatusg
            ,fnd_documents_vl fnddoc
            ,fnd_attached_documents fndattdoc
            ,fnd_documents_short_text st
            ,fnd_documents_long_text fdlt
       WHERE fndattfn.attachment_function_id = fndcatusg.attachment_function_id
         AND fndcatusg.category_id = fnddoc.category_id
         AND fnddoc.document_id = fndattdoc.document_id
         AND fndattfn.function_name =<Func. Name>
         AND fndattdoc.entity_name =<Entity Name>
         AND fnddoc.category_description = <Category Name>

         AND fnddoc.media_id = st.media_id
         AND fnddoc.media_id = fdlt.media_id
    ORDER BY fndattdoc.seq_num


    Tuesday, May 29, 2012

    Customer information Queries



    Ship To Customer Address
    Use below query to fetch ship_to address for a Order or Invoice
    For Order join hcsu.site_use_id = order.ship_to_org_id
    For Invoice join  hcsu.site_use_id = invoice.ship_to_site_use_id
           SELECT hcsu.site_use_id
                ,hcsu.location
                ,hcas.cust_acct_site_id "address_id"
                ,hps.party_site_number
                , hl.address1 || '  ' || hl.address2
                ,hl.city
                ,hl.state
                ,hl.country
                ,hl.postal_code
                ,hcsu.bill_to_site_use_id
                ,hcsu.contact_id
            FROM hz_cust_accounts_all hca
                ,hz_cust_acct_sites hcas
                ,hz_cust_site_uses hcsu
                ,hz_party_sites hps
                ,hz_locations hl
           WHERE 1 = 1
             AND hca.cust_account_id = hcas.cust_account_id
             AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
             AND hps.location_id = hl.location_id
             AND hps.party_site_id = hcas.party_site_id
              --AND hcsu.primary_flag = 'Y'
             AND hcsu.status = 'A'
             AND hcsu.site_use_code = 'SHIP_TO'
             AND hca.cust_account_id = <customer_id>
             AND hcsu.org_id =<org_id>;

    Bill To Customer Address
    Use below query to fetch bill_to address for a Order or Invoice
    For Order join hcsu.site_use_id = order.invoice_to_org_id
    For Invoice join  hcsu.site_use_id = invoice.bill_to_site_use_id
           SELECT hcsu.site_use_id
                ,hcsu.location
                ,hcas.cust_acct_site_id "address_id"
                ,hps.party_site_number
                ,hl.address1 || '  ' || hl.address2
                ,hl.city
                ,hl.state
                ,hl.country
                ,hl.postal_code
                ,hcsu.contact_id
            FROM hz_cust_accounts_all hca
                ,hz_cust_acct_sites hcas
                ,hz_cust_site_uses hcsu
                ,hz_party_sites hps
                ,hz_locations hl
           WHERE 1 = 1
             AND hca.cust_account_id = hcas.cust_account_id
             AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
             AND hps.location_id = hl.location_id
             AND hps.party_site_id = hcas.party_site_id
             AND hcsu.primary_flag = 'Y'
             AND hcsu.status = 'A'
             AND hcsu.site_use_code = 'BILL_TO'
             AND hca.cust_account_id = <customer_id>
             AND hcsu.org_id =<org_id>;

    Cusomer Contact's by Party/SiteUses & Customer Contact information
    Use below query to get the customer contact by party/site uses.
    You can use the same query to get contacts contact type by joining with  hz_contact_points hcp
    using the conditions hcp.owner_table_name = 'HZ_PARTIES' and hcp.owner_table_id = hcar.party_id (from below query)

    SELECT  hcar.cust_account_id
        , hcar.cust_acct_site_id
        , hcar.party_id rel_party_id
        , hr.object_id org_party_id
        , hr.subject_id person_party_id
        , hr.relationship_type
        , hp.party_name contact_name
        FROM hz_cust_account_roles hcar
        , hz_relationships hr
        , hz_parties hp
        WHERE 1                   =1
            AND hcar.role_type       = 'CONTACT'
            AND hcar.party_id        = hr.party_id
            AND hr.relationship_code = 'CONTACT_OF'
            AND hr.subject_id        = hp.party_id
            AND hcar.cust_account_id = <customer_id>
     Above query with hz_contact_points join to get site uses contact details
    SELECT  hcar.cust_account_id
        , hcar.cust_acct_site_id
        , hcar.party_id rel_party_id
        , hr.object_id org_party_id
        , hr.subject_id person_party_id
        , hr.relationship_type
        , hp.party_name contact_name
        , hcp.email_address
        FROM hz_cust_account_roles hcar
        , hz_relationships hr
        , hz_parties hp
        , hz_contact_points hcp
        WHERE 1                   =1
            AND hcar.role_type       = 'CONTACT'
            AND hcar.party_id        = hr.party_id
            AND hr.relationship_code = 'CONTACT_OF'
            AND hr.subject_id        = hp.party_id
            AND hcp.owner_table_id = hcar.party_id
            --AND hcp.contact_point_type = 'EMAIL'
            AND hcar.cust_account_id = <customer_id>
            and hcar.cust_acct_site_id = (select cust_acct_site_id from
            hz_cust_site_uses_all where site_use_id = <site_use_id>);


    Customer Contact Phone/Fax/Email
    Customer contact can be defined at party or party site level.

    Contact details from Party level. Modify PHONE_LINE_TYPE to FAX or EMAIL to get related data.
           SELECT hcp.raw_phone_number
              FROM hz_contact_points hcp, hz_relationships hr, hz_parties hzp
             WHERE hcp.owner_table_id = hr.party_id
               AND hr.object_id = nvl(&party_id,hr.object_id)
               AND hr.status = 'A'
               AND hcp.status = 'A'
               AND hcp.primary_flag = 'Y'
               AND hzp.party_id = hr.party_id
               AND hcp.phone_line_type = 'GEN'
               AND hcp.owner_table_name = 'HZ_PARTIES'

    Contact details from Party Site level.
             SELECT hcp.raw_phone_number
               FROM hz_contact_points hcp
              WHERE hcp.owner_table_id = nvl(&party_site_id,hcp.owner_table_id)
                AND phone_line_type = 'GEN'
                AND primary_flag = 'Y'
                AND owner_table_name = 'HZ_PARTY_SITES';

    Customer Profile
    Customer
       SELECT description
         FROM ar_customer_profiles_v a, ra_terms b
        WHERE customer_id = nvl(&customer_id,customer_id)
          AND status = 'A'
          AND site_use_id IS NULL
          AND a.standard_terms = b.term_id;

    Customer Site
             SELECT description
               FROM ar_customer_profiles_v a, ra_terms b
              WHERE customer_id = nvl(&customer_id,customer_id)
                AND status = 'A'
                AND site_use_id = nvl(&bill_site_use_id,site_use_id)
                AND a.standard_terms = b.term_id;

    Customer Credit Limits
    Customer
       SELECT nvl(overall_credit_limit,0)
                     ,nvl(trx_credit_limit,0)
         FROM hz_cust_profile_amts
        WHERE cust_account_id = nvl(&customer_id,customer_id)
          --AND status = 'A'
          AND currency_code = &CURRENCY_CODE -- For Multi Org
          AND site_use_id IS NULL;

    Customer Site
             SELECT nvl(overall_credit_limit,0)
                   ,nvl(trx_credit_limit,0)
               FROM hz_cust_profile_amts
              WHERE cust_account_id = nvl(&customer_id,customer_id)
                --AND status = 'A'
                AND site_use_id =  nvl(&bill_site_use_id,site_use_id);


    Friday, May 18, 2012

    Available to Transact/Reserve quantity API

    Api  inv_quantity_tree_pub.query_quantities can be used in oracle applications to find Available to transact and reserve quantity. You can even find the available quantity by lots / subinventory.

    Finding quantities by lots can be only achieved by setting the p_is_lot_control flag to TRUE along with lot_number else set it to FALSE.


    set serveroutput on
    DECLARE
       x_return_status         VARCHAR2 (50);
       x_msg_count             VARCHAR2 (50);
       x_msg_data              VARCHAR2 (50);
       l_item_id               NUMBER;
       l_organization_id       NUMBER;
       l_qty_on_hand           NUMBER;
       l_res_qty_on_hand       NUMBER;
       l_avail_to_tnsct        NUMBER;
       l_avail_to_reserve      NUMBER;
       l_qty_reserved          NUMBER;
       l_qty_suggested         NUMBER;
       l_lot_control_code      BOOLEAN;
       l_serial_control_code   BOOLEAN;
    BEGIN
       -- Set the org context
        FND_GLOBAL.apps_initialize ( user_id => 1, resp_id => 64521, resp_appl_id => 401,security_group_id => 0);
       inv_quantity_tree_grp.clear_quantity_cache; -- Clear Quantity cache    

       -- Set the variable values
       l_item_id := 15141562;
       l_organization_id := 96;
       l_lot_control_code := FALSE;  --Only When Lot number is passed  TRUE else FALSE
       l_serial_control_code := FALSE;
       -- Call API
       inv_quantity_tree_pub.query_quantities
                   (p_api_version_number       => 1.0
                   ,p_init_msg_lst             => NULL
                   ,x_return_status            => x_return_status
                   ,x_msg_count                => x_msg_count
                   ,x_msg_data                 => x_msg_data
                   ,p_organization_id          => l_organization_id
                   ,p_inventory_item_id        => l_item_id
                   ,p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode

                   ,p_is_revision_control      => FALSE
                   ,p_is_lot_control           => l_lot_control_code-- is_lot_control,
                   ,p_is_serial_control        => l_serial_control_code
                   ,p_revision                 => NULL              -- p_revision,
                   ,p_lot_number               => NULL              -- p_lot_number,
                   ,p_lot_expiration_date      => SYSDATE
                   ,p_subinventory_code        => NULL              -- p_subinventory_code,
                   ,p_locator_id               => NULL              -- p_locator_id,
                   --,p_cost_group_id            => NULL          
                   --,p_onhand_source            => NULL
                   ,x_qoh                      => l_qty_on_hand     -- Quantity on-hand
                   ,x_rqoh                     => l_res_qty_on_hand --reservable quantity on-hand
                   ,x_qr                       => l_qty_reserved
                   ,x_qs                       => l_qty_suggested
                   ,x_att                      => l_avail_to_tnsct  -- available to transact
                   ,x_atr                      => l_avail_to_reserve-- available to reserve
                   );
       DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || l_qty_on_hand);
       DBMS_OUTPUT.put_line ('Quantity Reserved: ' || l_qty_reserved);
       DBMS_OUTPUT.put_line ('Quantity Suggested: ' || l_qty_suggested);
       DBMS_OUTPUT.put_line ('Available to Transact: ' || l_avail_to_tnsct);
       DBMS_OUTPUT.put_line ('Available to Reserve: ' || l_avail_to_reserve);
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
    END;

    Wednesday, May 9, 2012

    You have encountered an unexpected error. Please contact the System Administrator for assistance.There are no active responsibilities available for this user.

    Error message occurs when database is cloned between instances.

    It can be resolved by running following concurrent programs from System Administrator responsibility.
    1. Sync responsibility role data into the WF table.
    2. Synchronize WF LOCAL tables
    3. Workflow Directory Services User/Role Validation


    4. If none of the above options work restart App server by clearing cache.