ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN CF0A6672722347719EC7B03D9F96DA52Y select null action_, null status_, null message_, null modified_columns_, qslhv.name price_list, qslhv.currency_code currency, qslhv.description, qslhv.start_date_active effective_from, qslhv.end_date_active effective_to, to_char(null) item_from, to_char(null) item_to, to_char(null) category_set, to_char(null) category, to_char(null) include_discounts, to_char(null) retain_effective_dates, to_char(null) new_price_list, to_char(null) new_description, to_date(null) new_effective_from, to_date(null) new_effective_to, xxen_util.yes(decode(hou.organization_id,null,'Y')) new_global, hou.name new_operating_unit, to_char(null) adjust_item_status, to_date(null) adjust_creation_date, xxen_util.meaning('%','ARITHMETIC_OPERATOR',661) adjust_by, to_number(null) adjust_value, null upload_row from qp_secu_list_headers_vl qslhv, (select hou.organization_id, hou.name from hr_operating_units hou where hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU')) hou where :p_upload_mode like '%'||xxen_upload.action_update and 1=1 and qslhv.list_type_code in ('PRL','AGR') and nvl(qslhv.pte_code,'ORDFUL')<>'LOGSTX' and nvl(qslhv.list_source_code,'X')<>'BSO' and qslhv.view_flag='Y' and nvl(fnd_profile.value('QP_PRICING_TRANSACTION_ENTITY'),'QP')=nvl(qslhv.pte_code,'QP') and qslhv.orig_org_id=hou.organization_id(+) Initial version: copies price lists and adjusts their prices as the Copy Price List and Adjust Price List windows, and end-dates the price list of the row. U A xxen_qp_upload.upload_price_list_copy_adjust US QP Price List Copy and Adjust Upload Copies price lists and adjusts their prices, one row per price list, as the Copy Price List and Adjust Price List windows of Advanced Pricing do. Upload Mode Create, Update downloads the selected price lists; Create gives an empty sheet to paste rows into. Enter a New Price List to copy the row's price list with its qualifiers and the lines active today. Adjust By and Adjust Value then change the prices of the new list, or of the row's own list when no New Price List is entered. Standard and agreement price lists can be copied, and the copy keeps the type of its source. A copied agreement price list gets no qualifiers and is not attached to an agreement. Prices can be adjusted on standard price lists only, as in the Adjust Price Lists window. Change Effective To to end-date the row's price list, for example the day before the new list starts. Only a price list without an end date can be end-dated: an existing end date cannot be changed or removed, and a blank Effective To leaves the price list unchanged. Item From, Item To and Category limit both the copy and the adjustment; Adjust Item Status and Adjust Creation Date limit the adjustment only. With any of these filters only lines priced by item number are copied or adjusted; lines priced by item category or all items are left out. The copy and the adjustment are saved as soon as they run. A row that fails afterwards keeps the new price list and its message says so: adjust the new list in a separate row instead of uploading the row again. Enginatics R12 only Upload 1=1 :p_upload_mode 1 10 :p_upload_mode LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 1=1 qslhv.list_header_id=:list_header_id LOV custom select qslhv.list_header_id id, qslhv.name value, qslhv.description from qp_secu_list_headers_vl qslhv where qslhv.list_type_code in ('PRL','AGR') and nvl(qslhv.pte_code,'ORDFUL')<>'LOGSTX' and nvl(qslhv.list_source_code,'X')<>'BSO' and qslhv.view_flag='Y' and nvl(fnd_profile.value('QP_PRICING_TRANSACTION_ENTITY'),'QP')=nvl(qslhv.pte_code,'QP') order by 2 US Price List 3 30 1=1 qslhv.name like :price_list_like Char US Price List like 4 40 1=1 qslhv.name>=:price_list_from LOV custom Y select qslhv.name value, qslhv.description from qp_secu_list_headers_vl qslhv where qslhv.list_type_code in ('PRL','AGR') and nvl(qslhv.pte_code,'ORDFUL')<>'LOGSTX' and nvl(qslhv.list_source_code,'X')<>'BSO' and qslhv.view_flag='Y' and nvl(fnd_profile.value('QP_PRICING_TRANSACTION_ENTITY'),'QP')=nvl(qslhv.pte_code,'QP') order by 1 US Price List From Downloads the price lists whose names are from Price List From to Price List To, compared as text. 5 50 1=1 qslhv.name<=:price_list_to LOV custom Y select qslhv.name value, qslhv.description from qp_secu_list_headers_vl qslhv where qslhv.list_type_code in ('PRL','AGR') and nvl(qslhv.pte_code,'ORDFUL')<>'LOGSTX' and nvl(qslhv.list_source_code,'X')<>'BSO' and qslhv.view_flag='Y' and nvl(fnd_profile.value('QP_PRICING_TRANSACTION_ENTITY'),'QP')=nvl(qslhv.pte_code,'QP') order by 1 US Price List To 6 60 1=1 nvl(qslhv.start_date_active,:effective_date)<=:effective_date and nvl(qslhv.end_date_active,:effective_date)>=:effective_date Date US Effective Date Downloads the price lists effective on this date. BF6621DE112049F19A3133617DFAD034 Adjust Price List Only Adjust the prices of existing price lists. ENGINATICS 12 ADJUST_BY 11 ADJUST_CREATION_DATE 10 ADJUST_ITEM_STATUS 13 ADJUST_VALUE 9 CATEGORY 8 CATEGORY_SET 2 CURRENCY Y 3 DESCRIPTION 4 EFFECTIVE_FROM 5 EFFECTIVE_TO Y 6 ITEM_FROM 7 ITEM_TO 1 PRICE_LIST S Site 5FBC8E9BB8E97ADCE0630100007F8C47 Copy Price List Only Copy a Price List ENGINATICS 9 CATEGORY 8 CATEGORY_SET 2 CURRENCY Y 3 DESCRIPTION 4 EFFECTIVE_FROM 5 EFFECTIVE_TO Y 10 INCLUDE_DISCOUNTS 6 ITEM_FROM 7 ITEM_TO 13 NEW_DESCRIPTION 14 NEW_EFFECTIVE_FROM 15 NEW_EFFECTIVE_TO 16 NEW_GLOBAL 17 NEW_OPERATING_UNIT 12 NEW_PRICE_LIST 1 PRICE_LIST 11 RETAIN_EFFECTIVE_DATES S Site 244E5685F156441999E16F3856155F65 Default Copy and/or Adjust price lists. Optionally End Date the source price list. ENGINATICS 18 ADJUST_BY 19 ADJUST_VALUE 9 CATEGORY 8 CATEGORY_SET 2 CURRENCY Y 3 DESCRIPTION 4 EFFECTIVE_FROM 5 EFFECTIVE_TO 10 INCLUDE_DISCOUNTS 6 ITEM_FROM 7 ITEM_TO 13 NEW_DESCRIPTION 14 NEW_EFFECTIVE_FROM 15 NEW_EFFECTIVE_TO 16 NEW_GLOBAL 17 NEW_OPERATING_UNIT 12 NEW_PRICE_LIST 1 PRICE_LIST 11 RETAIN_EFFECTIVE_DATES S Site 244E5685F156441999E16F3856155F65 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 PRICE_LIST LOV custom varchar2 select qslhv.list_header_id id, qslhv.name value, qslhv.description from qp_secu_list_headers_vl qslhv where qslhv.list_type_code in ('PRL','AGR') and nvl(qslhv.pte_code,'ORDFUL')<>'LOGSTX' and nvl(qslhv.list_source_code,'X')<>'BSO' and qslhv.view_flag='Y' and nvl(fnd_profile.value('QP_PRICING_TRANSACTION_ENTITY'),'QP')=nvl(qslhv.pte_code,'QP') order by 2 Y Y 6 CURRENCY Char varchar2 select qlhv.currency_code from qp_list_headers_vl qlhv where qlhv.name=:$column$.price_list and qlhv.list_type_code in ('PRL','AGR') Y 7 DESCRIPTION Char varchar2 select qlhv.description from qp_list_headers_vl qlhv where qlhv.name=:$column$.price_list and qlhv.list_type_code in ('PRL','AGR') Y 8 EFFECTIVE_FROM Date date select qlhv.start_date_active from qp_list_headers_vl qlhv where qlhv.name=:$column$.price_list and qlhv.list_type_code in ('PRL','AGR') Y 9 EFFECTIVE_TO Date date select qlhv.end_date_active from qp_list_headers_vl qlhv where qlhv.name=:$column$.price_list and qlhv.list_type_code in ('PRL','AGR') Change it to end-date the source price list, for example the day before the new price list starts. Only a price list without an end date can be end-dated. 10 ITEM_FROM LOV custom varchar2 Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=qp_util.get_item_validation_org order by msiv.concatenated_segments Limits the copy and the adjustment, whichever the row performs, to the lines of items from Item From to Item To, compared segment by segment. Lines priced by item category or all items are then left out. 11 ITEM_TO LOV custom varchar2 Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=qp_util.get_item_validation_org order by msiv.concatenated_segments Last item of the range. Applies to the copy and the adjustment. 12 CATEGORY_SET LOV custom varchar2 select mcsv.category_set_id id, mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where mcsv.category_set_id in (select mic.category_set_id from mtl_item_categories mic where mic.organization_id=qp_util.get_item_validation_org) order by mcsv.category_set_name Category set of Category. Applies to the copy and the adjustment. 13 CATEGORY LOV custom varchar2 select mck.category_id id, mck.concatenated_segments value, mcv.description from mtl_category_sets_vl mcsv, mtl_categories_kfv mck, mtl_categories_vl mcv where mcsv.category_set_name=:$column$.category_set and mcsv.structure_id=mck.structure_id and mck.category_id=mcv.category_id and nvl(mcv.disable_date,sysdate+1)>sysdate and (nvl(mcsv.hierarchy_enabled,'N')<>'Y' or 'I'=(select fpi.status from fnd_product_installations fpi where fpi.application_id=661)) order by mck.concatenated_segments Limits the copy and the adjustment, whichever the row performs, to the lines of items in this category. Lines priced by item category or all items are then left out. 14 INCLUDE_DISCOUNTS LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Copies the discount lists (modifier type Discount List) qualified by the price list, which exist only for price lists carried over from Release 11. Modifiers created in Release 11i or later are never copied, so leave it blank otherwise. 15 RETAIN_EFFECTIVE_DATES LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Keeps the effective dates of the copied lines. 16 NEW_PRICE_LIST Char varchar2 Name of the new price list. Entering it copies this row's price list with the lines active today, and with its qualifiers when it is a standard price list. 17 NEW_DESCRIPTION Char varchar2 18 NEW_EFFECTIVE_FROM Date date 19 NEW_EFFECTIVE_TO Date date 20 NEW_GLOBAL LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual select xxen_util.yes(decode(hou.organization_id,null,'Y')) from qp_list_headers_vl qlhv, (select hou.organization_id from hr_operating_units hou where hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU')) hou where qlhv.name=:$column$.price_list and qlhv.list_type_code in ('PRL','AGR') and qlhv.orig_org_id=hou.organization_id(+) Global flag of the new price list. 21 NEW_OPERATING_UNIT LOV custom varchar2 select hou.organization_id id, hou.name value, null description from hr_operating_units hou where :$column$.new_global is null and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name select hou.name from qp_list_headers_vl qlhv, hr_operating_units hou where :$column$.new_global is null and qlhv.name=:$column$.price_list and qlhv.list_type_code in ('PRL','AGR') and qlhv.orig_org_id=hou.organization_id and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') Operating unit of the new price list, when it is not global. 22 ADJUST_ITEM_STATUS LOV custom varchar2 select mis.inventory_item_status_code value, mis.description from mtl_item_status mis where nvl(mis.disable_date,sysdate+1)>sysdate order by mis.inventory_item_status_code Adjusts only the lines of items with this status. Applies to the adjustment only. 23 ADJUST_CREATION_DATE Date date Adjusts only the price list lines created on this date. 24 ADJUST_BY LOV custom varchar2 select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='ARITHMETIC_OPERATOR' and flvv.view_application_id=661 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code in ('%','AMT') order by flvv.meaning desc xxen_util.meaning('%','ARITHMETIC_OPERATOR',661) 25 ADJUST_VALUE Number number Percent or amount to add to the list prices of the new price list, or of this price list when no New Price List is entered. 26 UPLOAD_ROW Char varchar2 Y U 10 status_ out Char STATUS_CODE_ U 20 message_ out Char MESSAGE_ U 30 p_list_header_id in Number PRICE_LIST U 40 p_effective_to in Date Y EFFECTIVE_TO U 50 p_item_from in Char Y ITEM_FROM U 60 p_item_to in Char Y ITEM_TO U 70 p_category_set_id in Number Y CATEGORY_SET U 80 p_category_id in Number Y CATEGORY U 90 p_include_discounts in Char Y INCLUDE_DISCOUNTS U 100 p_retain_effective_dates in Char Y RETAIN_EFFECTIVE_DATES U 110 p_new_price_list in Char Y NEW_PRICE_LIST U 120 p_new_description in Char Y NEW_DESCRIPTION U 130 p_new_effective_from in Date Y NEW_EFFECTIVE_FROM U 140 p_new_effective_to in Date Y NEW_EFFECTIVE_TO U 150 p_new_global in Char Y NEW_GLOBAL U 160 p_new_org_id in Number Y NEW_OPERATING_UNIT U 170 p_adjust_item_status in Char Y ADJUST_ITEM_STATUS U 180 p_adjust_creation_date in Date Y ADJUST_CREATION_DATE U 190 p_adjust_by in Char Y ADJUST_BY U 200 p_adjust_value in Number Y ADJUST_VALUE Error SQL Order By upload_row, price_list Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.price_list, nvl(qlhv.currency_code,xu.currency) currency, nvl(qlhv.description,xu.description) description, nvl(qlhv.start_date_active,xu.effective_from) effective_from, nvl(qlhv.end_date_active,xu.effective_to) effective_to, xu.item_from, xu.item_to, xu.category_set, xu.category, xu.include_discounts, xu.retain_effective_dates, xu.new_price_list, nvl(qlhv_new.description,xu.new_description) new_description, nvl(qlhv_new.start_date_active,xu.new_effective_from) new_effective_from, nvl(qlhv_new.end_date_active,xu.new_effective_to) new_effective_to, nvl2(qlhv_new.list_header_id,xxen_util.yes(qlhv_new.global_flag),xu.new_global) new_global, nvl2(qlhv_new.list_header_id,(select hou.name from hr_operating_units hou where qlhv_new.orig_org_id=hou.organization_id),xu.new_operating_unit) new_operating_unit, xu.adjust_item_status, xu.adjust_creation_date, xu.adjust_by, xu.adjust_value, xu.upload_row from xxen_qp_price_list_copy_7032_u xu, (select qlhv.name, qlhv.currency_code, qlhv.description, qlhv.start_date_active, qlhv.end_date_active from qp_list_headers_vl qlhv where qlhv.list_type_code in ('PRL','AGR')) qlhv, qp_list_headers_vl qlhv_new where xu.status_code_=xxen_upload.status_success and xu.price_list=qlhv.name(+) and xu.new_price_list=qlhv_new.name(+)