[Home] [Help]
3185: l_icx_schema_name := ICX_CAT_UTIL_PVT.getIcxSchemaName;
3186:
3187: l_err_loc := 290;
3188: EXECUTE IMMEDIATE
3189: 'TRUNCATE TABLE '|| l_icx_schema_name ||'.icx_cat_r12_upg_autosource';
3190:
3191: l_err_loc := 295;
3192: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3193: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3191: l_err_loc := 295;
3192: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3193: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3194: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3195: 'truncate table icx_cat_r12_upg_autosource done:');
3196: END IF;
3197:
3198: l_err_loc := 300;
3199: FORALL i IN 1..l_as_org_id_tbl.COUNT
3196: END IF;
3197:
3198: l_err_loc := 300;
3199: FORALL i IN 1..l_as_org_id_tbl.COUNT
3200: INSERT INTO icx_cat_r12_upg_autosource
3201: (org_id, supplier_id, supplier_site_id, currency,
3202: contract_id, po_category_id,
3203: last_update_login, last_updated_by, last_update_date,
3204: created_by, creation_date, internal_request_id, request_id,
3271: SUBSTRB(itemtlp.description, 1, 240) description, itemsb.catalog_name,
3272: upg.po_interface_header_id, upg.created_language, upg.po_interface_line_id,
3273: price.price_type, itemtlp.primary_category_name
3274: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
3275: icx_por_category_order_map map, icx_cat_r12_upg_autosource src,
3276: icx_cat_r12_upgrade upg
3277: WHERE price.price_type = 'BULKLOAD'
3278: AND price.rt_item_id = itemtlp.rt_item_id
3279: AND price.rt_item_id = itemsb.rt_item_id
3727: DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
3728: SUBSTRB(itemtlp.description, 1, 240) description,
3729: itemsb.catalog_name, price.price_type, itemtlp.primary_category_name
3730: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
3731: icx_por_category_order_map map, icx_cat_r12_upg_autosource src
3732: WHERE price.price_type = 'BULKLOAD'
3733: AND price.rt_item_id = itemtlp.rt_item_id
3734: AND price.rt_item_id = itemsb.rt_item_id
3735: AND NOT EXISTS (SELECT 'extracted price'
4083: END processGBPALines;
4084:
4085: PROCEDURE createGBPAsForBlkldItems
4086: IS
4087: -- Join with icx_cat_r12_upg_autosource on org_id, supplier_id,
4088: -- supplier_site_id, currency and po_category_id
4089: -- Group on org_id, supplier_id, supplier_site_id, currency, contract_id and language.
4090: -- Cannot group on po_category_id because the number of GBPA to be created will increase
4091: -- depending on po_category_id.
4102: SELECT src.org_id org_id, src.supplier_id supplier_id,
4103: src.supplier_site_id supplier_site_id, src.currency currency,
4104: src.contract_id gbpa_cpa_reference,
4105: itemtlp.language language
4106: FROM icx_cat_r12_upg_autosource src, icx_cat_item_prices price,
4107: icx_cat_items_tlp itemtlp, icx_por_category_order_map map
4108: WHERE price.price_type = 'BULKLOAD'
4109: AND price.rt_item_id = itemtlp.rt_item_id
4110: AND NOT EXISTS (SELECT 'extracted price'
4360: upg.po_header_id, upg.po_line_id, upg.created_language,
4361: upg.po_category_id old_po_catgegory_id,
4362: DECODE(attr.ATTRIBUTE_VALUES_TLP_ID, NULL, 'ADD', 'UPDATE') attr_val_tlp_action,
4363: price.price_type, itemtlp.primary_category_name
4364: FROM icx_cat_r12_upg_autosource src, icx_cat_item_prices price,
4365: icx_cat_items_tlp itemtlp, icx_por_category_order_map map,
4366: icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
4367: icx_cat_r12_upgrade upg, po_attribute_values_tlp attr
4368: WHERE price.price_type = 'BULKLOAD'
5163: itemtlp.language language,
5164: upg.po_interface_header_id,
5165: upg.po_header_id, upg.created_language,
5166: nvl(upg.cpa_reference, -2) upg_cpa_reference
5167: FROM icx_cat_r12_upg_autosource src, icx_cat_item_prices price,
5168: icx_cat_items_tlp itemtlp, icx_por_category_order_map map,
5169: icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
5170: icx_cat_r12_upgrade upg
5171: WHERE price.price_type = 'BULKLOAD'