Query to find Templates details of XML publisher reports

SELECT distinct(XL.file_name )"File Name",
xddv.data_source_code "Data Definition Code",
       xddv.data_source_name "Data Definition",
       xddv.description "Data Definition Description",
       xtb.template_code "Template Code",
       xtt.template_name "Template Name",
       xtt.description "Template Description",
       xtb.template_type_code "Type",
       xl.file_name "File Name",
       xtb.default_output_type "Default Output Type"
  FROM apps.XDO_DS_DEFINITIONS_VL xddv,
       apps.XDO_TEMPLATES_B xtb,
       apps.XDO_TEMPLATES_TL xtt,
       apps.XDO_LOBS xl,
       apps.FND_APPLICATION_TL fat,
       APPS.FND_APPLICATION fa
WHERE  xddv.data_source_code like 'XX%'  --Need to change
       AND xddv.application_short_name = fa.application_short_name
       AND fat.application_id = fa.application_id
       AND xtb.application_short_name = xddv.application_short_name
       AND xddv.data_source_code = xtb.data_source_code
       AND xtt.template_code = xtb.template_code
       AND xl.LOB_CODE = xtb.template_code   
       AND xl.xdo_file_type = xtb.template_type_code;

Price List Conversion

Business requirement:

 Convert price list data from 11i to R12.2.7 instance as a part of consolidation project.

Basic check and prerequisite:

1)      Price list header’s end date should not be null.

2)     If price list header’s end date is not null but that price list line may use in sale order line so we need bring that line/header as well in R12.2.7.

3)      Price list header should be Active in 11i instance.

Environment:

Oracle E-Business Suite comes with a set of open interfaces and APIs to convert data into ERP once data is placed into those interface table. In data conversion we convert data form one format to another format in the different version of the same system.

Example: When we upgrade from Oracle 11i to Oracle R12 we do a data conversion from 11i to R12.

Mapping points with data selection criteria:

Flow: 

Approach (interface/API):

Below are some Price List API’s:

•  QP_Price_formula_PUB.Get_Price_Formula (Formula Calculation API): The Formula Calculation package consists of entities to calculate the value of a formula.  
• QP_Price_formula_PUB.Process_Price_Formula (Update Formula Prices API): The Update Formula Prices package consists of entities to update formula prices.  
• QP_CUSTOM.Get_Custom_Price (Get Custom Price API): You may add custom code to this customizable function. The pricing engine while evaluating a formula that contains a formula line (step) of type "function" calls this API.  
• QP_PREQ_GRP.Price_Request (Price Request API): The Price Request Application Program Interface (API) is a public API that allows you to get a base price and to apply price adjustments, other benefits, and charges to a transaction.
•  QP_MODIFIERS_PUB.Process_Modifiers (Business Object for Modifier Setup API): The Business Object for Modifier Setup package consists of entities to set up modifiers.  
• QP_QUALIFIER_RULES_PUB.Process_Qualifier_Rules (Qualifiers API): The Qualifiers package consists of entities to set up qualifiers.  
• QP_ATTR_MAPPING_PUB. Build_Contexts (Attribute Mapping API): The Attribute Mapping package consists of entities to map attributes.  
• QP_Price_List_PUB.Process_Price_List (Price List Setup API): The Price List Setup package consists of entities to set up price lists.

     Setups:

Before start price list conversion in R12.2.7 instance there are few pre requisites which is mandatory for    price list.

1)       Item

2)      Item Attributes

3)      Organization setup

4)      Operating unit set up

Another part include in setup is which needs to set profile options as below.

1)       MO OPERATING UNIT: This profile option only provides access to one operating unit and this is used as the default operating unit during transaction entry.

2)       QP ITEM VALIDATION: Set this profile, by site or responsibility, to an organization at the level in your organization hierarchy at which you set prices for items.

3)      QP: Continuous Price Breaks: Unable to create Volume offer when the QP: Continuous Price Break is set No.

Table Details:

·       QP_LIST_HEADERS_B

·       QP_LIST_LINES_V

·       QP_LIST_HEADERS_TL

·       QP_PRICING_ATTRIBUTES

              ·       QP_PRICE_BREAKS_V

Key columns observations specific issue:

For price breaks value from and value to in 11i allows user below scenario

But in R12.2.7 instance will not be allowed to put above value from and value to

It should be like this.

If user tried to insert values like 11i then it will give below error.

 Fetch data from source instance to staging table:

  Extraction query

Price List Extraction Query

Working example:

-- Cursor to fetch header level records from staging table.

   CURSOR c_hdr_details (p_prl_name IN VARCHAR2, p_currency_code IN VARCHAR2)

   IS

      SELECT DISTINCT global_flag, description, effective_start_date,

                   effective_end_date, comments, secondary_price_list,

                   precedence, prl_action_code, operating_unit, ROWID

      FROM xxc_stg1_table

      WHERE prl_name = p_prl_name

      AND currency_code = p_currency_code

      AND xxc_status IS NULL;

-- Cursor to fetch line level records from staging table.

CURSOR c_lines (p_prl_name IN VARCHAR2, p_line_type IN VARCHAR2)

   IS

      SELECT   UPPER (item_category) item_category,

               UPPER (item_cat_number) item_cat_number, uom_code,

               prl_action_code, VALUE, start_date, end_date, line_type,

               UPPER (price_break_type) price_break_type, value_from,

               value_to, comments, description, global_flag,

               effective_start_date, secondary_price_list, precedence,

               line_precedence, effective_end_date, attribute1, attribute2,

               attribute3, attribute4, attribute5, attribute6, attribute7,

               attribute8, attribute9, attribute10, attribute11, attribute12,

               attribute13, attribute14, attribute15, ROWID

      FROM xxc_stg1_table

      WHERE prl_name = p_prl_name

      AND line_type = p_line_type

      AND xxc_status IS NULL

      ORDER BY item_category,

               item_cat_number,

               start_date,

               end_date,

               price_break_type,

               value_from;

--Validation

1)     Validation for Customer order flag filed:

      SELECT msi.inventory_item_id, msi.customer_order_flag

        INTO l_inventory_item_id, l_customer_order_flag

        FROM mtl_system_items msi, oe_system_parameters_all osp

        WHERE msi.organization_id = osp.master_organization_id

        AND msi. segment1 = p_item_cat_number

        AND msi.organization_id = fnd_profile.VALUE ('ORG_ID');

2)     Validation of Item Category:

        SELECT mc.category_id

        INTO l_category_id

        FROM mtl_category_sets mcs, mtl_categories mc

        WHERE mcs.category_set_name = 'INVENTORY'

        AND mc.structure_id = mcs.structure_id

        AND mc.segment1 = p_item_cat_number;

3)     Validation for UOM code :

        SELECT uom_code

        INTO l_uom_code

        FROM mtl_item_uoms_view miu, oe_system_parameters_all osp

        WHERE inventory_item_id = p_inventory_item_id

        AND organization_id = osp.master_organization_id

        AND uom_code = p_uom_code

        AND miu.organization_id = fnd_profile.VALUE ('ORG_ID');

4)     Validation of UOM_CODE for Category

       SELECT DISTINCT uom_code

       INTO l_uom_code

       FROM mtl_item_uoms_view miu, oe_system_parameters osp

       WHERE organization_id = osp.master_organization_id

       AND uom_code = p_uom_code

       AND inventory_item_id IN (

                                            SELECT inventory_item_id

                                            FROM mtl_item_categories, oe_system_parameters osp

                                            WHERE category_id = p_category_id

                                             AND organization_id = osp.master_organization_id

        );

5)     Validation for Org:

      SELECT organization_id

      INTO ln_org_id

      FROM hr_operating_units

      WHERE NAME = c_hdr_details_rec.operating_unit;

Record type should be taken as below:

-- Header

      gpr_price_list_rec            qp_price_list_pub.price_list_rec_type;

      ppr_price_list_rec            qp_price_list_pub.price_list_rec_type;

      ppr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;

      -- Line

      gpr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;

      ppr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;

      ppr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;

    

 -- Qualifiers

      gpr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;

      ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;

      gpr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;

      ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;

      gpr_qualifier_rules_rec       qp_qualifier_rules_pub.qualifier_rules_rec_type;

      ppr_qualifier_rules_rec       qp_qualifier_rules_pub.qualifier_rules_rec_type;

      gpr_qualifier_rules_val_rec   qp_qualifier_rules_pub.qualifier_rules_val_rec_type;

      ppr_qualifier_rules_val_rec   qp_qualifier_rules_pub.qualifier_rules_val_rec_type;

      -- Attribute

      gpr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;

      ppr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;

      ppr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;

Calling API:

qp_price_list_pub.process_price_list

                    (p_api_version_number           => 1.0,

                     p_init_msg_list                => fnd_api.g_false,

                     p_return_values                => fnd_api.g_false,

                     p_commit                       => fnd_api.g_true,

                     x_return_status                => gpr_return_status,

                     x_msg_count                    => gpr_msg_count,

                     x_msg_data                     => gpr_msg_data,

                     p_price_list_rec               => gpr_price_list_rec,

                     p_price_list_line_tbl          => gpr_price_list_line_tbl,

                     p_pricing_attr_tbl             => gpr_pricing_attr_tbl,

                     x_price_list_rec               => ppr_price_list_rec,

                     x_price_list_val_rec           => ppr_price_list_val_rec,

                     x_price_list_line_tbl          => ppr_price_list_line_tbl,

                     x_price_list_line_val_tbl      => ppr_price_list_line_val_tbl,

                     x_qualifiers_tbl               => ppr_qualifiers_tbl,

                     x_qualifiers_val_tbl           => ppr_qualifiers_val_tbl,

                     x_pricing_attr_tbl             => ppr_pricing_attr_tbl,

                     x_pricing_attr_val_tbl         => ppr_pricing_attr_val_tbl,

                     p_check_duplicate_lines        => 'N'

                    );

Run below program to load data form staging to base table using API.

Error:

1)     Line level start date is earlier that header level start date. For this we added date validate on staging table.by using below query.

-- Added below additional code to validate the date Overlaps

         SELECT COUNT (1)

         INTO l_count_stg

         FROM xxc_stg1_table xls

         WHERE xls.item_cat_number = c_lines_rec.item_cat_number

         AND xls.uom_code = c_lines_rec.uom_code

         AND xls.prl_name = p_prl_name

         AND xls.line_type = c_lines_rec.line_type

         AND NVL (xls.value_from, -9) = NVL (c_lines_rec.value_from, -9)

         AND NVL (xls.value_to, -9) = NVL (c_lines_rec.value_to, -9);

2)     Line level end date is greater than header level end date.

Eg:

For this we need to do data clean up activity in 11i instance. So that we can bring valid records in R12 system.

After Data clean-up activity:

3)     Item not available against given organization.

        This is most typical error will get when loading price lines.

If QP ITEM VALIDATION is set to XXA Operating Unit and that item is not assigned to that XXA Operating Unit then this Item not available against given organization error will occurred.

Price List Extraction Query

SELECT 'New' PRL_ACTION_CODE
       ,t.name prl_name
       ,qha.currency_code
       ,hou.NAME operating_unit
       ,global_flag
       ,t.description
       ,TO_CHAR (qha.start_date_active, 'DD-MON-YYYY') effective_start_date
       ,TO_CHAR (qha.end_date_active, 'DD-MON-YYYY') effective_end_date
       ,qha.comments
       ,'ITEM' item_category
       ,(SELECT concatenated_segments FROM apps.mtl_system_items_vl  WHERE inventory_item_id = TO_NUMBER (qppr.product_attr_value) AND ROWNUM = 1) item_cat_number
       ,qppr.product_uom_code uom_code
       ,qpll.list_line_type_code line_type
       ,qpll.price_break_type_code price_break_type
   ,CASE
          WHEN qpll.list_line_type_code = 'PLL'
            THEN qpll.pricing_attr_value_from
          ELSE qpbv.pricing_attr_value_from
        END value_from
   ,CASE
          WHEN qpll.list_line_type_code = 'PLL'
            THEN qpll.pricing_attr_value_to
          ELSE qpbv.pricing_attr_value_to
        END value_to
       ,CASE
          WHEN qpll.list_line_type_code = 'PLL'
            THEN qpll.operand
          ELSE qpbv.operand
        END value
       ,TO_CHAR (NVL(qpll.start_date_active,qha.start_date_active), 'DD-MON-YYYY') start_date
       ,TO_CHAR (qpll.end_date_active, 'DD-MON-YYYY') end_date
       ,NVL (qpll.product_precedence, 220) line_precedence
       ,qppr.attribute1
       ,qppr.attribute2
       ,qppr.attribute3
       ,qppr.attribute4
       ,qppr.attribute5
       ,qppr.attribute6
       ,qppr.attribute7
       ,qppr.attribute8
       ,qppr.attribute9
       ,qppr.attribute10
       ,qppr.attribute11
       ,qppr.attribute12
       ,qppr.attribute13
       ,qppr.attribute14
       ,qppr.attribute15
       ,SYSDATE
       ,1
       ,SYSDATE
       ,1
       ,1
       , (SELECT OOD.ORGANIZATION_CODE
          FROM apps.ORG_ORGANIZATION_DEFINITIONS  OOD
          WHERE OOD.organization_id = hou.organization_id ) ORG_CODE
FROM apps.QP_LIST_HEADERS_B   qha
      , apps.qp_list_lines_v  qpll
      , apps.qp_list_headers_tl  t
      , apps.qp_pricing_attributes  qppr
  ,apps.qp_price_breaks_v  qpbv
      , apps.HR_OPERATING_UNITs  hou
WHERE qha.LIST_HEADER_ID             =      qpll.LIST_HEADER_ID
AND qha.list_type_code               =      'PRL'
AND qha.list_header_id               =      t.list_header_id
AND qppr.list_line_id                =      qpll.list_line_id
AND qpll.list_line_type_code         in      ('PLL','PBH')
and qpll.PRODUCT_ATTRIBUTE_CONTEXT   =      'ITEM'
AND qpll.product_attribute           =      'PRICING_ATTRIBUTE1'
AND t.LANGUAGE                       =     USERENV ('LANG')
AND hou.organization_id (+)          =       qha.orig_org_id
--AND hou.organization_id            =      242
AND qha.active_flag                  =       'Y'
AND qpbv.parent_list_line_id (+) = qpll.list_line_id
AND t.name                           ='XX_PRICE_LIST_NAME'
AND ( trunc(nvl(qha.end_date_active,sysdate)) >= trunc(sysdate) OR (trunc(to_date('15-12-2013','DD/MM/YYYY')) <= trunc(NVL(qpll.end_date_active,sysdate)) ));

WIP JOB Extraction Query

SELECT  WIP_ENTITY_NAME
,PRIMARY_ITEM_NAME
,ITEM_DESCRIPTION 
,SOURCE_CODE
,FIRM_PLANNED_FLAG
,STATUS_TYPE
,START_QUANTITY
,NET_QUANTITY
,CLASS_CODE
,ORGANIZATION_CODE
,ALTERNATE_BOM_DESIGNATOR
,BOM_REVISION
,SUPPLY_TYPE
,BOM_REVISION_DATE
,ALTERNATE_ROUTING_DESIGNATOR
,ROUTING_REVISION
,ROUTING_REVISION_DATE
,SERIALIZATION_START_OP
,COMPLETION_SUBINVENTORY
,COMPLETION_LOCATOR
,DATE_RELEASED
,SCHEDULE_GROUP_NAME
,LINE_CODE
,PROJECT_NUMBER
,TASK_NUMBER
,DUE_DATE
,PRIORITY
,DUE_DATE_PENALTY
,DUE_DATE_TOLERANCE
,DEMAND_CLASS
,LOT_NUMBER
,OVERCOMPLETION_TOLERANCE_TYPE
,OVERCOMPLETION_TOLERANCE_VALUE
,FIRST_UNIT_START_DATE
,FIRST_UNIT_COMPLETION_DATE
,LAST_UNIT_START_DATE
,LAST_UNIT_COMPLETION_DATE
,SHUTDOWN_TYPE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,DEPARTMENT_CODE
,PROCESSING_WORK_DAYS
,DAILY_PRODUCTION_RATE
,SCHEDULED_COMPLETION_DATE
,SCHEDULED_START_DATE
,BUILD_SEQUENCE
FROM
(SELECT distinct WE.WIP_ENTITY_NAME
      ,(SELECT CONCATENATED_SEGMENTS
        FROM mtl_system_items_kfv
        WHERE inventory_item_id = wdj.primary_item_id
        AND ORGANIZATION_ID = WDJ.ORGANIZATION_ID) PRIMARY_ITEM_NAME
      ,(SELECT DESCRIPTION
        FROM MTL_SYSTEM_ITEMS_B
        WHERE INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
        AND ORGANIZATION_ID = WDJ.ORGANIZATION_ID) ITEM_DESCRIPTION 
      ,WDJ.SOURCE_CODE
      ,WDJ.FIRM_PLANNED_FLAG
      ,WDJ.STATUS_TYPE
      ,DECODE (  wdj.start_quantity
                 - wdj.quantity_completed
                 - wdj.quantity_scrapped, 0, NULL,
                   wdj.start_quantity
                 - wdj.quantity_completed
                 - wdj.quantity_scrapped
                ) START_QUANTITY
      ,WDJ.NET_QUANTITY
      ,WDJ.SCHEDULED_START_DATE
      ,WDJ.SCHEDULED_COMPLETION_DATE
      ,WDJ.CLASS_CODE
      ,OOD.ORGANIZATION_CODE
      --BOM---------------
      ,WDJ.ALTERNATE_BOM_DESIGNATOR
      ,WDJ.BOM_REVISION
      ,WDJ.WIP_SUPPLY_TYPE SUPPLY_TYPE
      ,WDJ.BOM_REVISION_DATE
      --ROUTING
      ,WDJ.ALTERNATE_ROUTING_DESIGNATOR
      ,WDJ.ROUTING_REVISION
      ,WDJ.ROUTING_REVISION_DATE
      ,WDJ.SERIALIZATION_START_OP
      ,WDJ.COMPLETION_SUBINVENTORY
      ,(SELECT CONCATENATED_SEGMENTS
        FROM MTL_ITEM_LOCATIONS_KFV  IL
        WHERE IL.INVENTORY_LOCATION_ID = WDJ.COMPLETION_LOCATOR_ID) COMPLETION_LOCATOR
      --JOB HISTORY-----
      ,WDJ.DATE_RELEASED
      --SCHEDULE GROUP-----
      ,WSG.SCHEDULE_GROUP_NAME
      ,WL.LINE_CODE
  ,BUILD_SEQUENCE
      ,APPS.PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER
      ,APPS.PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID)TASK_NUMBER
      --SCHEDULING-------------
      ,WDJ.DUE_DATE
      ,WDJ.PRIORITY
      ,WDJ.DUE_DATE_PENALTY
      ,WDJ.DUE_DATE_TOLERANCE
      --MORE---------------
      ,WDJ.DEMAND_CLASS
      ,WDJ.LOT_NUMBER
      ,WDJ.OVERCOMPLETION_TOLERANCE_TYPE
      ,WDJ.OVERCOMPLETION_TOLERANCE_VALUE
      --OPERATIONS------
        --MAIN
        --QUANTITY
        --DATES
      ,WO.FIRST_UNIT_START_DATE
      ,WO.FIRST_UNIT_COMPLETION_DATE
      ,WO.LAST_UNIT_START_DATE
      ,WO.LAST_UNIT_COMPLETION_DATE
      --DESCRIPTION
      ,MFG3.MEANING SHUTDOWN_TYPE
      ,WDJ.ATTRIBUTE_CATEGORY
      ,WDJ.ATTRIBUTE1
      ,WDJ.ATTRIBUTE2
      ,WDJ.ATTRIBUTE3
      ,WDJ.ATTRIBUTE4
      ,WDJ.ATTRIBUTE5
      ,WDJ.ATTRIBUTE6
      ,WDJ.ATTRIBUTE7
      ,WDJ.ATTRIBUTE8
      ,WDJ.ATTRIBUTE9
      ,WDJ.ATTRIBUTE10
      ,WDJ.ATTRIBUTE11
      ,WDJ.ATTRIBUTE12
      ,WDJ.ATTRIBUTE13
      ,WDJ.ATTRIBUTE14
      ,WDJ.ATTRIBUTE15
      ---LINE LEVEL DETAILS
      ,BD.DEPARTMENT_CODE
      --SUPPLY
      ,WRS.PROCESSING_WORK_DAYS
      ,WRS.DAILY_PRODUCTION_RATE
FROM APPS.WIP_DISCRETE_JOBS  WDJ,
     APPS.WIP_ENTITIES  WE,
     APPS.HR_ALL_ORGANIZATION_UNITS  HAOU,
     APPS.ORG_ORGANIZATION_DEFINITIONS  OOD,
     APPS.WIP_REQUIREMENT_OPERATIONS  WRO,
     APPS.WIP_SCHEDULE_GROUPS  WSG,
     APPS.MFG_LOOKUPS  MFG,
     APPS.MFG_LOOKUPS  MFG1,
     APPS.MFG_LOOKUPS  MFG2,
     APPS.BOM_DEPARTMENTS  BD,
     APPS.BOM_STANDARD_OPERATIONS  BSO,
     APPS.MFG_LOOKUPS  ML1,
     APPS.WIP_LINES  WL,
     APPS.MTL_KANBAN_CARDS  KC,
     APPS.WIP_OPERATIONS  WO,
     APPS.MFG_LOOKUPS  MFG3,
     APPS.WIP_OPERATION_RESOURCES  WOR,
     APPS.WIP_REPETITIVE_SCpHEDULES  WRS,
     APPS.CST_ACTIVITIES  CA,
     APPS.BOM_RESOURCES  BR
WHERE 1=1
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
--AND WE.WIP_ENTITY_NAME =
AND OOD.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND OOD.OPERATING_UNIT = HAOU.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND BSO.STANDARD_OPERATION_ID(+) = WO.STANDARD_OPERATION_ID
AND NVL(BSO.OPERATION_TYPE,1)    = 1
AND BSO.LINE_ID IS NULL
AND MFG.LOOKUP_TYPE = 'WIP_DISCRETE_JOB'
AND MFG.LOOKUP_CODE = WDJ.JOB_TYPE
AND MFG1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND MFG1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND MFG2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND MFG2.LOOKUP_CODE = WDJ.WIP_SUPPLY_TYPE
AND MFG3.LOOKUP_TYPE(+)='BOM_EAM_SHUTDOWN_TYPE'
AND MFG3.LOOKUP_CODE(+)= WO.SHUTDOWN_TYPE
--
AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID
AND KC.KANBAN_CARD_ID (+)= WDJ.KANBAN_CARD_ID
AND WL.LINE_ID (+)= WDJ.LINE_ID
--
AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND ML1.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
AND ML1.LOOKUP_TYPE = 'WIP_SUPPLY'
--AND OOD.ORGANIZATION_CODE in ('NA')
AND WDJ.STATUS_TYPE in (1,3)
--RESOURCE CONDITIONS
AND CA.ACTIVITY_ID(+) = WOR.ACTIVITY_ID
AND BR.RESOURCE_ID  = WOR.RESOURCE_ID
AND WO.WIP_ENTITY_ID (+) = WOR.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM (+) = WOR.OPERATION_SEQ_NUM
AND NVL(WO.REPETITIVE_SCHEDULE_ID,-1) = NVL(WOR.REPETITIVE_SCHEDULE_ID,-1)
AND WRS.ORGANIZATION_ID(+) = WOR.ORGANIZATION_ID
AND WRS.WIP_ENTITY_ID(+) = WOR.WIP_ENTITY_ID
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WOR.REPETITIVE_SCHEDULE_ID);