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<