Freight Carrier Extract query(link between WSH_CARRIERS and WSH_CARRIER_SERVICES)

SELECT  -- WC.CARRIER_ID,
         HP.PARTY_NAME NAME,
         WC.FREIGHT_CODE SHORT_NAME,
         HP.STATUS ACTIVE_FLAG,
         WC.SCAC_CODE,
         WC.CURRENCY_CODE DEFAULT_CURRENCY,
         WC.GENERIC_FLAG,
         FLV.MEANING SERVICE_LEVEL,
         WCS.MODE_OF_TRANSPORT,
         FL.MEANING SHIP_METHOD_MEANING,
         WCS.SL_TIME_UOM,
         WCS.MIN_SL_TIME,
         WCS.MAX_SL_TIME,
         WCS.ENABLED_FLAG,
         WCS.WEB_ENABLED,
         WC.ATTRIBUTE1||WC.ATTRIBUTE2||WC.ATTRIBUTE3||WC.ATTRIBUTE4||WC.ATTRIBUTE5||WC.ATTRIBUTE6||WC.ATTRIBUTE7||WC.ATTRIBUTE8||WC.ATTRIBUTE9||WC.ATTRIBUTE10||WC.ATTRIBUTE11||WC.ATTRIBUTE12||WC.ATTRIBUTE13||WC.ATTRIBUTE14||WC.ATTRIBUTE15 DFF,
         WCSM.SITE_NUMBER,
         WCSM.LOCATION ADDRESS,
         WCSM.ACTIVE SITE_ACTIVE_FLAG,
         WCC.PERSON_LAST_NAME,
         WCC.PERSON_FIRST_NAME,
         WCC.PERSON_PRE_NAME_ADJUNCT TITLE,
         WCC.ACTIVE CONTACT_NAME_ACTIVE_FLAG,
         WCCI.COUNTRY_CODE,
         WCCI.AREA_CODE,
         WCCI.PHONE_NUMBER,
         WCCI.EXTENSION,
         WCCI.CONTACT_TYPE,
         WCCI.PRIMARY PRIMARY_FLAG,
         WCCI.ACTIVE CONTACT_TELE_ACTIVE_FLAG,
         WCCV.CARRIER_CLASS_CATEGORY CATEGORY,
         WCCV.ACTIVE CATEGORY_ENABLED_FLAG,
         WC.MANIFESTING_ENABLED_FLAG,
         WC.WEIGHT_UOM WEIGHT,
         WC.TIME_UOM TIME,
         WC.DIMENSION_UOM DIMENSION,
         WC.VOLUME_UOM VOLUME,
         WC.DISTANCE_UOM DISTANCE,
         PV.VENDOR_NAME SUPPLIER,
         PVS.VENDOR_SITE_CODE DEFAULT_SUPPLIER_SITE,
         WC.FREIGHT_BILL_AUTO_APPROVAL AUTO_APPROVE_BILLS_FLAG,
         WC.FREIGHT_AUDIT_LINE_LEVEL AUDIT_BILL_LINES_FLAG,
         WC.ORIGIN_DSTN_SURCHARGE_LEVEL SURCHARGE_LEVEL,
         WC.CM_FIRST_LOAD_DISCOUNT FIRST_LOAD_DISCOUNT_FLAG,
         WC.DISTANCE_CALCULATION_METHOD DISTANCE_CALCULATION,
         WC.CM_FREE_DH_MILEAGE FREE_DEADHEAD_DISTANCE,
         WC.MAX_OUT_OF_ROUTE "MAX_OUT_OF_ROUTE%",
         WC.MIN_CM_TIME MIN_TIME,
         WC.UNIT_RATE_BASIS,
         WC.MIN_CM_DISTANCE MIN_DISTANCE,
         WC.CM_RATE_VARIANT RATE_VARIANT,
         WC.ALLOW_INTERSPERSE_LOAD ALLOW_INTERSPERSAL_LOAD_FLAG,
         WC.ALLOW_CONTINUOUS_MOVE ALLOW_CONTINUOUS_MOVE_FLAG,
         WC.MAX_NUM_STOPS_PERMITTED MAX_NUMBER_OF_STOPS,
         WC.MAX_CM_TIME MAX_TIME,
         WC.MAX_TOTAL_DISTANCE,
         WC.MAX_CM_DISTANCE MAX_DISTANCE,
         WC.MAX_TOTAL_TIME,
         WC.MAX_CM_DH_DISTANCE MAX_DEADHEAD_DISTANCE,
         WC.MAX_CM_DH_TIME MAX_DEADHEAD_TIME,
         WC.MIN_LAYOVER_TIME,
         WC.MAX_TOTAL_DISTANCE_IN_24HR MAX_DISTANCE_PER_DAY,
         WC.MAX_LAYOVER_TIME,
         WC.MAX_DRIVING_TIME_IN_24HR MAX_DRIVING_TIME_PER_DAY,
         WC.MAX_DUTY_TIME_IN_24HR MAX_ON_DUTY_TIME_PER_DAY,
         WC.MIN_SIZE_LENGTH,
         WC.MAX_SIZE_LENGTH,
         WC.MIN_SIZE_HEIGHT,
         WC.MAX_SIZE_HEIGHT,
         WC.MIN_SIZE_WIDTH,
         WC.MAX_SIZE_WIDTH
  FROM   HZ_PARTIES HP,
         WSH_CARRIERS WC,
         WSH_CARRIER_SERVICES WCS,
         FND_LOOKUP_VALUES_VL FL,
         FND_LOOKUP_VALUES FLV,
         WSH_CARRIER_SITES_MAIN_V WCSM,
         WSH_CARRIER_CONTACTS_V WCC,
         HZ_PARTY_RELATIONSHIPS HPR,
         WSH_CARRIER_CONTACT_INFO_V WCCI,
         WSH_CARRIER_CLASSIFICATIONS_V WCCV,
         PO_VENDORS PV,
         PO_VENDOR_SITES_ALL PVS
 WHERE       WC.CARRIER_ID = HP.PARTY_ID
         AND WC.CARRIER_ID = WCS.CARRIER_ID
         AND FL.LOOKUP_TYPE = 'SHIP_METHOD'
         AND FL.LOOKUP_CODE = WCS.SHIP_METHOD_CODE
         AND FL.VIEW_APPLICATION_ID = 3
         AND FLV.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
         AND WCS.SERVICE_LEVEL = FLV.LOOKUP_CODE
         AND WC.CARRIER_ID = WCSM.CARRIER_ID(+)
         AND WC.CARRIER_ID = WCC.CARRIER_ID(+)
         AND WC.CARRIER_ID = HPR.OBJECT_ID(+)
         AND HPR.PARTY_ID = WCCI.RELATION_PARTY_ID(+)
         AND WC.CARRIER_ID = WCCV.PARTY_ID(+)
         AND WC.SUPPLIER_ID = PV.VENDOR_ID(+)
         AND WC.SUPPLIER_SITE_ID = PVS.VENDOR_SITE_ID(+)
         --AND HP.PARTY_NAME = 'PO- MERCER'

link between MTL_SYSTEM_ITEMS_B and MTL_ITEM_CATEGORIES_V

select msi.ORGANIZATION_ID,
msi.segment1,
msi.DESCRIPTION,
mst.LONG_DESCRIPTION,
msi.PRIMARY_UOM_CODE,
msi.ITEM_TYPE,
msi.INVENTORY_ITEM_STATUS_CODE,
miv.CATEGORY_CONCAT_SEGS HS_CATEGORY_CODE
 from  mtl_system_items_b msi ,
 MTL_SYSTEM_ITEMS_TL mst,
 MTL_ITEM_CATEGORIES_V miv
 where msi.inventory_item_id = mst.inventory_item_id
 and msi.organization_id = mst.organization_id
 and msi.inventory_item_id = miv.inventory_item_id
 and msi.organization_id = miv.organization_id
 and miv.CATEGORY_SET_NAME ='ENVS HS Code'
and msi.segment1 ='08180813' and msi.organization_id =104

API for Load the Price List (qp_price_list_pub.process_price_list)

SET SERVEROUTPUT ON;
DECLARE
  v_return_status             VARCHAR2(1) := NULL;
  v_msg_count                 NUMBER      := 0;
  v_msg_data                  VARCHAR2 (2000);
  v_price_list_rec            qp_price_list_pub.price_list_rec_type;
  v_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
  v_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
  v_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
  v_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
  v_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
  v_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
  v_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_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;
  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;
  ppr_qualifiers_tbl          qp_qualifier_rules_pub.qualifiers_tbl_type;
  ppr_qualifiers_val_tbl      qp_qualifier_rules_pub.qualifiers_val_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;

BEGIN
  v_price_list_rec.list_header_id             := 353500;
  v_price_list_rec.list_type_code             := 'PRL';
  v_price_list_rec.operation                  := qp_globals.g_opr_update;
 
  v_price_list_line_tbl(1).list_header_id     := 353500;
  v_price_list_line_tbl(1).list_line_id       := fnd_api.g_miss_num;
  v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
  v_price_list_line_tbl(1).operation          := qp_globals.g_opr_create;
  v_price_list_line_tbl(1).operand            := 51.6;
  v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
  v_price_list_line_tbl(1).start_date_active  := '01-NOV-2014';
  v_price_list_line_tbl(1).end_date_active  := '31-OCT-2015';
  v_price_list_line_tbl(1).PRODUCT_PRECEDENCE  :=220;
    v_price_list_line_tbl(1).organization_id    := NULL;
 
  v_pricing_attr_tbl(1).pricing_attribute_id  := fnd_api.g_miss_num;
  v_pricing_attr_tbl(1).list_line_id          := fnd_api.g_miss_num;
  v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
  v_pricing_attr_tbl(1).product_attribute     := 'PRICING_ATTRIBUTE1';
  v_pricing_attr_tbl(1).product_attr_value    := '1027096';
  v_pricing_attr_tbl(1).product_uom_code      := 'EA';   
  v_pricing_attr_tbl(1).excluder_flag         := 'N';
  v_pricing_attr_tbl(1).attribute_grouping_no := 1;
  v_pricing_attr_tbl(1).price_list_line_index := 1;
  v_pricing_attr_tbl(1).operation             := qp_globals.g_opr_create;
 
  dbms_output.put_line('Calling API to Enter Item Into Price List');
 
  qp_price_list_pub.process_price_list
      (
       p_api_version_number => 1
      ,p_init_msg_list      => fnd_api.g_true
      ,p_return_values      => fnd_api.g_false
      ,p_commit             => fnd_api.g_false
      ,x_return_status      => v_return_status
      ,x_msg_count          => v_msg_count
      ,x_msg_data           => v_msg_data
      ,p_price_list_rec     => v_price_list_rec
      ,p_price_list_line_tbl=> v_price_list_line_tbl
      ,p_pricing_attr_tbl   => v_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_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
      ,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
      );
    IF v_return_status = fnd_api.g_ret_sts_success THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull');
    ELSE
      DBMS_OUTPUT.put_line ('The Item loading into the price list Failed');
      ROLLBACK;
      FOR i IN 1 .. v_msg_count
      LOOP
        v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
        dbms_output.put_line( i|| ') '|| v_msg_data);
      END LOOP;
    END IF;
END; 
/