Infolinks

Sunday 12 May 2013

SEEDED REPORT (Physical inventory adjustments report)

Report  File Name: INVARPAR

Data Modal:
========

Query1:
=====

select o.organization_name             org_name,
       o.chart_of_accounts_id              chart_id,
       g.currency_code              currency_code,
       c.precision        std_precision,
       nvl(c.extended_precision,c.precision)    ext_precision
from   org_organization_definitions o,
          gl_sets_of_books                g,
          fnd_currencies                    c
where  o.organization_id = :P_org_id
and      o.set_of_books_id = g.set_of_books_id

================================================================================

Query2:
=====
select decode(:P_break_id,1,nvl(subinventory_name,' '),
              'X')        Break_option,
       &P_item_flex       C_item_flex,
       mpa.revision       Rev,
       subinventory_name  Subinv,
       &P_loc_flex        C_loc_flex,
       lot_number,
       serial_number,  
       system_quantity,
       nvl(count_quantity,0),
       msi.primary_uom_code UOM,
       adjustment_quantity,
       nvl(system_quantity*mpa.actual_cost,0)   system_value,
       round(nvl(count_quantity*mpa.actual_cost,0) ,:C_std_prec)   count_value,
 round(nvl(adjustment_quantity*mpa.actual_cost,0),:C_std_prec)   adjustment_value,
       loc.inventory_location_id       loc_id,
       rownum,
      decode(:P_break_id,2,nvl(subinventory_name,' '),
              'X')        second_sort_option,
      mpa.outermost_lpn_id,
      mpa.parent_lpn_id,
      mpa.cost_group_id,
      mpa.APPROVED_BY_EMPLOYEE_ID emp_id
from
       mtl_system_items           msi,
       mtl_item_locations         loc,
       mtl_physical_adjustments   mpa
where mpa.inventory_item_id =
       msi.inventory_item_id
and    mpa.locator_id = 
       loc.inventory_location_id(+)
and    mpa.organization_id = msi.organization_id
and  mpa.organization_id   =      
        loc.organization_id(+)
and    mpa.organization_id = :P_org_id
and    mpa.physical_inventory_id = :P_phys_inv_id
and    mpa.adjustment_quantity <> 0
&p_include_rej_items_opt
&P_condition
order by
1,  (nvl(count_quantity*mpa.actual_cost, 0)-nvl(system_quantity*mpa.actual_cost,0)) &C_sort,  17 asc, 2 asc, 3 asc, 5 asc, 6 asc, 7 asc

====================================

User Parameters:
===========
P_BREAK_ID
P_CONC_REQUEST_ID
P_CONDITION
P_INCLUDE_REJ_ITEMS
P_INCLUDE_REJ_ITEMS_OPT
P_ITEM_FLEX
P_LOC_FLEX
P_ORDER_ITEM
P_ORG_ID
P_PHYS_INV_ID
P_QTY_PRECISION
P_REJ_OPT_TITLE
P_SORT_ID
P_STRUCT_NUM
P_WMS_INSTALLED

BEFORE REPORT TRIGGER:
===============

function BeforeReport return boolean is
begin

BEGIN
SRW.USER_EXIT('FND SRWINIT');
exception
  when srw.user_exit_failure then
  srw.message(1,'Failed in before report trigger:SRWINIT');
  raise;
END;
declare
p_org_id_char varchar2(100):= to_char(:p_org_id);
begin
fnd_profile.put('MFG_ORGANIZATION_ID',p_org_id_char); --Bug3940118
srw.user_exit('FND PUTPROFILE NAME="'||'MFG_ORGANIZATION_ID'||'" FIELD="'||p_org_id_char||'"');
exception
   when srw.user_exit_failure then srw.message(020,'Failed in before report trigger, setting org profile ');
raise;
end;

begin
  select meaning into :p_rej_opt_title from mfg_lookups
  where lookup_type = 'SYS_YES_NO'
  and lookup_code = nvl(:p_include_rej_items,1) ;
exception
when others then null;
end;

-- Include rejected adj or not
if nvl(:p_include_rej_items,1) = 1 then
   :p_include_rej_items_opt := '' ;
else
   :p_include_rej_items_opt := 'and nvl(mpa.approval_status,1) <> 2 ' ;   -- exclude rejected
end if;

BEGIN
SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" NUM=":P_STRUCT_NUM"
               APPL_SHORT_NAME="INV" OUTPUT="P_item_flex"
               MODE="SELECT" DISPLAY="ALL" TABLEALIAS="MSI"');
exception
  when srw.user_exit_failure then
  srw.message(1,'Failed in before report trigger:MSTK');
  raise;
END;
BEGIN
SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" NUM=":P_STRUCT_NUM"
               APPL_SHORT_NAME="INV" OUTPUT="P_order_item"
               MODE="ORDER BY" DISPLAY="ALL" TABLEALIAS="MSI"');
exception
  when srw.user_exit_failure then
  srw.message(1,'Failed in before report trigger:MSTK:ORDERBY');
  raise;
END;
BEGIN
SRW.USER_EXIT('FND FLEXSQL CODE="MTLL" NUM=":P_STRUCT_NUM"
               APPL_SHORT_NAME="INV" OUTPUT="P_loc_flex"
               MODE="SELECT" DISPLAY="ALL" TABLEALIAS="LOC"');
exception
  when srw.user_exit_failure then
  srw.message(1,'Failed in before report trigger:MTLL');
  raise;
END;

-- WMS related addtions, if WMS is installed make p_wms_installed parameter as true
-- and show all related fields
  declare
    m_return_status varchar2(1);
    m_msg_count     number;
    m_msg_data      varchar2(2000);
  BEGIN
    if (wms_install.check_install(x_return_status    => m_return_status,
                  x_msg_count         => m_msg_count,
                  x_msg_data         => m_msg_data,
                  p_organization_id  => :p_org_id)) then
     :p_wms_installed := 'TRUE';   
     end if;
  END;

  return (TRUE);
end;

AFTER REPORT TRIGGER:
================

function AfterReport return boolean is
begin

BEGIN
SRW.USER_EXIT('FND SRWEXIT');
exception
  when srw.user_exit_failure then
  srw.message(1,'SRWEXIT failed');
END;  return (TRUE);
end;

=============================================================

Concurrent Program:
***********************
Executable:
=======

Executable: INVARPAR
Short Name:INVARPAR
Application: Inventory
Description:Physical Inventory Adjustment Report
Execution Method: Oracle Report
Execution File Name: INVARPAR

Define:
=====

Program: Physical inventory adjustments report
Short Name: INVARPAR
Application: Inventory
Description: Physical inventory adjustments report

Executable:
----------
Name: INVARPAR
Method: Oracle Report

Output:
-------
Format: Text

Columns:132
Rows:45
Style:Landscape

==================================================

Parameters:(Value sets)
*************************

Valuset:
=====
Syatem Administrator-->Application-->Validation-->Set

(1)Parameter:Organization
=================

Value Set Name: INV_SRS_NUMBER
Description: Any Number

List Type:List Of Values   Security type:No Security

Format Validation:
----------------
   Format Type: Number
              [yes] Numbers only (0-9)     Maximum Size:22
      
Value Validation:
---------------

   Validation Type: None
------------------------------------------------------------

(2)Parameter:Break Option   & (3)Include Rejected Items
=================     =================

Value Set Name: INV_SRS_YES_NO
Description: Yes or No

List Type:List Of Values   Security type:No Security

Format Validation:
----------------
   Format Type: Char
              [yes] Numbers only (0-9)     Maximum Size:80
      
Value Validation:
---------------

   Validation Type: Table

(Edit Information)
----------------
 Table Application: Inventory     Table Name: MFG_LOOKUPS

 Table Columns:
 --------------

                    (Name)               (Type)         (Size)       
      Value    : MEANING            Char           80
      Meaning: -----                 -----         -----    (No Need to Write)
      ID        :  LOOKUP_CODE    Number       22


      Where/ Order By:  where lookup_type = 'SYS_YES_NO'
                                order by lookup_code


(4)Parameter:Sort Option
================
Value Set Name: INV_SRS_ASC_DESC
Description: Acsending Descending Sort Option

List Type:List Of Values   Security type:No Security

Format Validation:
----------------
   Format Type: Char
              [yes] Numbers only (0-9)     Maximum Size:80
      
Value Validation:
---------------

   Validation Type: Table

(Edit Information)
----------------
 Table Application: Inventory      Table Name: MFG_LOOKUPS

 Table Columns:
 --------------

                    (Name)               (Type)        (Size)       
      Value    : MEANING            Varchar2     80
      Meaning: -----                 -----         -----    (No Need to Write)
      ID        :  LOOKUP_CODE    Number       22


      Where/ Order By:  WHERE LOOKUP_TYPE = 'INV_SRS_ASC_DESC'
                AND   ENABLED_FLAG = 'Y'


(5)Parameater:Physical Inventory Name
=========================

Value Set Name: INV_SRS_PHYSICAL_INVENTORY
Description: Physical Inventory Name

List Type:List Of Values   Security type:No Security

Format Validation:
----------------
   Format Type: Char
              [  ] Numbers only (0-9)     Maximum Size:30
      
Value Validation:
---------------

   Validation Type: Table

(Edit Information)
----------------
 Table Application: Inventory      Table Name: MTL_PHYSICAL_INVENTORIES

 Table Columns:
 --------------

                    (Name)                            (Type)             (Size)       
      Value    : PHYSICAL_INVENTORY_NAME            Varchar2           30
      Meaning: DESCRIPTION                     Varchar2         50 
      ID        :  PHYSICAL_INVENTORY_ID           Number          44


      Where/ Order By:  WHERE ORGANIZATION_ID = :$PROFILES$.MFG_ORGANIZATION_ID

(6)Parameater:Dynamic Precision Option
==========================

Value Set Name: INV_SRS_PRECISION
Description: Dynamic Precision Option

List Type:List Of Values   Security type:No Security

Format Validation:
----------------
   Format Type: Number
              [Yes] Numbers only (0-9)     Maximum Size:2

   Min Value: 0                                  Max Value:13       
      
Value Validation:
---------------

   Validation Type: Table

(Edit Information)
----------------
 Table Application: Inventory      Table Name: MFG_LOOKUPS

 Table Columns:
 --------------

                    (Name)                            (Type)             (Size)       
      Value    : LOOKUP_CODE                        Number           2
      Meaning: MEANING                         Varchar2        80 
      ID        :  LOOKUP_CODE               Number         22


      Where/ Order By:  WHERE LOOKUP_TYPE = 'INV_SRS_PRECISION'
              and enabled_flag = 'Y'
              order by LOOKUP_CODE


--------------------------------------------------------------------------

Parameter:
=======

System Administrator-->Concurrent--> Program-->Define-->Parameters

(1)Parameter:Organization
=================
Seq:10
Parameter:Organization
Description:Organization

Validation
---------
Value Set: INV_SRS_NUMBER (Name: INV_SRS_NUMBER, Validation: None, Format: Number, Length: 22, Description: Any Number)

Defoult Type: Profile

Defoult Value: MFG_ORGANIZATION_ID

[Yes] Required

Display Size: 22     Description Size: 50

Concatenated Description Size: 25    Prompt: Organization

Token: P_ORG_ID


(2)Parameter:Break Option
=================
Seq: 20
Parameter:Break Option
Description:Break Option

Validation
---------
Value Set: INV_SRS_YES_NO (Name: INV_SRS_YES_NO, Validation: Table,  Format:  Char, Length: 80, Description: Yes or No)

Defoult Type: SQL Statement

Defoult Value: SELECT MEANING FROM MFG_LOOKUPS WHERE LOOKUP_TYPE = 'SYS_YES_NO' AND LOOKUP_CODE = 2

[Yes] Required

Display Size: 4     Description Size: 50

Concatenated Description Size: 25    Prompt: Display and Break on Subinventory
Token: P_break_id

(3)Parameter:Include Rejected Items
=======================
Seq: 25
Parameter:Include Rejected Items
Description:Include Rejected Items

Validation
---------
Value Set: INV_SRS_YES_NO (Name: INV_SRS_YES_NO, Validation: Table,  Format:  Char, Length: 80, Description: Yes or No)

Defoult Type: SQL Statement

Defoult Value: SELECT MEANING FROM MFG_LOOKUPS WHERE LOOKUP_TYPE = 'SYS_YES_NO' AND LOOKUP_CODE = 1

Display Size: 4     Description Size: 50

Concatenated Description Size: 25    Prompt: Include Rejected Items

Token: p_include_rej_items

(4)Parameter:Sort Option
================
Seq: 30
Parameter:Sort Option
Description:Sort Option

Validation
---------
Value Set: INV_SRS_ASC_DESC (Name: INV_SRS_ASC_DESC, Validation: Table,  Format:  Char, Length: 80, Description: Acsending Descending Sort Option)

Defoult Type: SQL Statement

Defoult Value: select meaning from mfg_lookups where lookup_type = 'INV_SRS_ASC_DESC' and   lookup_code = '2'

Display Size: 20    Description Size: 50

Concatenated Description Size: 25    Prompt: Adjustment Value Sort Option

Token: P_sort_id

(5)Parameater:Physical Inventory Name
=========================
Seq: 35
Parameter:Physical Inventory Name
Description:Physical Inventory Name

Validation
---------
Value Set: INV_SRS_PHYSICAL_INVENTORY (Name: INV_SRS_PHYSICAL_INVENTORY, Validation: Table,  Format:  Char, Length: 30, Description: Physical Inventory Name)

Defoult Type: -----

Defoult Value: -----

[Yes] Required
Display Size: 30    Description Size: 50

Concatenated Description Size: 25    Prompt: Physical Inventory

Token: P_phys_inv_id

(6)Parameater:Dynamic Precision Option
==========================
Seq: 40
Parameter:Dynamic Precision Option
Description:Dynamic Precision Option

Validation
---------
Value Set: INV_SRS_PRECISION (Name: INV_SRS_PRECISION, Validation: Table,  Format:  Number, Length: 2, Description: Dynamic Precision Option)

Defoult Type: Profile

Defoult Value: REPORT_QUANTITY_PRECISION

[  ] Required
Display Size: 2    Description Size: 50

Concatenated Description Size: 25    Prompt: Dynamic Precision Option

Token: P_qty_precision




================================by surendra===========================

No comments:

Post a Comment