Printed Requisitions Report in PO


This Program will register concurrent executable, programs, parameters for the Bhaskar Printed Requisitions Report



Creating Concurrent Executable and Program and adding to a Request Group              

----- Parameter Hints -----

PROMPT              application_name       : PO
PROMPT              group_application_name : Purchasing
PROMPT              request_group_name     : All Reports

DECLARE
  application_name_in             VARCHAR2(500);
  group_application_name_in       VARCHAR2(500);
  request_group_name_in           VARCHAR2(500);
  application_id_ret              NUMBER;
  group_application_id_ret        NUMBER;
  group_id                        NUMBER;
BEGIN
 DBMS_OUTPUT.PUT_LINE('1');
  application_name_in := 'PO'; 
  group_application_name_in := 'Purchasing';
  request_group_name_in := 'All Reports';
 BEGIN
     If fnd_program.program_exists('POXPREQR',application_name_in) then
                 fnd_program.remove_from_group
                 (             program_short_name     => 'POXPREQR'
                                ,program_application    => application_name_in
                                ,request_group          => request_group_name_in
                                ,group_application      => group_application_name_in
                 );
     End if;
 END;

  DBMS_OUTPUT.PUT_LINE('2');

  /********************************************************************
  -- If Program Exists, Delete Concurrent Program and it's executables
  ********************************************************************/
 BEGIN
    IF FND_PROGRAM.PROGRAM_EXISTS  ('POXPREQR',application_name_in) THEN
      FND_PROGRAM.DELETE_PROGRAM   ('POXPREQR',application_name_in);
      FND_PROGRAM.DELETE_EXECUTABLE('POXPREQR',application_name_in);
    END IF;
 END ;

  /*******************************************************************
    -- Create concurrent program executable
   ******************************************************************/
 BEGIN
    FND_PROGRAM.executable (
      executable           => 'POXPREQR',
      application          => application_name_in,
      short_name           => 'POXPREQR',
      description          => 'Printed Purchase Requisition Report',
      execution_method     => 'Oracle Reports',
      execution_file_name  => 'POXPREQR',
      subroutine_name      => NULL,
      icon_name            => NULL,
      language_code        => 'US',
      execution_file_path  => NULL
      );
 END;

  /******************************************************************
    -- Create concurrent program
  ******************************************************************/
  BEGIN
  FND_PROGRAM.register (
      program                  => 'Bhaskar Printed Requisitions Report',
      application              => application_name_in,
      enabled                  => 'Y',
      short_name               => 'POXPREQR',
      description              => 'Printed Requisitions Report',
      executable_short_name    => 'POXPREQR',
      executable_application   => Application_name_in,
      execution_options        => NULL,
      priority                 => NULL,
      save_output              => 'Y',
      print                    => 'Y',
      cols                     => NULL,
      rows                     => NULL,
      style                    => 'Landscape',
      style_required           => 'N',
      printer                  => NULL,
      request_type             => NULL,
      request_type_application => NULL,
      use_in_srs               => 'Y',
      allow_disabled_values    => 'Y',
      run_alone                => 'N',
      output_type              => 'PDF',
      enable_trace             => 'N',
      restart                  => 'Y',
      nls_compliant            => 'Y',
      icon_name                => NULL,
      language_code            => 'US',
      mls_function_short_name  => NULL,
      mls_function_application => NULL,
      incrementor              => NULL
      );     
  END;

     /*********************************************************************
        -- Creating Value set for Apprvoed Requisition_Number From
     *********************************************************************/
      BEGIN
                IF fnd_flex_val_api.valueset_exists('PO_SRS_APPROVED_REQUISITION_NUMBER') THEN    
                        fnd_flex_val_api.delete_valueset('PO_SRS_APPROVED_REQUISITION_NUMBER');
                END IF;
        END;

      BEGIN
              
        IF NOT fnd_flex_val_api.valueset_exists('PO_SRS_APPROVED_REQUISITION_NUMBER') THEN
                        fnd_flex_val_api.set_session_mode('customer_data');
                        fnd_flex_val_api.create_valueset_table(
                        value_set_name                  => 'PO_SRS_APPROVED_REQUISITION_NUMBER'
                        ,description                    => 'Approved Requisition Number'
                        ,security_available             => 'N'
                        ,enable_longlist                => 'N'
                        ,format_type                    => 'Char'
                        ,maximum_size                   => 20
                        ,precision                      => NULL
                        ,numbers_only                   => 'N'
                        ,uppercase_only                 => 'N'
                        ,right_justify_zero_fill        => 'N'
                        ,min_value                      => NULL
                        ,max_value                      => NULL
                        ,table_application              => 'Purchasing'
                        ,table_name                     => 'PO_REQUISITION_HEADERS PRH,PO_SYSTEM_PARAMETERS PSP'
                        ,allow_parent_values            => 'N'
                        ,value_column_name              => 'PRH.SEGMENT1'
                        ,value_column_type              => 'Char'
                        ,value_column_size              => 20
                        ,meaning_column_name            => NULL
                        ,meaning_column_type            => NULL
                        ,meaning_column_size            => NULL
                        ,id_column_name                 => 'PRH.SEGMENT1'
                        ,id_column_type                 => 'Char'
                        ,id_column_size                 => 20
                        ,where_order_by                 => 'where (nvl(PRH.authorization_status,'INCOMPLETE')='APPROVED')
                                                            ORDER BY DECODE(PSP.manual_req_num_type, 'NUMERIC',to_number(PRH.segment1)),
                                                                            DECODE(PSP.manual_req_num_type, 'ALPHANUMERIC',PRH.segment1)'
                        ,additional_columns             => NULL --' DESCRIPTION'
                        );
                                                                                                                                 
        END IF;
       END;

     /**************************************************************
        -- Creating Value set for Apprvoed Requisition_Number To
     **************************************************************/
     /* BEGIN
                IF fnd_flex_val_api.valueset_exists('PO_SRS_APPROVED_REQUISITION_NUMBER') THEN
                        fnd_flex_val_api.delete_valueset('PO_SRS_APPROVED_REQUISITION_NUMBER');
                END IF;
        END;
      BEGIN
        IF NOT fnd_flex_val_api.valueset_exists('PO_SRS_APPROVED_REQUISITION_NUMBER') THEN
                        fnd_flex_val_api.set_session_mode('customer_data');
                        fnd_flex_val_api.create_valueset_table(
                        value_set_name                  => 'PO_SRS_APPROVED_REQUISITION_NUMBER'
                        ,description                    => 'Approved Requisition Number'
                        ,security_available             => 'N'
                        ,enable_longlist                => 'N'
                        ,format_type                    => 'Char'
                        ,maximum_size                   => 20
                        ,precision                      => NULL
                        ,numbers_only                   => 'N'
                        ,uppercase_only                 => 'N'
                        ,right_justify_zero_fill        => 'N'
                        ,min_value                      => NULL
                        ,max_value                      => NULL
                        ,table_application              => 'Purchasing'
                        ,table_name                     => 'PO_REQUISITION_HEADERS PRH,PO_SYSTEM_PARAMETERS PSP'
                        ,allow_parent_values            => 'N'
                        ,value_column_name              => 'PRH.SEGMENT1'
                        ,value_column_type              => 'Char'
                        ,value_column_size              => 20
                        ,meaning_column_name            => NULL
                        ,meaning_column_type            => NULL
                        ,meaning_column_size            => NULL
                        ,id_column_name                 => 'PRH.SEGMENT1'
                        ,id_column_type                 => 'Char
                        ,id_column_size                 => 20
                        ,where_order_by                 => 'where (nvl(PRH.authorization_status,'INCOMPLETE')='APPROVED')
                                                            ORDER BY DECODE(PSP.manual_req_num_type, 'NUMERIC',to_number(PRH.segment1)),
                                                                            DECODE(PSP.manual_req_num_type, 'ALPHANUMERIC',PRH.segment1)'
                        ,additional_columns             => NULL --' DESCRIPTION'
                        );
        END IF;
       END;     */

     /*****************************************************
        -- Creating Value set for Quantity Precision
     *****************************************************/
      BEGIN
                IF fnd_flex_val_api.valueset_exists('INV_SRS_PRECISION') THEN    
                        fnd_flex_val_api.delete_valueset('INV_SRS_PRECISION');
                END IF;
        END;

      BEGIN
              
        IF NOT fnd_flex_val_api.valueset_exists('INV_SRS_PRECISION') THEN
                        fnd_flex_val_api.set_session_mode('customer_data');
                        fnd_flex_val_api.create_valueset_table(
                        value_set_name                  => 'INV_SRS_PRECISION'
                        ,description                    => 'Dynamic Precision Option'
                        ,security_available             => 'N'
                        ,enable_longlist                => 'N'
                        ,format_type                    => 'Number'
                        ,maximum_size                   => 2
                        ,precision                      => NULL
                        ,numbers_only                   => 'Y'
                        ,uppercase_only                 => 'N'
                        ,right_justify_zero_fill        => 'N'
                        ,min_value                      => 0
                        ,max_value                      => 13
                        ,table_application              => 'Inventory'
                        ,table_name                     => 'MFG_LOOKUPS'
                        ,allow_parent_values            => 'N'
                        ,value_column_name              => 'LOOKUP_CODE'
                        ,value_column_type              => 'Number'
                        ,value_column_size              => 2
                        ,meaning_column_name            => 'MEANING'
                        ,meaning_column_type            => 'Varchar2'
                        ,meaning_column_size            => 80
                        ,id_column_name                 => 'LOOKUP_CODE'
                        ,id_column_type                 => 'Number'
                        ,id_column_size                 => 22
                        ,where_order_by                 => 'WHERE LOOKUP_TYPE = 'INV_SRS_PRECISION'and enabled_flag = 'Y'
                                                            order by LOOKUP_CODE'
                        ,additional_columns             => NULL --' DESCRIPTION'
                        );
                                                                                                                                 
        END IF;
       END;
      
      
   /******************************************************************
        --Create concurrent program parameters P_REQ_NO_FROM
   ******************************************************************/
     BEGIN
      FND_PROGRAM.PARAMETER(
        program_short_name                      => 'POXPREQR',
                application                     => application_name_in,
                sequence                        => 10,
                parameter                       => 'P_REQ_NO_FROM',
                description                     => 'Req Numbers From',
                enabled                         => 'Y',
                value_set                       => 'PO_SRS_APPROVED_REQUISITION_NUMBER',
                default_type                    => NULL,
                default_value                   => NULL,
                required                        => 'N',
                enable_security                 => 'N',
                range                           => NULL,
                display                         => 'Y',
                display_size                    => 20,
                description_size                => 50,
                concatenated_description_size   => 25,
                prompt                          => 'P_REQ_NO_FROM',
                token                           => 'P_REQ_NO_FROM'
          
                     );
      END;

   /******************************************************************
        --Create concurrent program parameters P_REQ_NO_TO
   ******************************************************************/
     BEGIN
      FND_PROGRAM.PARAMETER(
        program_short_name                      => 'POXPREQR',
                application                     => application_name_in,
                sequence                        => 20,
                parameter                       => 'P_REQ_NO_TO',
                description                     => 'Req Numbers To',
                enabled                         => 'Y',
                value_set                       => 'PO_SRS_APPROVED_REQUISITION_NUMBER',
                default_type                    => NULL,
                default_value                   => NULL,
                required                        => 'N',
                enable_security                 => 'N',
                range                           => NULL,
                display                         => 'Y',
                display_size                    => 20,
                description_size                => 50,
                concatenated_description_size   => 25,
                prompt                          => 'P_REQ_NO_TO',
                token                           => 'P_REQ_NO_TO'
          
                     );
      END;


  /******************************************************************
        --Create concurrent program parameters P_QTY_PRECISION
   *****************************************************************/
     BEGIN
      FND_PROGRAM.PARAMETER(
        program_short_name                      => 'POXPREQR',
                application                     => application_name_in,
                sequence                        => 30,
                parameter                       => 'P_QTY_PRECISION',
                description                     => 'Dynamic Quantity Precision',
                enabled                         => 'Y',
                value_set                       => 'INV_SRS_PRECISION',
                default_type                    => 'Profile',
                default_value                   => 'REPORT_QUANTITY_PRECISION',
                required                        => 'Y',
                enable_security                 => 'N',
                range                           => NULL,
                display                         => 'Y',
                display_size                    => 2,
                description_size                => 50,
                concatenated_description_size   => 25,
                prompt                          => 'P_QTY_PRECISION',
                token                           => 'P_QTY_PRECISION'
          
                     );
      END;

     
   /****************************************************************
   -- Add to the request group
   ****************************************************************/
      BEGIN
        fnd_program.add_to_group (
                program_short_name         => 'POXPREQR',
                program_application        => application_name_in,
                request_group              => request_group_name_in,
                group_application          => group_application_name_in
                                    );
      END;
  commit;
END;
/
--exit;

1 comment:

  1. Hi Frnd..This is Rajesh...I want to know what is the session_mode in value set creation..Can u explain it in detail..I was getting problems when creating value set through backend...

    Thanks
    Rajesh P

    ReplyDelete

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...