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

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);

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;

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.