[Home] [Help]
2068: l_err_loc := 120;
2069: OPEN p_blanketAndQuoteHdrs_csr FOR
2070: SELECT distinct p.contract_id po_header_id, p.price_type, p.org_id
2071: FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
2072: icx_cat_items_b itemsb, icx_cat_ext_items_tlp exttlp
2073: WHERE p.price_type IN ('BLANKET', 'QUOTATION')
2074: AND p.contract_id >= p_start_po_header_id
2075: AND itemtlp.rt_item_id = p.rt_item_id
2076: AND itemsb.rt_item_id = p.rt_item_id
2113: DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
2114: itemsb.catalog_name, itemtlp.primary_category_id,
2115: getPOAttrValuesTLPAction(p.contract_line_id, '-2' ,-2, p.org_id, itemtlp.language)
2116: FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
2117: icx_cat_items_b itemsb, po_headers_interface phi
2118: WHERE p.price_type IN ('BLANKET', 'QUOTATION')
2119: AND p.contract_line_id >= p_start_po_line_id
2120: AND itemtlp.rt_item_id = p.rt_item_id
2121: AND itemsb.rt_item_id = p.rt_item_id
2131: DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
2132: itemsb.catalog_name, itemtlp.primary_category_id,
2133: getPOAttrValuesTLPAction(p.contract_line_id, '-2' ,-2, p.org_id, itemtlp.language)
2134: FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
2135: icx_cat_items_b itemsb, icx_cat_ext_items_tlp exttlp, po_headers_interface phi
2136: WHERE p.price_type IN ('BLANKET', 'QUOTATION')
2137: AND p.contract_line_id >= p_start_po_line_id
2138: AND itemtlp.rt_item_id = p.rt_item_id
2139: AND itemsb.rt_item_id = p.rt_item_id
2497: l_err_loc := 200;
2498: OPEN p_reqTemplateHdrs_csr FOR
2499: SELECT distinct p.template_id, p.org_id
2500: FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
2501: icx_cat_items_b itemsb, icx_cat_ext_items_tlp exttlp
2502: WHERE p.price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
2503: AND p.template_id >= p_start_template_id
2504: AND itemtlp.rt_item_id = p.rt_item_id
2505: AND itemsb.rt_item_id = p.rt_item_id
2539: DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
2540: itemsb.catalog_name, itemtlp.primary_category_id,
2541: getPOAttrValuesTLPAction(-2, p.template_id, p.template_line_id, p.org_id, itemtlp.language)
2542: FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
2543: icx_cat_items_b itemsb, po_headers_interface phi
2544: WHERE p.price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
2545: AND p.rt_item_id >= p_start_rt_item_id
2546: AND itemtlp.rt_item_id = p.rt_item_id
2547: AND itemsb.rt_item_id = p.rt_item_id
2558: DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
2559: itemsb.catalog_name, itemtlp.primary_category_id,
2560: getPOAttrValuesTLPAction(-2, p.template_id, p.template_line_id, p.org_id, itemtlp.language)
2561: FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
2562: icx_cat_items_b itemsb, icx_cat_ext_items_tlp exttlp, po_headers_interface phi
2563: WHERE p.price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
2564: AND p.rt_item_id >= p_start_rt_item_id
2565: AND itemtlp.rt_item_id = p.rt_item_id
2566: AND itemsb.rt_item_id = p.rt_item_id
2971: NVL(map.external_source_key, '-2') po_category_id
2972: FROM icx_cat_item_prices price,
2973: icx_cat_items_tlp itemtlp,
2974: icx_por_category_order_map map,
2975: icx_cat_items_b itemb,
2976: icx_cat_ext_items_tlp extitemtlp,
2977: icx_cat_r12_upgrade upg
2978: WHERE price.price_type = 'BULKLOAD'
2979: AND price.rt_item_id = itemtlp.rt_item_id
3270: DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
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
3307: DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
3308: SUBSTRB(itemtlp.description, 1, 240) description, itemsb.catalog_name,
3309: upg.po_interface_header_id, upg.created_language, upg.po_interface_line_id,
3310: price.price_type, itemtlp.primary_category_name
3311: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
3312: icx_por_category_order_map map, icx_cat_r12_upgrade upg
3313: WHERE price.price_type = 'CONTRACT'
3314: AND price.rt_item_id = itemtlp.rt_item_id
3315: AND price.rt_item_id = itemsb.rt_item_id
3726: SUBSTRB(itemtlp.supplier_part_num, 1, 25) supplier_part_num,
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
3757: SUBSTRB(itemtlp.supplier_part_num, 1, 25) supplier_part_num,
3758: DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
3759: SUBSTRB(itemtlp.description, 1, 240) description,
3760: itemsb.catalog_name, price.price_type, itemtlp.primary_category_name
3761: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
3762: icx_por_category_order_map map
3763: WHERE price.price_type = 'CONTRACT'
3764: AND price.rt_item_id = itemtlp.rt_item_id
3765: AND price.rt_item_id = itemsb.rt_item_id
4319: l_po_header_id NUMBER;
4320: BEGIN
4321: SELECT po_interface_header_id, po_header_id
4322: INTO l_po_interface_header_id, l_po_header_id
4323: FROM icx_cat_r12_upgrade upg, icx_cat_items_b b
4324: WHERE b.rt_item_id = upg.rt_item_id
4325: AND b.org_id = p_org_id
4326: AND b.supplier_id = p_supplier_id
4327: AND upg.supplier_site_id = p_supplier_site_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'
4369: AND price.rt_item_id = itemtlp.rt_item_id
4370: AND NOT EXISTS (SELECT 'extracted price'
4419: DECODE(attr.ATTRIBUTE_VALUES_TLP_ID, NULL, 'ADD', 'UPDATE') attr_val_tlp_action,
4420: price.price_type, itemtlp.primary_category_name
4421: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp,
4422: icx_por_category_order_map map,
4423: icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
4424: icx_cat_r12_upgrade upg, po_attribute_values_tlp attr
4425: WHERE price.price_type = 'CONTRACT'
4426: AND price.rt_item_id = itemtlp.rt_item_id
4427: AND NOT EXISTS (SELECT 'extracted price'
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'
5172: AND price.rt_item_id = itemtlp.rt_item_id
5173: AND NOT EXISTS (SELECT 'extracted price'
5205: upg.po_interface_header_id,
5206: upg.po_header_id, upg.created_language,
5207: nvl(upg.cpa_reference, -2) upg_cpa_reference
5208: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp,
5209: icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
5210: icx_cat_r12_upgrade upg
5211: WHERE price.price_type = 'CONTRACT'
5212: AND price.rt_item_id = itemtlp.rt_item_id
5213: AND NOT EXISTS (SELECT 'extracted price'
5361: SELECT rt_item_id, po_interface_header_id, po_interface_line_id,
5362: upg.po_header_id, po_line_id, ph.org_id
5363: FROM icx_cat_r12_upgrade upg, po_headers_all ph
5364: WHERE NOT EXISTS (SELECT 'x'
5365: FROM icx_cat_items_b itemsB, icx_cat_item_prices prices
5366: WHERE itemsB.rt_item_id = prices.rt_item_id
5367: AND upg.rt_item_id = prices.rt_item_id
5368: AND upg.supplier_site_id = prices.supplier_site_id
5369: AND upg.currency = prices.currency
5790: 2. Check for price added i.e. price_rowid not in icx_cat_r12_upgrade
5791: 3. Check for item updated/translation added
5792: Note: Steps 1, 2 and 3 will be done together using the following approach:
5793: a) Outer join with icx_cat_r12_upgrade
5794: b) Check for last_update_date > g_bulk_last_run_date in icx_cat_items_b, icx_cat_items_tlp,
5795: icx_cat_ext_items_tlp and icx_cat_item_prices
5796: 4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5797: 5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5798: 6. Check for any pricing hdr attribute (i.e. supplier_site_id, currency, contract_id) update after running pre-upgrade.
5792: Note: Steps 1, 2 and 3 will be done together using the following approach:
5793: a) Outer join with icx_cat_r12_upgrade
5794: b) Check for last_update_date > g_bulk_last_run_date in icx_cat_items_b, icx_cat_items_tlp,
5795: icx_cat_ext_items_tlp and icx_cat_item_prices
5796: 4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5797: 5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5798: 6. Check for any pricing hdr attribute (i.e. supplier_site_id, currency, contract_id) update after running pre-upgrade.
5799: Note: Steps 4, 5 and 6 will be done together using the following approach:
5800: a) Get all the records from icx_cat_r12_upgrade that don't exist in icx_cat_items_b and icx_cat_item_prices
5793: a) Outer join with icx_cat_r12_upgrade
5794: b) Check for last_update_date > g_bulk_last_run_date in icx_cat_items_b, icx_cat_items_tlp,
5795: icx_cat_ext_items_tlp and icx_cat_item_prices
5796: 4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5797: 5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5798: 6. Check for any pricing hdr attribute (i.e. supplier_site_id, currency, contract_id) update after running pre-upgrade.
5799: Note: Steps 4, 5 and 6 will be done together using the following approach:
5800: a) Get all the records from icx_cat_r12_upgrade that don't exist in icx_cat_items_b and icx_cat_item_prices
5801: based on rt_item_id, supplier_site_id, currency, contract_id
5796: 4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5797: 5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5798: 6. Check for any pricing hdr attribute (i.e. supplier_site_id, currency, contract_id) update after running pre-upgrade.
5799: Note: Steps 4, 5 and 6 will be done together using the following approach:
5800: a) Get all the records from icx_cat_r12_upgrade that don't exist in icx_cat_items_b and icx_cat_item_prices
5801: based on rt_item_id, supplier_site_id, currency, contract_id
5802: 7. Check for items that have errors i.e. the ones that were not migrated into po tables due to validation errors,
5803: These will have po_interface_header_id and po_interface_line_id populated but will have null po_header_id and po_line_id
5804: Assumptions: Translations cannot be deleted.