INV Cycle count entries and adjustments

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Cycle count entries and adjustments report (XML)
Short Name: INVARCTA_XML
DB package: INV_INVARCTA_XMLP_PKG
SELECT
                 1    COUNT_FLAG,
                 CE.INVENTORY_ITEM_ID                        INVENTORY_ITEM_ID,
                 CE.SUBINVENTORY                              SUBINVENTORY,
                 CE.LOCATOR_ID                                   LOCATOR_ID,
                 CL.ABC_CLASS_NAME                         ABC_CLASS_NAME,
                 ITEMS.ITEM_NUMBER                            ITEM_NUMBER,
                 CE.REVISION                                         REVISION,
                 CE.LOT_NUMBER                                 LOT_NUMBER,
                 SITEMS.DESCRIPTION                            DESCRIPTION,
                 ce.count_type_code                             COUNT_TYPE_CODE,
                 nvl(CE.ITEM_UNIT_COST,0)                  ITEM_UNIT_COST,
                 round( nvl(CE.COUNT_QUANTITY_CURRENT,0) ,
                         :P_qty_precision)
                                                           COUNT_QUANTITY_CURRENT,
                  CE.SYSTEM_QUANTITY_CURRENT  SYSTEM_QUANTITY_CURRENT,
                round ( NVL( CE.ADJUSTMENT_QUANTITY,0) ,
                             :P_qty_precision)                 ADJUSTMENT_QUANTITY_CURRENT,
                to_char(DECODE(CE.ENTRY_STATUS_CODE,
                       5, CE.APPROVAL_DATE,
                          CE.COUNT_DATE_CURRENT), 'DD-MON-YYYY')            CURRENT_DATE,
                CE.ENTRY_STATUS_CODE                        ENTRY_STATUS_CODE,
                decode (ce.count_type_code, 4,0, DECODE(CE.ENTRY_STATUS_CODE, 5,1,0) )
                                                       COMPLETED_COUNTER,
                MFG1.MEANING                     COMPLETED_FLAG,
                substr(MFG.MEANING,1,9)                  MEANING,
                null                             C_ITEM_FLEX,
                null                             C_LOCATOR_FLEX,
                ITEMS.PRIMARY_UOM_CODE                   UOM,
                round( nvl(CE.COUNT_QUANTITY_PRIOR,0) ,
                         :P_qty_precision)
                                                                     COUNT_QUANTITY_PRIOR,
                 round( nvl(CE.COUNT_QUANTITY_FIRST,0) ,
                         :P_qty_precision)
                                                                COUNT_QUANTITY_FIRST,
                 round ( ce.count_quantity_first - ce.system_quantity_first,
                               :P_qty_precision)                 ADJUSTMENT_QUANTITY_FIRST,
                 round ( ce.count_quantity_prior - ce.system_quantity_prior ,
                               :P_qty_precision)                 ADJUSTMENT_QUANTITY_PRIOR,
                 to_char(CE.COUNT_DATE_PRIOR, 'DD-MON-YYYY')                COUNT_DATE_PRIOR,
                 to_char(CE.COUNT_DATE_FIRST, 'DD-MON-YYYY')                COUNT_DATE_FIRST,
                 MFG2.MEANING                   NON_COMPLETED_FLAG,
            CE. NUMBER_OF_COUNTS NUMBER_OF_COUNTS,
            CE.COUNT_UOM_CURRENT,
            CE.COUNT_UOM_FIRST,
            CE.COUNT_UOM_PRIOR,
            CYCLE_COUNT_ENTRY_ID,
            NVL(SERIAL_DETAIL,0)SERIAL_DETAIL, --Bug20360988
            SERIAL_NUMBER,
            ADJUSTMENT_QUANTITY POS_ADJUSTMENT_QUANTITY,
            ADJUSTMENT_AMOUNT POS_ADJUSTMENT_AMOUNT,
            NEG_ADJUSTMENT_QUANTITY,
            NEG_ADJUSTMENT_AMOUNT,
            CE.PARENT_LPN_ID PARENT_LPN_ID,
            CE.OUTERMOST_LPN_ID OUTERMOST_LPN_ID,
            CE.COST_GROUP_ID,
                INV_INVARCTA_XMLP_PKG.s_other_countsformula(:s_total_counts, :s_counts_completed) s_other_counts,
                INV_INVARCTA_XMLP_PKG.s_gross_adjformula(:s_pos_adj, :s_neg_adj) s_gross_adj,
                INV_INVARCTA_XMLP_PKG.s_net_adjformula(:s_pos_adj, :s_neg_adj) s_net_adj,
                INV_INVARCTA_XMLP_PKG.s_net_accuracyformula(:s_net_adj, :s_inventory_value) s_net_accuracy,
                INV_INVARCTA_XMLP_PKG.s_gross_accuracyformula(:s_gross_adj, :s_inventory_value) s_gross_accuracy,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('f_locator_value', 'INV', 'MTLL', 101, LOC.ORGANIZATION_ID, LOC.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') F_LOCATOR_VALUE,
                /*INV_INVARCTA_XMLP_PKG.pos_adjformula(:ADJUSTMENT_AMOUNT) POS_ADJ,*/
                INV_INVARCTA_XMLP_PKG.pos_adjformula(INV_INVARCTA_XMLP_PKG.adjustment_amountformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, round ( NVL ( CE.ADJUSTMENT_QUANTITY , 0 ) , :P_qty_precision ), INV_INVARCTA_XMLP_PKG.conv_rate_currentformula(CE.COUNT_UOM_CURRENT, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE), nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION)) POS_ADJ,
                /*INV_INVARCTA_XMLP_PKG.neg_adjformula(:ADJUSTMENT_AMOUNT, NEG_ADJUSTMENT_AMOUNT) NEG_ADJ,*/
                INV_INVARCTA_XMLP_PKG.neg_adjformula(INV_INVARCTA_XMLP_PKG.adjustment_amountformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, round ( NVL ( CE.ADJUSTMENT_QUANTITY , 0 ) , :P_qty_precision ), INV_INVARCTA_XMLP_PKG.conv_rate_currentformula(CE.COUNT_UOM_CURRENT, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE), nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION), NEG_ADJUSTMENT_AMOUNT) NEG_ADJ,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('f_item_value', 'INV', 'MSTK', 101, SITEMS.ORGANIZATION_ID, SITEMS.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') F_ITEM_VALUE,
                INV_INVARCTA_XMLP_PKG.c_approvedfieldformula(CE.ENTRY_STATUS_CODE, MFG1.MEANING, MFG2.MEANING) C_ApprovedField,
                /*INV_INVARCTA_XMLP_PKG.item_inv_value_currentformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, CE.SYSTEM_QUANTITY_CURRENT, :CONV_RATE_CURRENT, nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION) ITEM_INV_VALUE_CURRENT,*/
                INV_INVARCTA_XMLP_PKG.item_inv_value_currentformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, CE.SYSTEM_QUANTITY_CURRENT, INV_INVARCTA_XMLP_PKG.conv_rate_currentformula(CE.COUNT_UOM_CURRENT, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE), nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION) ITEM_INV_VALUE_CURRENT,
                INV_INVARCTA_XMLP_PKG.conv_rate_priorformula(CE.COUNT_UOM_PRIOR, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE) CONV_RATE_PRIOR,
                INV_INVARCTA_XMLP_PKG.conv_rate_firstformula(CE.COUNT_UOM_FIRST, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE) CONV_RATE_FIRST,
                INV_INVARCTA_XMLP_PKG.conv_rate_currentformula(CE.COUNT_UOM_CURRENT, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE) CONV_RATE_CURRENT,
                /*INV_INVARCTA_XMLP_PKG.adjustment_amountformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, round ( NVL ( CE.ADJUSTMENT_QUANTITY , 0 ) , :P_qty_precision ), :CONV_RATE_CURRENT, nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION) ADJUSTMENT_AMOUNT,*/
                INV_INVARCTA_XMLP_PKG.adjustment_amountformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, round ( NVL ( CE.ADJUSTMENT_QUANTITY , 0 ) , :P_qty_precision ), INV_INVARCTA_XMLP_PKG.conv_rate_currentformula(CE.COUNT_UOM_CURRENT, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE), nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION) ADJUSTMENT_AMOUNT,
                /*INV_INVARCTA_XMLP_PKG.adjustment_amount_priorformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, round ( ce.count_quantity_prior - ce.system_quantity_prior , :P_qty_precision ), :CONV_RATE_PRIOR, nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION) ADJUSTMENT_AMOUNT_PRIOR,
                INV_INVARCTA_XMLP_PKG.adjustment_amount_firstformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, round ( ce.count_quantity_first - ce.system_quantity_first , :P_qty_precision ), :CONV_RATE_FIRST, nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION) ADJUSTMENT_AMOUNT_FIRST,*/
                INV_INVARCTA_XMLP_PKG.adjustment_amount_priorformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, round ( ce.count_quantity_prior - ce.system_quantity_prior , :P_qty_precision ), INV_INVARCTA_XMLP_PKG.conv_rate_priorformula(CE.COUNT_UOM_PRIOR, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE), nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION) ADJUSTMENT_AMOUNT_PRIOR,
                INV_INVARCTA_XMLP_PKG.adjustment_amount_firstformula(CE.ENTRY_STATUS_CODE, ce.count_type_code, round ( ce.count_quantity_first - ce.system_quantity_first , :P_qty_precision ), INV_INVARCTA_XMLP_PKG.conv_rate_firstformula(CE.COUNT_UOM_FIRST, CE.INVENTORY_ITEM_ID, ITEMS.PRIMARY_UOM_CODE), nvl ( CE.ITEM_UNIT_COST , 0 ), :S_STD_PRECISION) ADJUSTMENT_AMOUNT_FIRST,
                INV_INVARCTA_XMLP_PKG.cf_outermost_lpnformula(CE.OUTERMOST_LPN_ID) CF_OUTERMOST_LPN,
                INV_INVARCTA_XMLP_PKG.cf_cost_groupformula(CE.COST_GROUP_ID) CF_COST_GROUP,
                INV_INVARCTA_XMLP_PKG.cf_parent_lpnformula(CE.PARENT_LPN_ID) CF_PARENT_LPN
            FROM
                 MTL_ITEM_LOCATIONS                LOC,
                 MTL_CYCLE_COUNT_ITEMS             CCI,
                 MTL_ABC_CLASSES                    CL,
                 MFG_LOOKUPS                       MFG,
                 MFG_LOOKUPS                      MFG1,
                 MFG_LOOKUPS              MFG2,
                MTL_ITEM_FLEXFIELDS              ITEMS,
                 MTL_SYSTEM_ITEMS_VL                SITEMS,
                MTL_CYCLE_COUNT_ENTRIES   CE
            WHERE
                  CE.LOCATOR_ID =
                  LOC.INVENTORY_LOCATION_ID(+)
             AND  CE.ORGANIZATION_ID = LOC.ORGANIZATION_ID(+)
             AND  SITEMS.INVENTORY_ITEM_ID
                        = CE.INVENTORY_ITEM_ID
             AND  SITEMS.ORGANIZATION_ID  = :P_ORG_ID
             AND  CE.ORGANIZATION_ID =  :P_ORG_ID
             AND  ITEMS.ORGANIZATION_ID = :P_ORG_ID
             AND (  (TO_CHAR ( (DECODE (CE.ENTRY_STATUS_CODE,5,
                                CE.APPROVAL_DATE,
                                CE.COUNT_DATE_CURRENT) ), 'YYYY/MM/DD' ) >=
                        TO_CHAR ((:P_FROM_DATE),'YYYY/MM/DD') )
                   OR
                      (TO_CHAR ((DECODE (CE.ENTRY_STATUS_CODE,5,
                                CE.APPROVAL_DATE,
                                CE.COUNT_DATE_FIRST) ),'YYYY/MM/DD' ) >=
                        TO_CHAR ((:P_FROM_DATE), 'YYYY/MM/DD') )
                   OR
                       (TO_CHAR ((DECODE (CE.ENTRY_STATUS_CODE,5,
                                CE.APPROVAL_DATE,
                                CE.COUNT_DATE_PRIOR )), 'YYYY/MM/DD' ) >=
                        TO_CHAR ((:P_FROM_DATE), 'YYYY/MM/DD') ))
            AND  ( (TO_CHAR ((DECODE (CE.ENTRY_STATUS_CODE,5,
                                CE.APPROVAL_DATE,
                                CE.COUNT_DATE_CURRENT)), 'YYYY/MM/DD')  <=
                      TO_CHAR ((:P_TO_DATE), 'YYYY/MM/DD') )
                    OR
                     (TO_CHAR ((DECODE (CE.ENTRY_STATUS_CODE,5,
                                CE.APPROVAL_DATE,
                                CE.COUNT_DATE_FIRST)), 'YYYY/MM/DD')  <=
                      TO_CHAR ((:P_TO_DATE), 'YYYY/MM/DD') )
                     OR
                     (TO_CHAR ((DECODE (CE.ENTRY_STATUS_CODE,5,
                                CE.APPROVAL_DATE,
                                CE.COUNT_DATE_PRIOR)), 'YYYY/MM/DD')  <=
                      TO_CHAR ((:P_TO_DATE), 'YYYY/MM/DD') ) )
            AND  CE.CYCLE_COUNT_HEADER_ID  =       :P_CYCLE_COUNT_HEADER_ID
            AND  ( CE.SUBINVENTORY  = :P_SUBINVENTORY
                         OR  :P_SUBINVENTORY IS NULL)
            AND   CE.ENTRY_STATUS_CODE  <> 1
            AND   MFG.LOOKUP_TYPE = 'MTL_CC_COUNT_TYPES'
            AND   MFG.LOOKUP_CODE = CE.COUNT_TYPE_CODE
            AND   CCI.CYCLE_COUNT_HEADER_ID =
                       CE.CYCLE_COUNT_HEADER_ID
            AND   CCI.INVENTORY_ITEM_ID =
                       CE.INVENTORY_ITEM_ID
            AND   CCI.ABC_CLASS_ID   =  CL.ABC_CLASS_ID
            AND   CE.INVENTORY_ITEM_ID = ITEMS.ITEM_ID
            AND   MFG1.LOOKUP_TYPE = 'SYS_YES_NO'
            AND   MFG1.LOOKUP_CODE =  1
            AND   MFG2.LOOKUP_TYPE = 'SYS_YES_NO'
            AND   MFG2.LOOKUP_CODE =  2
			AND   CE.ENTRY_STATUS_CODE = DECODE(:P_APPROVED,1,5,CE.ENTRY_STATUS_CODE) -- Bug17478035
            --ORDER BY 3, 41, 42, 43, 3,21,5,20,7,SERIAL_NUMBER,8,15 desc
            ORDER BY CE.SUBINVENTORY, F_ITEM_VALUE,
            DECODE(CE.ENTRY_STATUS_CODE, 5, CE.APPROVAL_DATE, CE.COUNT_DATE_CURRENT) desc
Parameter Name SQL text Validation
Precision
 
LOV Oracle
Organization
 
Number
Approved Counts Only
 
LOV Oracle
Display Serial Numbers
 
LOV Oracle
End Date
 
Date
Start Date
 
Date
Subinventory
 
LOV Oracle
Cycle Count Name
 
LOV Oracle
Ask a question